I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a
null for each row of the input which did not result in an insertion.
CREATE TABLE all_sums (sum INTEGER); SELECT CASE WHEN a_sum IS NULL THEN null ELSE (SELECT sum FROM (INSERT INTO sums (sum) VALUES (sum) RETURNING sum)) END FROM (SELECT a + b FROM (VALUES (1, null), (null, 2), (2, 3)) AS row (a, b)) AS a_sum;
should result in the table
all_sums looking like:
all_sums: sum ------ 5 (1 row)
but the output of the query should be:
null null 5 ------ (3 rows)
This example fails due to a syntax error:
ERROR: syntax error at or near "INTO"
Is there any way to achieve the desired query output?
(For context: My reason for doing this is because there are further queries which rely on knowing whether the insertion occurred for a particular row.
This is part of an effort to insert some data more efficiently from a file by transposing my queries from one per row to one per column. I'm not looking for other tips in improving insert speed though and if it's not possible I'm happy to call it a day at this point.)