Postgresql: UPSERT / INSERT INTO defining a conflict

this is my first post on the platform.

I hope this image explains what I am trying to do. I am trying to merge table B into table A. If the condition is met that (A.cell = B.cell AND A.object = B.objet) then I want to update A.cost with the corresponding cost from table B. If the condition is not met, I want to insert the row from B into A.

What I have tried so far is an upsert, but it didn’t work:

INSERT INTO reached_points
SELECT B.cell_id, B.object_id, B.reached_cost
FROM test_reached_cells B
ON CONFLICT (cell_id, object_id)
WHERE (cell_id = B.cell_id AND object_id = B.object_id)
DO UPDATE SET cost = B.reached_cost

I thought in the WHERE clause after the CONFLICT I could define when that conflict is ‘triggered’. Is this not the case? I thought so because in the documentation it says something like “[ON CONFLICT target action;] The target can be: WHERE predicate – a WHERE clause with a predicate”.

Is there a way to achieve an update and if the condition is not met, an insert? I wanted to avoid to do it in two completely seperate steps, because it seems inefficient to compare the tables twice.

Answer

I thought in the WHERE clause after the CONFLICT I could define when that conflict is ‘triggered’. Is this not the case?

No. What defines the concflict is the target, the expression that immediatly follows the ON CONFLICT keyword. In your query, that is:

on conflict(cell_id, object_id)

…which is what you want – note that this require that you have a unique index on this tuple of column (or a primary key constraint). So you don’t need (nor want) this where clause.

Also, to access the column from the other table in the do update clause, you can use pseudo-table excluded.

Consider:

insert into reached_points(cell_id, object_id, cost)
select cell_id, object_id, reached_cost from test_reached_cells
on conflict(cell_id, object_id)
do update set cost = excluded.cost

Demo on DB Fiddle:

Sample data:

select * from reached_points;

cell_id | object_id | cost
------: | --------: | ---:
      1 |         2 |    3

select * from test_reached_cells;

cell_id | object_id | reached_cost
------: | --------: | -----------:
      1 |         2 |            4
      1 |         3 |            4

Query:

insert into reached_points(cell_id, object_id, cost)
select cell_id, object_id, reached_cost from test_reached_cells
on conflict(cell_id, object_id)
do update set cost = excluded.cost
-- 2 rows affected

Results:

select * from reached_points;

cell_id | object_id | cost
------: | --------: | ---:
      1 |         2 |    4
      1 |         3 |    4