postgresqlsubqueryalter-table

ALTER TABLE in Postgres with a subquery


I am trying to update the identity sequence in my table, but I am getting a syntax error.

This works:

ALTER TABLE "ApiResourceScopes" ALTER COLUMN "Id"
   RESTART SET START 145

This doesn't work:

ALTER TABLE "ApiResourceScopes" ALTER COLUMN "Id"
   RESTART SET START (
      select coalesce(max("Id"), '0') + 1 as "Id"
      FROM public."ApiResourceScopes"
   )

The error is:

ERROR: syntax error at or near "(" LINE 3: ...sourceScopes" ALTER COLUMN "Id" RESTART SET START (select co... ^ SQL state: 42601 Character: 71

How could I set a value with a subquery in an ALTER TABLE statement?

Note: my table foes not have nextval('somesequence') in the column default, but an identity column, so directly updating the sequence is not an option.


Solution

  • You cannot use a subquery in that place. The simplest solution would be to first run the query and then an appropriate ALTER TABLE. If you insist on doing it in a single statement, use something like

    SELECT setval(
              pg_get_serial_sequence(
                 '"ApiResourceScopes"',
                 'Id'
              ),
              (select coalesce(max("Id"), '0')
               FROM public."ApiResourceScopes")
           );