Re: pg_restore without dropping db/table

fyi, since the version of PostgreSQL was NOT stated (or O/S) as is the
proper thing to do when posting, I gave a generic solution which covers all
versions and O/S's

On Thu, Mar 10, 2016 at 3:53 PM, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
wrote:

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.