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 <[email protected]> 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 _______________________________________________ use-livecode mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
