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
