Re: PostgreSQL server does not increment a SERIAL internally - Mailing list pgsql-general

From Matthias Apitz
Subject Re: PostgreSQL server does not increment a SERIAL internally
Date
Msg-id 20200706122723.GB318@sh4-5.1blu.de
Whole thread Raw
In response to Re: PostgreSQL server does not increment a SERIAL internally  (Sándor Daku <daku.sandor@gmail.com>)
List pgsql-general
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@unixarea.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!



pgsql-general by date:

Previous
From: Francisco Olarte
Date:
Subject: Re: Apply LIMIT when computation is logically irrelevant
Next
From: Simon Riggs
Date:
Subject: Re: Apply LIMIT when computation is logically irrelevant