Unnest multiple arrays into rows 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?
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.