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@aklaver.com
