Postgresql - Using subqueries with alter sequence expressions

Question

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)

score 96 · Accepted Answer · answered Jan 07 '10 at 18:56

96

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.

answered Jan 07 '10 at 18:56

Arthur Thomas

17

`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. – chbrown Dec 03 '14 at 20:51
do i need a from table at the end of the whole statement? "missing from clause" – FlavorScape Mar 21 '18 at 19:10

score 8 · Answer 2 · answered Mar 01 '13 at 16:41

8

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

answered Mar 01 '13 at 16:41

Duncan Smart

2

took me a bit to notice the single quotes around the double quotes! – Tim Tisdall Jan 19 '18 at 20:55
you absolute HERO. I sunk about 4 hrs into this problem and this fixed it. Thanks! – Joel Worsham May 09 '20 at 17:14

2 Answers2