SQL query using Common Table Expression (CTE) not returning results in some cases

Viewed 49

I am using a CTE to insert a row and then a SELECT/UNION/SELECT clause to fetch the inserted row (or if the row already exists, return that row).

I want this query to always be returning a row but I'm finding there are certain cases where nothing is returned. I've looked into the cases where nothing is returned and see nothing wrong with the params being fed to the query.

WITH inserted AS (
    INSERT INTO records 
            (field1, field2, field3)
    VALUES 
            (?, ?, ?)
    ON CONFLICT ON CONSTRAINT records_field1_field2_field3_pk
    DO UPDATE
    SET color = NULL, shape = 'NONE' 
    WHERE records.color IS NOT NULL OR records.shape <> 'NONE'
    RETURNING *
)
SELECT
    inserted.id             AS id,
    inserted.field1         AS field1,
    inserted.color          AS color,
    backup.id               AS backup_id,
FROM inserted
LEFT JOIN backup ON inserted.user_id = backup.id
UNION
SELECT
    records.id              AS id,
    records.field1          AS field1,
    records.color           AS color,
    backup.id               AS backup_id,
FROM records
LEFT JOIN backup ON records.user_id = backup.id
WHERE field1 = ? AND field2 = ? AND field3 = ?
ORDER BY color DESC;

Additional info:

CONSTRAINT records_field1_field2_field3_pk UNIQUE (field1, field2, field3)

I'm using PG v13.

Please let me know if you need any additional information.

0 Answers
Related