Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

postgresql

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)
like image 996
Amiko Avatar asked Apr 25 '17 09:04

Amiko


1 Answers

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
like image 106
Oto Shavadze Avatar answered Nov 14 '22 21:11

Oto Shavadze