ERROR: duplicate key violates unique constraint "customers_pkey"

Hi Experts, I'm using the psql query editor - connected to MyDatabase.  What is wrong with this?  I've got a column defined as 'serial' as describe below - when I try to insert I get the error 'ERROR:  duplicate key violates unique constraint "customers_pkey"'

Thanks for any help!

MyDatabase=> \d customers
                                         Table "public.customers"
     Column     |          Type          |                            Modifiers
 customer_id    | integer                | not null default nextval('customers_customer_id_seq'::regclass)
 telephone1     | character varying(100) | not null default ''::character varying
 telephone2     | character varying(100) | default ''::character varying
 email          | character varying(100) | default ''::character varying
 title          | character varying(150) | default ''::character varying
 street         | character varying(50)  | not null default ''::character varying
 city           | character varying(40)  | not null default ''::character varying
 postal         | character varying(30)  | not null default ''::character varying
 province       | character varying(30)  | not null default ''::character varying
 country        | character varying(30)  | not null default ''::character varying
 ship_number    | character varying(10)  | default ''::character varying
 bill_number    | character varying(15)  | default ''::character varying
 purchase_order | character varying(20)  | default ''::character varying
 customertype   | character varying(50)  | default ''::character varying
 keep_in_touch  | smallint               | not null default 0
 sendoffers     | smallint               | default 1
 resident       | smallint               | not null default 0
 prefix         | character varying(10)  | default ''::character varying
 firstname      | character varying(75)  | not null default ''::character varying
 lastname       | character varying(75)  | not null default ''::character varying
 english        | smallint               | default 0
 proforma       | smallint               | default 0
 unid           | character varying(23)  | not null default ''::character varying
    "customers_pkey" PRIMARY KEY, btree (customer_id)
MyDatabase=> insert into customers (Telephone1, Telephone2, Email, Title, Street, City, Postal, Province, Country,
MyDatabase(> ship_number, Bill_Number, Purchase_Order, CustomerType, Keep_In_Touch, SendOffers, Resident, Prefix, FirstName,
MyDatabase(> LastName, English, Proforma, UNID) VALUES ('123-123-1212','','','','123 Boob','Montreal','12345','QC',
MyDatabase(> 'Canada','','','','Type',1,1,0,'','Jer','Pascual',0,0,'2008');
ERROR:  duplicate key violates unique constraint "customers_pkey"

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.



Do you have an enormous number of records in your db?
Then the customer_id might have passed the max integer
value and started from 0.

Also I don't understand this:
not null default nextval('customers_customer_id_seq'::regclass)
why not:
not null default nextval('customers_customer_id_seq')

Another problem might be that the sequence:
'customers_customer_id_seq' started from begining.
Do a select on it and see what is the value in it.
Then set the value in the sequence to be the max of the



Ok, I get it now, this is just a conversion to what nexval expects.
not null default nextval('customers_customer_id_seq'::regclass)
Is fine.

I bet the sequence 'customers_customer_id_seq' went back to the begining.

customers_customer_id_seq might have not gone to the beginning but could hold a value that has already been taken. This happens if there was an insert that did not use the sequence explicitly.

Determine what your current sequence value. Then compare that to the max customer_id. If the customer_id is larger than the sequence value, set the sequence to a number higher than the highest customer_id. See the code below for an example.

MyDatabase=# SELECT nextval('customers_customer_id_seq');
LOG:  duration: 0.000 ms
(1 row)
MyDatabase=# SELECT MAX(customer_id) FROM customers;
LOG:  duration: 0.000 ms
(1 row)
MyDatabase=# SELECT setval('customers_customer_id_seq', 124);
LOG:  duration: 0.000 ms
(1 row)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
threadyAuthor Commented:
Hi ivanovn,

If I run the following queries, are you saying it should fail?

INSERT INTO CUSTOMERS (Customer_ID, Telephone) VALUES (1, '123')

... because the 2nd query started explicitly using the sequence after a record was inserted?  If yes, how can I fix this without having to do your proposed solution programmatically before every INSERT?

Many thanks,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.