SQL insert new rows but keep old value in conflicting keys-CodePudding

Say I have two tables:

tb1:

id name date
1 John 01/01/2012
1 John 01/02/2012
2 James 02/02/2020

tb2:

id name date
1 John 01/01/2013
1 John 01/01/2012

The uniqueness of both tb1 and tb2 comes from the combination of (id, name,date) columns. Therefore I would like to insert only values from tb2 that are new to tb1. In this case only (1,John,01/01/2013) would be inserted since the other row is already present in tb1.

My try is:

INSERT INTO tb1 (date) SELECT * FROM tb2 ON CONFLICT (id,name,date) DO NOTHING;

CodePudding user response：

You did not tell us what the error is that you get. But just from a syntax check, it will result in the error:

ERROR: INSERT has more expressions than target columns

because you specify one target columns, but provide three columns from the SELECT.

Assuming you did specify a unique constraint or primary key on the three columns, adding the additional columns in the INSERT statement should work:

INSERT INTO tb1 (id,name,date) 
SELECT id,name,date 
FROM tb2 ON CONFLICT (id,name,date) DO NOTHING;

CodePudding user response：

SQL language is a non procedural language, just a query language... You must do this with queries, like :

INSERT INTO tb1 (date) 
SELECT * 
FROM   tb2 
WHERE  NOT EXISTS(SELECT * 
                  FROM   tb1 INNER JOIN tb2 
                         ON ROW (tb1.id, tb1.name, tb1.date) = 
                            ROW (tb2.id, tb2.name, tb2.date));