Thread: updating sequence value for column 'serial'
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
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Mientras haya voluntad de lucha habrá esperanza de vencer.
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@aklaver.com
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.
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?
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Mientras haya voluntad de lucha habrá esperanza de vencer.
Attachment
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
El día miércoles, septiembre 25, 2019 a las 07:42:11a. m. -0700, Adrian Klaver escribió:
> >>> 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;
Hi Adrian,
I adopted your code to the name of my table 'ig_target_ipfilter' and its
SERIAL column 'id'; it does not work (and I don't know how it could
works because in the 'ALTER SEQUENCE ...' stmt is somehow missing '... WITH value ...')
or do I understand something wrong?):
cat -n /home/apitzm/postgreSQL/test.sql
1 DO $$
2 DECLARE
3 max_id int;
4 BEGIN
5 SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;
6 RAISE NOTICE 'Max id in % is %', 'ig_target_ipfilter', max_id;
7 EXECUTE 'ALTER SEQUENCE ig_target_ipfilter_id_seq RESTART ' || max_id::text;
8 END;
9 $$ LANGUAGE plpgsql;
psql -Usisis -dsisis < /home/apitzm/postgreSQL/test.sql
NOTICE: Max id in ig_target_ipfilter is <NULL>
ERROR: query string argument of EXECUTE is null
KONTEXT: PL/pgSQL function inline_code_block line 7 at EXECUTE
Please clarify. Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Mientras haya voluntad de lucha habrá esperanza de vencer.
Attachment
On 9/25/19 10:12 PM, Matthias Apitz wrote:
> El día miércoles, septiembre 25, 2019 a las 07:42:11a. m. -0700, Adrian Klaver escribió:
>
>>>>> 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;
>
> Hi Adrian,
>
> I adopted your code to the name of my table 'ig_target_ipfilter' and its
> SERIAL column 'id'; it does not work (and I don't know how it could
> works because in the 'ALTER SEQUENCE ...' stmt is somehow missing '... WITH value ...')
> or do I understand something wrong?):
>
> cat -n /home/apitzm/postgreSQL/test.sql
> 1 DO $$
> 2 DECLARE
> 3 max_id int;
> 4 BEGIN
> 5 SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;
> 6 RAISE NOTICE 'Max id in % is %', 'ig_target_ipfilter', max_id;
> 7 EXECUTE 'ALTER SEQUENCE ig_target_ipfilter_id_seq RESTART ' || max_id::text;
> 8 END;
> 9 $$ LANGUAGE plpgsql;
>
> psql -Usisis -dsisis < /home/apitzm/postgreSQL/test.sql
> NOTICE: Max id in ig_target_ipfilter is <NULL>
> ERROR: query string argument of EXECUTE is null
> KONTEXT: PL/pgSQL function inline_code_block line 7 at EXECUTE
>
> Please clarify. Thanks
I forgot about the possibility of NULL being returned by max_id in:
SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;
So:
SELECT INTO max_id COALESCE(max(id), 0) + 1 FROM ig_target_ipfilter ;
That will turn a NULL max(id) into 0.
>
> matthias
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com