1

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.

Dylan
  • 43
  • 5
  • All appearances are this should result in a row every time. For text purposes, can you repeat this error without the left joins? It's not important except to simplify the problem. Secondly, can you demonstrate a field1, 2, 3 combo that fails? Show what's in the table. My only guess is that you have six parameters, and one would assume that 1 and 4, 2 and 5, 3 and 6 should line up but do not. If you used named parameters, that would guarantee that could not happen. – Hambone Sep 16 '22 at 19:47

0 Answers0