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

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
– Asad Rao, Commented Oct 1, 2019 at 13:04

Oto Shavadze · Accepted Answer · 2017-04-25 09:24:36Z

19

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, 2017 at 9:24

answered Apr 25, 2017 at 9:18

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

4 Comments

Amiko Over a year ago

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. დიდი მადლობა!

Oto Shavadze Over a year ago

@Amiko - You're welcome. გაიხარე ))

Asad Rao Over a year ago

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));

Fabio Espinosa Over a year ago

If sequence contains uppercase letters in name: SELECT setval(' "Foo" ', 42); Notice the double quotes inside the single quotes

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related