skip violates unique constraint when dump db

Question

I try to dump a db to another. There are some duplicate unique values in a tables. When pg_dump try to add this item to db, it throw an error and exit. Error is like;

ERROR:  duplicate key value violates unique constraint "test_table_pkey"
DETAIL:  Key (pktable)=(col11) already exists.
CONTEXT:  COPY test_table, line 1: "col11  col12"

My table is;

   Column    |         Type          | Modifiers 
-------------+-----------------------+-----------
 pktable     | text                  | not null
 source      | text                  | not null
Indexes:
    "test_table_pkey" PRIMARY KEY, btree (pktable)

My command is;

pg_dump -U postgres -v --data-only <db_name> | psql -U postgres -h <remote_host> -d <db_name>

I want to that pg_dump skip duplicate unique value and continue to dump operation. How can I do that?

What if the keys are the same, but the rest of the rows are different? — Mike Sherrill 'Cat Recall', Dec 28 '14 at 15:55
In my case if keys are same, all of row have to be same, too. However this rule writing by Lajos can cause another problems in my situation. — umut, Dec 28 '14 at 18:59

score 2 · Accepted Answer · edited May 23 '17 at 12:06

I think this rule could help you:

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SELECT 1 FROM my_table 
                WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
  DO INSTEAD NOTHING;

Basically the INSTEAD NOTHING is triggered if the newly inserted row is already in the table.

(Based on this: https://stackoverflow.com/a/6176044/1665673)

skip violates unique constraint when dump db

1 Answers1