| From: | Miles Keaton <mileskeaton(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | interesting! a sequence clashes with data already in that table | 
| Date: | 2004-10-08 10:00:07 | 
| Message-ID: | 59b2d39b04100803003b5f47f2@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Here's an interesting problem!
When a sequence clashes with data already in that table:
CREATE TABLE clients (
id serial NOT NULL PRIMARY KEY UNIQUE,
name varchar(64));
-- import OLD clients, with their original ID#...
INSERT INTO clients VALUES (3, 'Dave');
INSERT INTO clients VALUES (4, 'Sally');
-- done! let the world add new clients now
INSERT INTO clients (name) VALUES ('Harry');
 -- no problems, id=1
INSERT INTO clients (name) VALUES ('Mary');
 -- no problems, id=2
INSERT INTO clients (name) VALUES ('Burt');
ERROR:  duplicate key violates unique constraint "clients_pkey"
I thought Postgres would be smart enough to make the clients_id_seq
skip existing numbers, but I guess not!
Do I, instead, have to be smart enough to set the sequence over the
highest existing id# from my imported data, so that it can blindly
increment without clashing?
If so, setval() would be best for that, right?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Garamond | 2004-10-08 10:02:48 | Re: 8.0 questions | 
| Previous Message | Robin Ericsson | 2004-10-08 09:19:57 | query gone haywire :) |