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@xxxxxxxxxxx
