Ruby on Rails+PostgreSQL: usage of custom sequences

activerecord, postgresql, rails-postgresql, ruby-on-rails, save

Solution

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.

Problem

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.

Original source