I have a table with the follow definition:
CREATE TABLE "test" (
id UUID PRIMARY KEY,
created TIMESTAMPTZ NOT NULL,
description TEXT,
permissions TEXT[]
);
I want to bulk insert into the table using unnest() and be able to ignore the update if there is a conflict:
INSERT INTO test (id, created, description, permissions)
SELECT unnest($1::uuid[]), unnest($2::timestamptz[]), unnest($3::text[]), unnest($4::text[][])
ON CONFLICT (user_id, session_id, confirmation_code_hash) DO NOTHING
The arguments passed in are:
- Array of UUIDs
- Array of timestamps
- Array of texts
- Array of array of texts
The problem is that unnest() flattens the array of array of texts completely, where as I would just like to flatten the first dimension and insert an array of text into each column.
Is there any way to use unnest() for bulk insertion of array columns?
ON CONFLICTclause seems to refer to columns that don't exist in your example. (Shouldn't be relevant to your question though)unnest()s will zip the arrays, i.e. iterate them in parallel, see What is the expected behaviour for multiple set-returning functions in SELECT clause?unnest()per array also seems to be slightly faster than unnesting all in one call, with the variadicunnest(): dbfiddle.uk/gR7Y2NLR