0

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

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Martin Nicolas Ortiz
  • 15
  • 2
  • *doest not works* means? any error message? – Jens Jan 31 '22 at 14:44
  • yes , error message from postgres: ERROR: syntax error at or near "(" LINE 3: ...sourceScopes" ALTER COLUMN "Id" RESTART SET START (select co... ^ SQL state: 42601 Character: 71 – Martin Nicolas Ortiz Jan 31 '22 at 15:49
  • 1
    https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync –  Jan 31 '22 at 16:25
  • "*my table has not a sequence function, jus a identity Id, so.. update the sequences not its a option*" - an identity column **does** use a sequence in the background. https://stackoverflow.com/a/23390399 –  Jan 31 '22 at 16:48

1 Answers1

0

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")
       );
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263