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

Postgresql – Upsert into a table and deleting a row from another table

I am doing an upsert into say table1 with a composite primary key on col1, col3. I understand I can do it as

insert into table1 (col1, col2, col3, col4) 
values (val1, val2, val3, val4) 
    on conflict(col1, col3) do update set ......

I am aware of the notion of Excluded and all.

My dilemma is that when this conflict happens, I want to delete a row from table2 that has specific values in corresponding columns in table1. How do I do this in a single upsert query?

Tags:

2

Answers


  1. 0

    In PostgreSQL, you can achieve this by using a Common Table Expression (CTE) within the ON CONFLICT DO UPDATE clause. Here’s how you can structure the query to perform the upsert into table1 and delete a row from table2 based on the conflict:

    WITH upsert_data AS (
  INSERT INTO table1 (col1, col2, col3, col4)
  VALUES (val1, val2, val3, val4)
  ON CONFLICT (col1, col3)
  DO UPDATE SET col2 = EXCLUDED.col2, col4 = EXCLUDED.col4
  RETURNING col1, col3
)
DELETE FROM table2
WHERE table2.col1 = (SELECT col1 FROM upsert_data)
AND table2.col3 = (SELECT col3 FROM upsert_data);
    Login or Signup to reply.
  2. 0

    I wonder why not use 2 separate queries: One with DELETE and the other with INSERT, but
    I think using CTE is good option:

    WITH inserting(col1, col2, col3, col4) AS (
    VALUES (1, 2, 3, 4) 
),
deleting AS (
    DELETE FROM table1 AS t USING inserting i
    WHERE t.col1 = i.col1 AND t.col3 = i.col3
)
INSERT INTO table1 (col1, col2, col3, col4) TABLE inserting;

    Here is fiddle: https://www.db-fiddle.com/f/2kibo6nMux3BBbuSRAhDgj/0

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search