patternsqlMinor
Include non-inserted rows as result of INSERT INTO ... RETURNING
Viewed 0 times
resultrowsinsertnonincludeintoinsertedreturning
Problem
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a
For example
should result in the table
but the output of the query should be:
This example fails due to a syntax error:
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.)
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.)
Solution
I would simply separate the query logic and the insert logic:
Online example
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
Code Snippets
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;Context
StackExchange Database Administrators Q#263970, answer score: 4
Revisions (0)
No revisions yet.