1

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?

12
  • "SELECT unnest($1::uuid[]), unnest($2::timestamptz[]) ..." this expression assumes the Cartesian product of these arrays. Which this contradicts ON CONFLICT. Perhaps you need to "INSERT ... SELECT $1[i],$1[i],$3[1],$4[ i] .. FOR i=1..N" ? Commented Nov 21 at 10:53
  • Your ON CONFLICT clause seems to refer to columns that don't exist in your example. (Shouldn't be relevant to your question though) Commented Nov 21 at 11:14
  • @ValNik No, the 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? Commented Nov 21 at 11:18
  • @Bergi, thank you. I didn't notice the difference in the query syntax. Commented Nov 21 at 11:25
  • 1
    OP's single unnest() per array also seems to be slightly faster than unnesting all in one call, with the variadic unnest(): dbfiddle.uk/gR7Y2NLR Commented Nov 21 at 14:24

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.