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