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(dot)klaver(at)aklaver(dot)com