0

I've crafted what seems to be a rather clever query for only inserting records that don't exist, and then returning the ids for all inserted data rows, with each row's PK id, whether it's been inserted or not (avoiding a lot of the problems that come with ON CONFLICT... RETURNING id clauses). The only problem is, it doesn't return the data that's been inserted, which doesn't make sense to me:

In postgres 12.3:

CREATE TABLE target_table (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  value JSONB NOT NULL,
  UNIQUE (name, value)
);

INSERT INTO target_table(name, value) VALUES ('asdf', '18446744073709551615');
INSERT INTO target_table(name, value) VALUES ('asdf', 'null');


-- merge query
WITH incoming AS (
    SELECT * FROM (VALUES ('asdf','null'::JSONB), ('asdf', '18446744073709551615'::JSONB), ('asdaf', '3'::JSONB)) AS tmp(name, value)
)
, merge_op AS (
    INSERT INTO target_table(name, value)
    SELECT i.name, i.value
    FROM incoming i
    LEFT JOIN target_table er
        ON er.name = i.name
        AND er.value = i.value
    WHERE er.name IS NULL
    ON CONFLICT DO NOTHING
)
SELECT er.id, er.name, er.value
FROM target_table er
JOIN incoming i ON i.name = er.name
AND i.value = er.value;

Since this is all one statement, it all happens in the same transaction, so I'd think that the last SELECT statement would be able to 'see' the inserted records and join appropriately. What happens is, the last record (with a value of 3) doesn't get returned on the first run of the merge query; only the first two existing records are returned. A subsequent run (inserting 0 records this time) will return all incoming records.

Short of running the last SELECT in a separate query, can this be done? Any unforeseen drawbacks to this approach?

1 Answer 1

2

Quote from the manual

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. (... ) and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

(emphasis mine)

So you need a RETURNING clause in your merge_op clause to see the inserted rows.

-- merge query
WITH incoming (name, value) AS (
  VALUES 
    ('asdf','null'::JSONB), 
    ('asdf', '18446744073709551615'::JSONB), 
    ('asdaf', '3'::JSONB)
)
, merge_op AS (
    INSERT INTO target_table(name, value)
    SELECT i.name, i.value
    FROM incoming i
    LEFT JOIN target_table er
        ON er.name = i.name
        AND er.value = i.value
    WHERE er.name IS NULL
    ON CONFLICT DO NOTHING
    returning *
)
SELECT er.id, er.name, er.value
FROM merge_op er;

If you also want to see the rows from incoming that were not inserted (e.g. due to a conflict) you need an outer join:

SELECT er.id, i.name, i.value
FROM incoming i 
  LEFT JOIN merge_op er 
         ON i.name = er.name
        AND i.value = er.value;
8
  • The objective was to get the whole set of incoming values, augmented with their (either existing or newly created) surrogate ids. It looks like that can be accomplished with a UNION? SELECT er.id, er.name, er.value FROM target_table er JOIN incoming i ON i.name = er.name AND i.value = er.value UNION SELECT * FROM merge_op
    – BrDaHa
    Mar 5, 2022 at 19:23
  • the order in which the specified updates actually happen is unpredictable is unintuitive at first, but I suppose that makes sense.
    – BrDaHa
    Mar 5, 2022 at 19:25
  • I re-worded the question; after a re-read it seems I expressed the expected output incorrectly.
    – BrDaHa
    Mar 5, 2022 at 19:36
  • The LEFT JOIN will return the whole set of "incoming" values.
    – user1822
    Mar 5, 2022 at 20:33
  • But not with the new ids for the inserted values. If records exist: 1 | a, 2 | b and I insert a c, what I wanted returned is 1 | a, 2 | b, 3 | c
    – BrDaHa
    Mar 6, 2022 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.