I am trying to develop a rails application on postgresql using a sequence to increment a field instead of a default ruby approach based on validates_uniqueness_of.

This has proved challenging for a number of reasons: 1. This is a migration of an existing table, not a new table or column 2. Using parameter :default => "nextval('seq')" didn't work because it tries to set it in parenthesis 3. Eventually got migration working in 2 steps:

change_column :work_commencement_orders, :wco_number_suffix, :integer, :null => false#, :options => "set default nextval('wco_number_suffix_seq')"

execute %{
  ALTER TABLE work_commencement_orders ALTER COLUMN wco_number_suffix SET DEFAULT nextval('wco_number_suffix_seq');
}

Now this would appear to have done the correct thing in the development database and the schema looks like:

wco_number_suffix      | integer                     | not null default nextval('wco_number_suffix_seq'::regclass)

However, the tests are failing with

PGError: ERROR:  null value in column "wco_number_suffix" violates not-null constraint
: INSERT INTO "work_commencement_orders" ("expense_account_id", "created_at", 
"process_id", "vo2_issued_on", "wco_template", "updated_at", "notes", "process_type", 
"vo_number", "vo_issued_on", "vo2_number", "wco_type_id", "created_by", 
"contractor_id", "old_wco_type", "master_wco_number", "deadline", "updated_by", 
"detail", "elective_id", "authorization_batch_id", "delivery_lat", "delivery_long", 
"operational", "state", "issued_on", "delivery_detail") VALUES(226, '2010-05-31 
07:02:16.764215', 728, NULL, E'Default', '2010-05-31 07:02:16.764215', NULL, 
E'Procurement::Process', NULL, NULL, NULL, 226, NULL, 276, NULL, E'MWCO-213', 
'2010-06-14 07:02:16.756952', NULL, E'Name 4597', 220, NULL, NULL, NULL, 'f', 
E'pending', NULL, E'728 Test Road; Test Town; 1234; Test Land') RETURNING "id"

The explanation can be found when you inspect the schema of the test database:

wco_number_suffix      | integer                     | not null

So what happened to the default?

I tried adding

task:
    template: smmt_ops_development

to the database.yml file which has the effect of issuing

create database smmt_ops_test template = "smmt_ops_development" encoding = 'utf8'

I have verified that if I issue this then it does in fact copy the default nextval. So clearly rails is doing something after that to suppress it again.

Any suggestions as to how to fix this?

Thanks Robert