Referential integrity is enforced at the end of the statement by default. Strictly speaking, both inserts happen "in parallel", but since this is a single statement, default FOREIGN KEY constraints will not complain. The final outer INSERT can now insert a foo_id for every row: either the type pre-existed, or it was inserted in step 2. The 2nd CTE ins inserts distinct new types ( foo_id IS NULL) into foo, and returns the newly generated foo_id - together with the type to join back to insert rows. All other rows get foo_id IS NULL this way. Immediately LEFT JOIN to foo to append the foo_id for pre-existing type rows. The subquery val with the VALUES expression can be replaced with a table or subquery as source. The 1st CTE sel provides multiple rows of input data. Two rows to illustrate the need for DISTINCT in the first INSERT statement. Both are purple, which does not exist in foo, yet. Old sqlfiddle for Postgres 9.6 - works the same in 9.1. SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id) SELECT DISTINCT type FROM sel WHERE foo_id IS NULL SELECT val.description, val.type, f.id AS foo_id To create missing entries in foo on the fly, in a single SQL statement, CTEs are instrumental: WITH sel AS ( It's enough to do it in the first row, the rest falls in line. ), types cannot be derived and default data types are used unless typed explicitly. Since the VALUES expression is not directly attached to a table (like in INSERT. Subqueries are typically a bit faster when none of the above is needed. Common Table Expressions offer additional features and are easier to read in big queries, but they also pose as optimization barriers (up to Postgres 12). The VALUES expression in the subquery does the same as CTE. Instead, NULL is entered for foo_id (which raises an exception if the column is defined NOT NULL). LEFT JOIN instead of JOIN means that rows from val all rows are kept, even when no match is found in foo. , ('new row1', 'purple') - purple does not exist in foo, yet (text 'testing', text 'blue') - explicit type declaration see below Plain INSERT INSERT INTO bar (description, foo_id)
0 kommentar(er)
