On 9/24/19 10:40 PM, Matthias Apitz wrote:
El día martes, septiembre 24, 2019 a las 08:01:46a. m. -0700, Adrian Klaver escribió:
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:
Yes, but I found no way to use a variable like 'maxikatkey' in the ALTER SEQUENCE ...
it only excepts digits like 330722.
DO $$
DECLARE
max_id int;
BEGIN
SELECT INTO max_id max(id) + 1 FROM seq_test;
RAISE NOTICE 'Max id is %', max_id;
EXECUTE 'ALTER SEQUENCE seq_test_id_seq RESTART ' || max_id::text;
END;
$$ LANGUAGE plpgsql;
Sometimes, when the table has no rows for example, the SELECT MAX(...) FROM ...
returns <NULL>. I'm surprised about that even maxikatkey := maxikatkey +1; does
not set it to 1 'maxikatkey'. Should I worry about this in SELECT SETVAL(...) or can
I make it somehow to 1 or 0?
It's just going to leave it where it is:
test=# create sequence test_seq;
CREATE SEQUENCE
test=# select * from test_seq ;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
test=# SELECT SETVAL('test_seq', NULL);
setval
--------
NULL
(1 row)
test=# select * from test_seq ;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
test=# SELECT SETVAL('test_seq', 15);
setval
--------
15
(1 row)
test=# SELECT SETVAL('test_seq', NULL);
setval
--------
NULL
(1 row)
test=# select * from test_seq ;
last_value | log_cnt | is_called
------------+---------+-----------
15 | 0 | t
(1 row)
matthias
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
