postgresqldatabase-sequence

PostgreSQL ALTER SEQUENCE with SELECT clause

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?

Solution

  • You can use setval() instead

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

    Note the parentheses around the select.