Seems that unnest does not support arrays

[oxilor]

Let's say we have the following table:

CREATE TABLE roles (
  name varchar(100) NOT NULL,
  file_types text[] DEFAULT ARRAY[]::text[] NOT NULL
);

and we want to use unnest to populate the table with data (only one row just for simplicity):

const data = await pool.any(sql.unsafe`
    INSERT INTO roles (name, file_types)
    SELECT *
    FROM ${sql.unnest(
      [['name', ['IMAGE']]],
      ['text', sql.fragment`text[]`] // 'text[]' also does not work because it generates $2::"text[]"[]
    )}
    RETURNING *
`);

In this case, the following error is occured:

error: column "file_types" is of type text[] but expression is of type text

Generated sql:

INSERT INTO roles (name, file_types)
SELECT *
FROM unnest($1::"text"[], $2::text[][])
RETURNING *

Generated values:

[[ 'name' ], [ [ 'IMAGE' ] ] ]

P.S. Inserting rows using INSERT INTO roles VALUES (${'name'}, ${sql.array(['IMAGE'], 'text')}) works fine.

[gajus]

Consider this pattern instead #481 (comment)