postgres "serial primary key" recycling values

[Dr. Hawkins]

I think I see what is happening.

The nextval() in postgres does *not* consider the numbers in use in the
sequence; I thought it had.

So adding

put "ALTER SEQUENCE " & dhtbl_dbtr & "_unqKy_seq RESTART WITH 1001;" & cr
after dcmd


Instead of inserting ckCap solves the problem.

I tried

MIN(1001, MAX(unqKy))

but it didn't like the min

On Wed, Aug 31, 2016 at 11:13 AM, Dr. Hawkins <dochawk at gmail.com> wrote:

>
> I create my table with
>
> *CREATE TABLE dhdbt_testy_xxiv_______001  (unqKy SERIAL PRIMARY KEY, ktyp
>       *
>
> *   CHAR(1),  kywd VARCHAR(50) UNIQUE, usr VARCHAR(20), tstmp TIMESTAMP
> WITH *
>
> *   TIME ZONE, scr TEXT, dltd BOOLEAN, cmd TEXT );*
>
> *ALTER TABLE dhdbt_testy_xxiv_______001 OWNER TO hawk ;*
>
> *INSERT INTO dhdbt_testy_xxiv_______001(kywd, unqKy, ktyp, cmd) VALUES *
>
> *   ('ckDinfo', 1, 'M',  '100000a');*
>
> *INSERT INTO dhdbt_testy_xxiv_______001(kywd, unqKy, ktyp, cmd) VALUES  *
>
> *   ('ckDna',2, 'M',  '100000a');*
>
> *INSERT INTO dhdbt_testy_xxiv_______001(kywd, unqKy, ktyp, cmd) VALUES*
>
> *   ('ckCap',1000, 'M',  '100000a');*
>
> IN another routine, I check with
>
> *ck revDataFromQuery(,,debtorDb,**"SELECT * FROM "** & dhtbl_dbtr)*
>
>
> yielding
>
> *1 M ckDinfo 100000a*
>
> *2 M ckDna 100000a*
>
> *1000 M ckCap 100000a*
>
>
> so far, so good.
>
> But then
>
> *DELETE FROM dhdbt_testy_xxiv_______001 WHERE kywd='hasB_19';*
>
> *INSERT INTO dhdbt_testy_xxiv_______001 (kywd, ktyp, tstmp , cmd) *
>
> *   VALUES('hasB_19', 'I', NOW(), 'INSERT INTO testy_xxiv_______001_dinfo *
>
> *   (kywd, dTyp, tstmp ) VALUES(''hasB_19'', ''B'', ''' || NOW() ||  ''');*
>
> *   ');*
>
> has the result
>
> *ERROR:  duplicate key value violates unique constraint
> "dhdbt_testy_xxiv_______001_pkey"*
>
> *DETAIL:  Key (unqky)=(1) already exists.*
>
>
> This code has been working for months or years, and now I"m getting this
> failure.
>
> It's talking to the same postgres server on my machine that it's been
> talking to for months.  (the download for OS X from postgres itself,
> version 9.3)
>
> unqKy gets defined in the create table, and *should* be advancing to the
> next available value, which I believe is 1001 (which is why that ckCap is
> there; to reserve lower values for me)
>
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
>



-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462