Unnest multiple arrays into rows

Question

I have been shown a great way to bulkinsert with this example:

WITH p AS (
    INSERT INTO parent_table (column_1) 
    SELECT $1 
    RETURNING id) 
INSERT INTO child_table (parent_table_id, column_a) 
SELECT p.id, a 
FROM   p, unnest($2::text[]) AS a

However, I need to insert multiple rows from multiple arrays, so I tried this syntax:

WITH p AS (
    INSERT INTO parent_table (column_1) 
    SELECT $1 
    RETURNING id) 
INSERT INTO child_table (parent_table_id, column_a, column_b) 
SELECT p.id, a, b 
FROM   p, unnest($2::text[]) AS a, unnest($3::bigint[]) AS b

I have a primary key on parent_table_id and column_a, and when I try to execute this query, Postgres complains of a duplicate key violation.

How should the arrays be unwrapped so that they form individual rows?

In other words, if $2 and $3 both have two entries, how can the first entry of $2 only be inserted with the first entry of $3 and the same for the respective second entries?

If this is not possible, can I construct a multidimensional array? If so, how should it be passed with multiple array types, and what is the multidimensional array syntax?

Answer 1

This would do what you desire:

WITH p AS (
    INSERT INTO parent_table (column_1) 
    SELECT $1 
    RETURNING id) 
INSERT INTO child_table (parent_table_id, column_a, column_b) 
SELECT p.id, t.a, t.b 
FROM   p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t

The subtle difference here is that unnest() calls in the same SELECT list are unnested in parallel if the number of elements is identical. Careful though: In Postgres 9.6 or older, if the number is not the same, it results in a Cartesian product instead. The behavior was sanitized in Postgres 10. See:

• What is the expected behaviour for multiple set-returning functions in select clause?
• Is there something like a zip() function in PostgreSQL that combines two arrays?

You could use a cleaner form with generate_subscripts() or other techniques, but those would be much more verbose. Details in this related question:

• How to preserve the original order of elements in an unnested array?

Postgres 9.4

The new WITH ORDINALITY in Postgres 9.4 allows a much cleaner (and only moderately more verbose) form for this:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, ta.a, tb.b 
FROM   p
     , unnest($2::text[]) WITH ORDINALITY AS ta(a, rn)
JOIN   unnest($3::bigint[]) WITH ORDINALITY AS tb(b, rn) USING (rn);

And this special case can be even simpler with the new form of unnest() that accepts multiple arrays:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, t.a, t.b 
FROM   p, unnest($2::text[], $3::bigint[]) AS t(a, b);

Example in this related answer.

Answer 2

I solved with:

INSERT INTO foo(Val,i,j)
SELECT myArr[i][j] ,i,j 
FROM Generate_Series(1,lbound) i 
JOIN Generate_Series(1,ubound) j
    ON true