Re: PostgreSQL server does not increment a SERIAL internally

El día Montag, Juli 06, 2020 a las 01:58:04 +0200, Sándor Daku escribió:

> > We update the serial 'acq_haushalt_hnr_seq' with this statement after
> > loading:
> >
>
> What does "loading" mean, and why do you reset the sequence after loading?
> (And as I can see you setting it to the value it most likely already has.)
> My guess is that your problem lurks somewhere here as in certain
> circumstances you reset it to an incorrect(previous) value.

Hello Sándor,

All the tables (~400) are loaded from an export in CSV like format done
from the same Sybase ASE database with this commands for any table:

08:08:00 TRUNCATE TABLE acq_haushalt ;
08:08:00 TRUNCATE TABLE
08:08:01 \COPY acq_haushalt FROM '/home/sisis/guru/sisisDBsrap14/CC-acq_haushalt.load' WITH ( NULL '' , DELIMITER '|' )
08:08:01 COPY 2862

and afterwards we have to adjust the serials to the highest used value
with the shown command:
>
>
> > /* table: acq_haushalt */
> > DO $$
> > DECLARE
> > max_id int;
> > BEGIN
> > if to_regclass('acq_haushalt') is not null then
> > SELECT INTO max_id GREATEST(COALESCE(max(hnr), 0),0) + 1 FROM
> > acq_haushalt;
> > RAISE NOTICE '% % %', 'acq_haushalt', 'hnr', max_id ;
> > EXECUTE 'ALTER SEQUENCE acq_haushalt_hnr_seq RESTART ' || max_id::text;
> > end if;
> > END $$ LANGUAGE plpgsql;

It's output (for this table) was:

NOTICE: acq_haushalt hnr 3183

which is correct because it matches the highest value +1 of 'acq_haushalt.hnr'.

matthias

--
Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!