how can I translate a query to CTE?

Question

I'm still having trouble understanding how CTE works.

I'm looking to make an insert. In case of conflict I use the on conflict do nothing but I want it to return the id to me (for the success of the insert or the conflict)

WITH inserted AS (
    INSERT INTO fiche(label) 
    VALUES ('label') 
    ON CONFLICT (label) DO NOTHING
    RETURNING *
)
SELECT * FROM inserted
WHERE NOT EXISTS (SELECT 1 FROM inserted);

From docs [INSERT](https://www.postgresql.org/docs/current/sql-insert.html): *The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned.* So you will only get values returned if the `INSERT` succeeds not if it fails. — Adrian Klaver, Jan 16 '23 at 21:21
What is the `id` of the table? Is that an auto-generated value with a constraint (such as a primary key or `not null`)? Under what conditions will there be a conflict upon insert? — Patrick, Jan 16 '23 at 21:28
Not sure what you mean by "*translate a query to CTE?*" - you already have a CTE! — Bergi, Jan 16 '23 at 22:49
As for your actual problem, see https://dba.stackexchange.com/questions/129522/how-to-get-the-id-of-the-conflicting-row-in-upsert https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql https://stackoverflow.com/questions/40323799/return-rows-from-insert-with-on-conflict-without-needing-to-update — Bergi, Jan 16 '23 at 22:52

SebDieBln · Answer 1 · 2023-01-17T09:33:18.690

0

Note that

SELECT * FROM some_relation
WHERE NOT EXISTS (SELECT 1 FROM some_relation);

will always give you an empty result. Either some_relation is empty itself or if it is not empty SELECT 1 FROM some_relation is not empty and therefore NOT EXISTS ... always returns false and so no record is matching the WHERE clause.

What you want is to have the VALUES as a CTE. You can then reference the values from your INSERT statement and in a SELECT to compare those values to the result of the RETURNING clause.

WITH
vals AS (
    VALUES ('label')
),
inserted AS (
    INSERT INTO fiche(label) 
    SELECT * FROM vals
    ON CONFLICT (label) DO NOTHING
    RETURNING label, id
)
SELECT
    vals.column1,
    inserted.id
FROM vals
LEFT JOIN inserted ON vals.column1 = inserted.label

This should give you a row for each row in your VALUES clause and the second column will be NULL if it was not inserted due to a conflict or the inserted ID otherwise.

edited Jan 17 '23 at 09:33

answered Jan 16 '23 at 21:21

SebDieBln

3,303
1
7
21

Except the OP is looking for the `id` of the existing row, not the value proposed for insertion. – Adrian Klaver Jan 16 '23 at 21:24
@AdrianKlaver Oh yes, that is right. I modified my answer accordingly, assuming the name `id` for the column. – SebDieBln Jan 17 '23 at 09:34
Except the `DO NOTHING` means nothing will be returned in the conflict case. Therefore no `id`. See my comment to the question, also the docs [INSERT](https://www.postgresql.org/docs/current/sql-insert.html). – Adrian Klaver Jan 17 '23 at 15:56

how can I translate a query to CTE?

1 Answers1