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.