Ruby on Rails+PostgreSQL: usage of custom sequences

Question

Say I have a model called Transaction which has a :transaction_code attribute. I want that attribute to be automatically filled with a sequence number which may differ from id (e.g. Transaction with id=1 could have transaction_code=1000).

I have tried to create a sequence on postgres and then making the default value for the transaction_code column the nextval of that sequence. The thing is, if I do not assign any value to @transaction.transaction_code on RoR, when I issue a @transaction.save on RoR, it tries to do the following SQL:

INSERT INTO transactions (transaction_code) VALUES (NULL);

What this does is create a new row on the Transactions table, with transaction_code as NULL, instead of calculating the nextval of the sequence and inserting it on the corresponding column. Thus, as I found out, if you specify NULL to postgres, it assumes you really want to insert NULL into that column, regardless of it having a default value (I'm coming from ORACLE which has a different behavior).

I'm open to any solution on this, either if it is done on the database or on RoR:

• either there is a way to exclude attributes from ActiveRecord's save
• or there is a way to change a column's value before insert with a trigger
• or there is a way to generate these sequence numbers within RoR
• or any other way, as long as it works :-)

Thanks in advance.

Answer 1

For the moment, you might be stuck fetching and assigning the sequence in your ROR model like this:

before_create :set_transaction_code_sequence

def set_transaction_code_sequence
  self.transaction_code = self.class.connection.select_value("SELECT nextval('transaction_code_seq')")
end

I'm not particularily fond of this solution, since I'd like to see this corrected in AR directly... but it does do the trick.

Answer 2

If you want to insert the default value in to a column in an INSERT statement, you can use the keyword DEFAULT - no quotes:

INSERT INTO mytable (col1, col2) VALUES (105, DEFAULT);

Or you could spell out the default, nextval(...) in your case. See the manual here.

---

A trigger for that case is simple. That's actually what I would recommend if you want to make sure that only numbers from your sequence are entered, no matter what.

CREATE OR REPLACE FUNCTION trg_myseq()
  RETURNS trigger AS
$BODY$
BEGIN

NEW.mycol := nextval('my_seq');
RETURN NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER myseq
  BEFORE INSERT
  ON mytable
  FOR EACH ROW
  EXECUTE PROCEDURE trg_myseq();

On a side note: If you want to assign your own (non-sequential) numbers as 'sequence', I have written a solution for that in an answer a couple of days ago:
How to specify list of values for a postgresql sequence