How to create sequence using starting value from query?

Question

In database migration script (psql) I want to create sequence starting from certain value queried from table, like:

CREATE SEQUENCE book_id_seq START ( SELECT MAX(id) + 1 FROM book.book );

or tried to set \set start (SELECT MAX(id) + 1 FROM book.book) to use variable like:

CREATE SEQUENCE book_id_seq START :'start';

But using \set did not inerpret the query.

Another way setting variable did not work also

start := SELECT MAX(id) + 1 FROM book.book;

gave error:

ERROR:  syntax error at or near ":="
LINE 1: start := SELECT MAX(id) + 1 FROM book.book;

Selecting value INTO does not help also, because accessing needs another SELECT query.

Using static value works fine:

\set start 33
CREATE SEQUENCE book_id_seq START :'start'::int;

How to use dynamic starting value?

Postgres 9.6

Why do you want to use a sequence? Just define a serial value. — Gordon Linoff
– Gordon Linoff, Commented May 18, 2018 at 12:18
@GordonLinoff it is relevant to my other Q/A stackoverflow.com/a/50388597/196241, need to handle separate sequences for one table, to summarize problem in short. — w.k
– w.k, Commented May 18, 2018 at 12:21

user330315 · Accepted Answer · 2018-05-18 12:15:27Z

10

you can use setval() after you created the sequence:

CREATE SEQUENCE book_id_seq;
select setval('book_id_seq', (SELECT MAX(id) + 1 FROM book.book));

answered May 18, 2018 at 12:15

user330315

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

1 Comment

Nice approach, still keen to understand, how to assign dynamic value in script.