PostgreSQL ALTER SEQUENCE with SELECT clause

Question

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?

user330315 · Accepted Answer · 2021-10-18 07:32:35Z

2

You can use setval() instead

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

Note the parentheses around the select.

answered Oct 18, 2021 at 7:32

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Aleks Vujic Over a year ago

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?

user330315 Over a year ago

DDL statements typically do not allow parameters to be specified by queries

Collectives™ on Stack Overflow

PostgreSQL ALTER SEQUENCE with SELECT clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related