Thread: Multitable insert syntax support on Postgres?
Hello
Are there any plans to incorporate a formal syntax multitable/conditional insert , similar to the syntax below? snowflake does have the same feature
Today, im resorting to a function that receives the necessary parameters from the attributes definition/selection area in a sql select query, called by each tuple retrieved. A proper syntax show be real cool
Thanks!
Hi, On 2022-11-14 21:06:09 -0300, Alexandre hadjinlian guerra wrote: > Hello > Are there any plans to incorporate a formal syntax multitable/conditional > insert , similar to the syntax below? snowflake does have the same feature > > https://oracle-base.com/articles/9i/multitable-inserts > > Today, im resorting to a function that receives the necessary parameters > from the attributes definition/selection area in a sql select query, called > by each tuple retrieved. A proper syntax show be real cool I only skimmed that link, but afaict most of this can be done today in postgres, with a bit different syntax, using CTEs. Postgres implements this as an extension to the standard CTE syntax. WITH data_src AS (SELECT * FROM source_tbl), insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5), insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5) INSERT INTO c SELECT * FROM data_src WHERE d < 5 It's a bit annoying that the last "arm" of the insert looks a bit difference. OTOH, it's a much more general syntax. Greetings, Andres Freund
On Mon, Nov 14, 2022 at 7:06 PM Alexandre hadjinlian guerra <alexhguerra@gmail.com> wrote:
HelloAre there any plans to incorporate a formal syntax multitable/conditional insert , similar to the syntax below? snowflake does have the same featurehttps://oracle-base.com/articles/9i/multitable-insertsToday, im resorting to a function that receives the necessary parameters from the attributes definition/selection area in a sql select query, called by each tuple retrieved. A proper syntax show be real coolThanks!
I'm not aware of any efforts to implement this at this time, mostly because I don't think it's supported in the SQL Standard. Being in the standard would change the question from "why" to "why not".
I've used that feature when I worked with Oracle in a data warehouse situation. I found it most useful when migrating data dumps from mainframes where the data file contained subrecords and in cases where one field in a row changes the meaning of subsequent fields in the same row. That may sound like a First Normal Form violation, and it is, but such data formats are common in the IBM VSAM world, or at least they were in the data dumps that I had to import.
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5)
INSERT INTO c SELECT * FROM data_src WHERE d < 5
I suppose you could just do a dummy SELECT at the bottom to make it look more symmetrical
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5)
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5)
SELECT true AS inserts_complete;
Or maybe get some diagnostics out of it:
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5 RETURNING NULL),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5 RETURNING NULL),
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5 RETURNING NULL)
SELECT
(SELECT COUNT(*) FROM insert_a) AS new_a_rows,
(SELECT COUNT(*) FROM insert_b) AS new_b_rows,
(SELECT COUNT(*) FROM insert_c) AS new_c_rows;
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5)
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5)
SELECT true AS inserts_complete;
Or maybe get some diagnostics out of it:
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5 RETURNING NULL),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5 RETURNING NULL),
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5 RETURNING NULL)
SELECT
(SELECT COUNT(*) FROM insert_a) AS new_a_rows,
(SELECT COUNT(*) FROM insert_b) AS new_b_rows,
(SELECT COUNT(*) FROM insert_c) AS new_c_rows;