ALTER TABLE in Postgres with a subquery

Question

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.

Answer 1

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")
       );