Restoring only some key values using COPY STDIN in Postgres?

Question

I accidentally ran a query on live data that deleted 5000 odd rows. I made a backup before I did this, and the backup is in this format:

COPY table (id, "position", event) FROM stdin;
529 1   5283
648 1   6473
687 1   6853
\.

Problem is, if I run it, i get:

ERROR:  duplicate key value violates unique constraint "table_pkey"

is there a way to alter this query to only insert the rows I deleted? Something like an "if exists, ignore" kind of thing? Normally I know this affects many things, but because it's literally just those entries that need to be replaced, I think something like this could work, but I don't know if it exists?

Gavin · Accepted Answer · 2013-02-21 08:50:07Z

1

Easiest way may be to create a copy of the original table and restore to that. Then insert to original table from copy where no entry exists in original. e.g.

create table copy_table as select * from table where 1=2;
-- change the copy statement
COPY copy_table from stdin;
...

-- Insert to original
INSERT INTO table t1
SELECT ct.*
  FROM copy_table ct
       LEFT JOIN  table t2 ON t2.id = ct.id -- assuming id is primary key
 WHERE t2.id IS NULL;

answered Feb 21, 2013 at 8:50

Gavin

6,5603 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Using create table copy_table like original_table might be a better choice instead of the as select... if e.g. default values, not null or check constraints should be preserved.

Gavin Over a year ago

Arguably unnecessary if this is just a quick and temporary table for restore process and the backup copy is considered reliable. In fact using unlogged table might serve.

user330315 · Accepted Answer · 2013-02-21 08:50:11Z

0

No, this is unfortunately not possible using the COPY command.

You need to insert all rows into a staging table, then use insert into .. select ... where not exits (...) to copy the misssing rows from the staging table into the real table.

answered Feb 21, 2013 at 8:50

user330315

Collectives™ on Stack Overflow

Restoring only some key values using COPY STDIN in Postgres?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related