Sqitch migration with ALTER SEQUENCE on Postgresql db has no effect

Question

When I create a table with a Sqitch migration I want to initialize its starting SERIAL PRIMARY KEY with something other than 1. However, for some reason the ALTER SEQUENCE command does not seem to have any effect.

My deploy migration looks something like this:

BEGIN;

  CREATE TABLE "user" (
    "id" SERIAL PRIMARY KEY,

    ... more columns ...
  );

  ALTER SEQUENCE "user_id_seq" RESTART WITH 12345;

COMMIT;

When the above migration has successfully run, the id column of first row I insert into my user table with have the value 1.

However, if I execute the ALTER SEQUENCE "user_id_seq" RESTART WITH 12345; statement manually (after the migrations have run), the next row I insert, the id column has the value 12345.

I have tried to move the ALTER SEQUENCE... statement into its own migration but this does not seem to have any positive effect.

How can I successfully execute the ALTER SEQUENCE... statement within a Sqitch migration?

Answer 1

Did you try this:

SELECT setval("user_id_seq", 12345, false);

https://www.postgresql.org/docs/current/functions-sequence.html

UPDATED:

what about this? just tested it works fine

BEGIN;

CREATE SEQUENCE user_id_seq START 12345;

CREATE TABLE "user" (
  "id" integer NOT NULL DEFAULT nextval('user_id_seq'),
  --- rest fields
);

ALTER SEQUENCE user_id_seq OWNED BY user.id;

COMMIT;