Postgresql – Include non-inserted rows as result of INSERT INTO … RETURNING

caseinsertpostgresqlpostgresql-11subquery

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.

For example

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.)

Best Answer

I would simply separate the query logic and the insert logic: 

with vals (a,b) as (
  VALUES (1, null), (null, 2), (2, 3)
), new_rows as (
  insert into all_sums (sum)
  select a + b
  from vals
  where a + b is not null
)
select a + b
from vals;

Online example

Related Question