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 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