7

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?

11

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:

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

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.

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy