Is it possible to use subqueries within alter expressions in PostgreSQL?
I want to alter a sequence value based on a primary key column value.
I tried using the following expression, but it wouldn't execute.
alter sequence public.sequenceX restart with (select max(table_id)+1 from table)
I don't believe you can do it like that but you should be able to use the setval function direction which is what the alter does.
select setval('sequenceX', (select max(table_id)+1 from table), false)
The false will make it return the next sequence number as exactly what is given.
select setval('sequenceX', (select max(table_id) from table)) will accomplish the same thing, since the last parameter, "is_called", defaults to true, but also correctly handles the case where you might have created that sequence with an INCREMENT BY value other than 1.
In addition if you have mixed case object names and you're getting an error like this:
ERROR: relation "public.mytable_id_seq" does not exist
... the following version using regclass should be useful:
select setval('"public"."MyTable_Id_seq"'::regclass, (select MAX("Id") FROM "public"."MyTable"))
