Question 90763 in PostgreSQL Question posted in
The official documentation can be found here.

SQL insert new rows but keep old value in conflicting keys – Postgresql

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;
Tags:

2

Answers


  1. 0

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

    INSERT INTO tb1 (id,name,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));
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search