Re: updating sequence value for column 'serial'

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(at)unixarea(dot)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.