db:migrate creates sequences but doesn't alter table?

Question

Hello,

I have a migration that creates a postres sequence for auto incrementing a primary identifier, and then executes a statement for altering the column and specifying the default value:

execute 'CREATE SEQUENCE "ServiceAvailability_ID_seq";'
execute <<-SQL
  ALTER TABLE "ServiceAvailability" 
    ALTER COLUMN "ID" set DEFAULT NEXTVAL('ServiceAvailability_ID_seq');
SQL

If I run db:migrate everything seems to work, in that no errors are returned, however, if I run the rails application I get:

Mnull value in column "ID" violates not-null constraint

I have discovered by executing the sql statement in the migration manually, that this error is because the alter statement isn't working, or isn't being executed.

If I manually execute the following statement:

CREATE SEQUENCE "ServiceAvailability_ID_seq;

I get:

error : ERROR: relation "serviceavailability_id_seq" already exists

Which means the migration successfully created the sequence! However, if I manually run:

ALTER TABLE "ServiceProvider" 
    ALTER COLUMN "ID" set DEFAULT NEXTVAL('ServiceProvider_ID_seq');
SQL

It runs successfully and creates the default NEXTVAL.

So the question is, why is the migration file creating the sequence with the first execute statement, but not altering the table in the second execute? (Remembering, no errors are output on running db:migrate)

Thank you and apologies for tl:dr

Answer 1

I separated the creation of sequences and the altering of tables into two migrations.

When running:

rake db:migrate

The sequences would not be created nor the tables altered and the rake would run successfully.

If however, I ran the migrations seperately:

rake db:migrate VERSION=1
rake db:migrate VERSION=2

The sequences would be created, and the tables altered as expected.