0

I am trying to alter database sequence and restart it with value returned by complex SELECT statement. This is a simplified example that I prepared to replicate the issue:

ALTER SEQUENCE
    abc.my_seq
RESTART WITH
    (SELECT 1234)

When I run this query, I get the following error:

ERROR: syntax error at or near "("

Why am I receiving this error? Is it possible to set the value of a sequence based on returned value of SELECT statement?

Sam Carlson
  • 1,891
  • 1
  • 17
  • 44

1 Answers1

1

You can use setval() instead

select setval('abc.my_seq', (select ... 
                             from ...));

Note the parentheses around the select.

  • Thank you, this works. Is there a reason why my initial try (`ALTER SEQUENCE abc.my_seq RESTART WITH (SELECT 1234)`) doesn't work? Is it simply not allowed by-design in PostgreSQL? – Sam Carlson Oct 18 '21 at 13:37
  • DDL statements typically do not allow parameters to be specified by queries –  Oct 18 '21 at 13:42