Postgresql - Using subqueries with alter sequence expressions

Solution 1:

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.

Solution 2:

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"))