Postgres | Execute delete cte after insert cte

Question

I have a table:

CREATE TABLE product
(
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_id bigint NOT NULL,
    to_id   bigint NOT NULL,
    comments text NOT NULL,
    data    jsonb  NOT NULL
);

CREATE UNIQUE INDEX product_unique_idx ON product(from_id, to_id, comments);

With the following data:

insert into product(from_id, to_id, comments, data) values
(1, 2, 'bla', '{}'),
(2, 3, 'bla', '{}'),
(1, 3, 'bla', '{}'),
(3, 2, 'bla', '{}'),
(2, 1, 'bla', '{}'),
(3, 1, 'bla', '{}');

Now, I want to insert new records that will update from_id & to_id with a given collection, for example replace all from_id & to_ids [1,2] with 3 (also delete where from_id == to_id), because of the UNIQUE INDEX I do it via insert and then delete all other rows:

with
   insert_stmt_to_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select from_id,3,comments,data from product
           where to_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING),
   insert_stmt_from_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select 3,to_id,comments,data from product
           where from_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING),
   delete_stmt AS (DELETE from product where to_id in (1,2) or from_id in (1,2) RETURNING *)
select * from delete_stmt

But after select * from product, I get (there are [1, 2] in from+it\to_id):

How is it?

I am a bit confused about what you are trying to achieve. Is the expected result, that all rows have `(from_id, to_id) = (3,3)` after this? Also: a column named `comments` as part of a unique constraint seems a bit strange — , Jul 18 '22 at 06:10
@a_horse_with_no_name, maybe I didn't mentioned it, duplicates should removed as well. about the comments, yes, it's part of the unique constraint — Shay Zambrovski, Jul 18 '22 at 06:13

score 0 · Answer 1 · answered Jul 18 '22 at 10:42

So I've managed to do so:

with
   delete_duplicates as (
      delete from product where
         (from_id in (1,2) and to_id in (1,2)) or
         (from_id = 3 and to_id in (1,2)) or
         (from_id in (1,2) and to_id  = 3)
      RETURNING id),
   insert_stmt_from_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select 3,to_id,comments,data from product
           where from_id in (1,2) and not id in (select id from delete_duplicates))
       ON CONFLICT (from_id, to_id, comments) DO NOTHING RETURNING id),
   insert_stmt_to_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select from_id,3,comments,data from product
           where to_id in (1,2) and not id in (select id from delete_duplicates))
       ON CONFLICT (from_id, to_id, comments) DO NOTHING RETURNING id),
   delete_leftovers_stmt AS (DELETE from product where from_id in (1,2) or to_id in (1,2) RETURNING id)
select id from delete_duplicates union select id from delete_leftovers_stmt;

score 0 · Answer 2 · answered Jul 18 '22 at 16:20

You need a transaction, so that all commands are run in an extra state and it will give you an answer with only one as result

BEGIN;
      insert into product
         (from_id, to_id, comments, data)
         (select from_id,3,comments,data from product
           where to_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING;

      insert into product
         (from_id, to_id, comments, data)
         (select 3,to_id,comments,data from product
           where from_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING;
    DELETE from product where to_id in (1,2) or from_id in (1,2);         
COMMIT;

✓

1 rows affected

✓

6 rows affected

✓

SELECT * FROM product

id | from_id | to_id | comments | data
-: | ------: | ----: | :------- | :---
 8 |       3 |     3 | bla      | {}

db<>fiddle here

Postgres | Execute delete cte after insert cte

2 Answers2