How to alter Sequence in PostgreSQL with specific max(id) from a table?

Question

I need to get following SQL script syntax right. In postgres, you can't really chain "alter sequence" with "select max(id)". So what's the right way to write script in a way that PostgreSQL accepts it?

Here is the script so you can have an idea what I need:

 alter SEQUENCE notification_settings_seq START with (select max(id) from game_user)

Just run this command: SELECT setval('my_table_seq', (SELECT max(id) FROM my_table)); — Asad Rao, Oct 01 '19 at 13:04

Oto Shavadze · Accepted Answer · 2017-04-25T09:24:36.883

10

This restarts your sequence with new value:

do $$
declare maxid int;
begin
    select max(id) from game_user into maxid;
    execute 'alter SEQUENCE seq_name RESTART with '|| maxid;   
end;
$$ language plpgsql

edited Apr 25 '17 at 09:24

answered Apr 25 '17 at 09:18

Oto Shavadze

34,391
48
116
201

Thanks for the fast reply. I edited a bit but it does what I need. Apparently, this question has already been answered. My bad for posting duplicates. დიდი მადლობა! – Amiko Apr 25 '17 at 09:43
@Amiko - You're welcome. გაიხარე )) – Oto Shavadze Apr 25 '17 at 10:04
SELECT setval('my_table_seq', (SELECT max(id) FROM my_table)); – Asad Rao Oct 01 '19 at 13:04
If sequence contains uppercase letters in name: SELECT setval(' "Foo" ', 42); Notice the double quotes inside the single quotes – Fabio Espinosa Dec 05 '19 at 14:39

How to alter Sequence in PostgreSQL with specific max(id) from a table?

1 Answers1