inserting data from generate_series into motherduck table returns error

[wuputah]

Description

Given:

CREATE TABLE md (i integer) USING duckdb;

An insert using generate_series call will fail as follows:

INSERT INTO md SELECT generate_series(1, 1000);

ERROR:  (PGDuckDB/Duckdb_ExecCustomScan) Conversion Error: Unimplemented type for cast (BIGINT[] -> INTEGER)
LINE 1: INSERT INTO my_db.main.md (i) SELECT generate_series(1, 1000) AS generate...

Most likely, this would have to be fixed in duckdb itself, since I think this is a mismatch between Postgres and DuckDB behavior, but posting here first.

[Y--]

I looked a bit into this and got confused since SELECT generate_series(1,1000) doesn't return the same thing:

• in DuckDB, it returns one row which contains an array of 1000 integers
• in Postgres, it returns 1000 rows of integers

This however works:

y=# INSERT INTO md SELECT * FROM generate_series(1, 1000);
INSERT 0 0
y=# select count(*) from md;
 count
-------
  1000
(1 row)

Using unnest would have worked too but we don't support this function:

y=# INSERT INTO md SELECT unnest(generate_series(1, 1000));
ERROR:  function unnest(integer) does not exist
LINE 1: INSERT INTO md SELECT unnest(generate_series(1, 1000));
                               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I am not sure it is a DuckDB bug since strictly speaking we're trying to insert an integer[] in an integer column (even though the intent is likely to "unnest" automatically).

Should we add support for unnest?