Re: updating sequence value for column 'serial'

On 9/24/19 7:47 AM, Matthias Apitz wrote:
> 
> Hello,
> 
> We have in a database some 400 tables, 75 of them have a 'serial'
> column, like the one in the example table 'titel_daten', column 'katkey'.
> 
> I want to create a SQL script to adjust alls these sequences to the
> max+1 value in its column after loading the database from CSV file.
> I found no other way as the code below (the RAISE NOTICE is
> only for test at the moment and the output is correct for this table,
> i.e current max in 'katkey' is 330721):
> 
> sisis=# DO $$
> sisis$# DECLARE
> sisis$#    maxikatkey integer := ( select max(katkey) from titel_daten );
> sisis$#    result integer := 1;
> sisis$# BEGIN
> sisis$#    maxikatkey := maxikatkey +1;
> sisis$#    RAISE NOTICE '%', maxikatkey ;
> sisis$#    result := (SELECT SETVAL('titel_daten_katkey_seq', maxikatkey) );
> sisis$#    RAISE NOTICE '%', result ;
> sisis$# END $$;
> NOTICE:  330722
> NOTICE:  330723
> DO
> 
> Is there any better way? Thanks

I have not found a better way. I use ALTER SEQUENCE .. RESTART 330722 
though:

https://www.postgresql.org/docs/11/sql-altersequence.html


That is roughly equivalent to

SELECT SETVAL('titel_daten_katkey_seq', maxikatkey, false)

in that the next value used will be 330722 not 330723.

RESTART is also transactional whereas SETVAL() is not.

> 
>     matthias
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com