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!

Mike
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
Indexes:
    "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

LVL 1
threadyAsked:
Who is Participating?
 
ivanovnConnect With a Mentor Commented:
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
 nextval
---------
      115
(1 row)
 
MyDatabase=# SELECT MAX(customer_id) FROM customers;
LOG:  duration: 0.000 ms
 nextval
---------
      123
(1 row)
 
MyDatabase=# SELECT setval('customers_customer_id_seq', 124);
LOG:  duration: 0.000 ms
 setval
--------
    124
(1 row)

Open in new window

0
 
adrpoCommented:

Hi,

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
customer_id.

Cheers,
za-k/

0
 
adrpoCommented:

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.

Cheeers,
za-k/
0
 
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')
INSERT INTO CUSTOMERS (Customer_ID, Telephone) VALUES(DEFAULT, '456')

... 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,
Mike
0
All Courses

From novice to tech pro — start learning today.