Re: pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 01:49:42PM -0500, Melvin Davidson wrote:

> The best way to accomplish what you want is to create a table with the same
> structure in the first database as the one you want to restore to. Then you
> can truncate that table, restore the data from the other db into it, and
> use that to add the new rows to your table.
> eg:
> 1. You have your original table:
> CREATE TABLE orig_table
> (prime_key varchar(10) ,
> data_col1 integer,
> data_col2 varchar(5),
> CONSTRAINT orig_table_pk PRIMARY KEY (prime_key)
> );
> 2. Duplicate table:
> CREATE TABLE dup_table
> (prime_key varchar(10) ,
> data_col1 integer,
> data_col2 varchar(5),
> CONSTRAINT dup_table_pk PRIMARY KEY (prime_key)
> );

This could benefit from

create table [...] like orig_table excluding all ...

> 8. INSERT INTO orig_table
> SELECT * FROM dup_table
> WHERE dup.prime_key NOT IN (SELECT prime_key FROM orig_table);

This will work if

dup.prime_key NOT IN (SELECT prime_key FROM orig_table)

identifies "new" rows. This probably has the highest chance
of being true if prime_key is a natural key.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346