9

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.

Ricardo Melo
  • 131
  • 1
  • 4

3 Answers3

6

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.

Erik Lott
  • 697
  • 1
  • 9
  • 17
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

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    Thanks Erwin for your answer. The thing is, within ActiveRecord's save method, you cannot "send" the keyword DEFAULT within an INSERT (that would solve the case, yes). I would prefer a solution that could be 100% codified within the Ruby on Rails application, instead of programming it on the database, which would disable me from seamlessly migrating to another DBMS if needed. – Ricardo Melo Oct 24 '11 at 10:54
  • 1
    @Ricardo: "seamlessly migrating to another DBMS" is a nice fantasy but it doesn't have that much to do with reality, sorry. One big downside of a trigger is that you won't be able to use it on a Heroku shared database. – mu is too short Oct 24 '11 at 20:00
0

I was still experiencing this issue with Rails7 - I could see that Rails was generating a NULL in the insert, but changing the column from integer to bigint solved it. - Rails then does not supply a value for my sequenced column and the DEFAULT nextval('number_seq') is used.

Brent Greeff
  • 174
  • 1
  • 6