0

I am trying to batch insert or update rows to a database in one postgres statement depending on whether the ID already exists in the table or not. I will be updating/inserting 100 or more rows at the same time

The closest solution I have been able to find online is the ON CONFLICT statement below but that is not giving me the result I would like.

Starting table data example

ID |  DATE       | PRICE
1  |  1/02/2019  | 10

INSERT INTO table1 (id, date, price) 
VALUES (1, '1/14/2019', 20), (2, '1/15/2019', 43) 
ON CONFLICT(id) DO UPDATE 
SET date = table1."date", price = table1.price

The following is the updated table after running the above SQL statement

ID |  DATE       | PRICE
1  |  1/02/2019  | 10
2  |  1/15/2019  | 43

The row with ID is inserted but the 1st row values are not updated with the new values but keep the old values that are already in the table

The result I would like is the following

ID |  DATE       | PRICE
1  |  1/14/2019  | 20
2  |  1/15/2019  | 43
Adrian
  • 45
  • 1
  • 6

1 Answers1

3

In the manual it says this:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

(Emphasis mine).

So, your statement should have been:

INSERT INTO table1 (id, date, price) 
VALUES (1, '1/14/2019', 20), (2, '1/15/2019', 43) 
ON CONFLICT(id) DO UPDATE 
SET date = EXCLUDED."date", price = EXCLUDED.price

The way you were doing it, you were updating the column with the same values as already existed in the table.

harmic
  • 28,606
  • 5
  • 67
  • 91
  • Thank you for the answer and link to the documentation. I saw the use of excluded in some answers of similar questions but I never understood what excluded was doing. – Adrian Jan 20 '19 at 20:44