postgres "serial primary key" recycling values

Dr. Hawkins dochawk at gmail.com
Wed Aug 31 14:13:16 EDT 2016


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



More information about the Use-livecode mailing list