Re: updating sequence value for column 'serial'

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