By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,812 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,812 IT Pros & Developers. It's quick & easy.

interesting! a sequence clashes with data already in that table

P: n/a
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?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Fri, Oct 08, 2004 at 03:00:07 -0700,
Miles Keaton <mi*********@gmail.com> wrote:
I thought Postgres would be smart enough to make the clients_id_seq
skip existing numbers, but I guess not!
It wouldn't be very efficient to do that.
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?
That is normally what you want to do.
If so, setval() would be best for that, right?


Yes.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.