0

I have a PostgreSQL 9.2 server and want to restore this to PostgreSQL 9.6.

Here is the table.

create table mytable (
ID int,
EID int,
Name varchar(100),
Address varchar(300),
primary key (ID, EID)
)

While restoring this I'm getting this error.

COPY failed for table "mytable ": ERROR:  duplicate key value violates unique constraint "ID_EID_pkey"
DETAIL:  Key (ID, EID)=(18, 2) already exists.

Here is the select query results of the mytable.

select ID, EID from mytable where ID=18;

ID | EID
___|____
18 | 2

(1 row)


select ID, EID from where mytable where EID=2;

ID | EID
___|____
10 | 2
14 | 2 
18 | 2
(3 rows)

As per my knowledge, PostgreSQL will create a Primary key on ID column and Unique index on EID.

So the EID is the unique column.

\d+ mytable --On both servers

     Column      |            Type             | Modifiers | Storage  | Stats target | Description
-----------------+-----------------------------+-----------+----------+--------------+-------------
 ID              | integer                     | not null  | extended |              |
 EID             | integer                     | not null  | plain    |              |
 Name            | character varying(100)      |           | plain    |              |
 Address         | character varying(100)      |           | plain    |              |
Indexes:
    "ID_EID_pkey" PRIMARY KEY, btree (ID, EID)
Has OIDs: no

My Questions,

  1. How the unique column has 3 duplicate values (value 2)?
  2. While restoring why its throwing this error?
  • Could you add both table schema with index? – McNets Dec 20 '17 at 9:20
  • Both tables are having the same schema. – Bhuvanesh Dec 20 '17 at 9:20
  • Let me suggest to temporaryly remove ID_EID_pkey index, restore the data and look for duplicates. – McNets Dec 20 '17 at 9:22
  • Ok, but i don't how the source table able to insert duplicate records even in the unique index. – Bhuvanesh Dec 20 '17 at 9:25
  • 1
    Sounds as if you have a corrupted primary key index on the source database. Check for duplicates there. What is the exact version (select version();) of the 9.2 installation? – a_horse_with_no_name Dec 20 '17 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.