Append ARRAY with Data Chunk

[nshiab]

Hi!

I am trying to append float arrays by tweaking this code example, but without success...

await connection.run(
  `create or replace table target_table(i integer, v varchar)`
);

const appender = await connection.createAppender('target_table');

const chunk = DuckDBDataChunk.create([INTEGER, VARCHAR]);
chunk.setColumns([
  [42, 123, 17],
  ['duck', 'mallad', 'goose'],
]);
// OR:
// chunk.setRows([
//   [42, 'duck'],
//   [123, 'mallard'],
//   [17, 'goose'],
// ]);

appender.appendDataChunk(chunk);
appender.flushSync();

How would you insert a third column with this data:

[ [1.23, 2.45, 3.45], [1.23, 2.45, 3.45], [1.23, 2.45, 3.45] ]

Also, do you always have to create a new table, or can you add just another column to an existing table?

Thank you! 🙏

[jraymakers]

To add a third column of type FLOAT[3] (i.e. ARRAY of FLOAT, length 3):

const chunk = DuckDBDataChunk.create([INTEGER, VARCHAR, ARRAY(FLOAT, 3)]);
chunk.setColumns([
  [42, 123, 17],
  ['duck', 'mallad', 'goose'],
  [ arrayValue([1.23, 2.45, 3.45]), arrayValue([1.23, 2.45, 3.45]), arrayValue([1.23, 2.45, 3.45]) ]
]);

Note that you need to wrap the JS arrays with arrayValue. This is because DuckDB supports multiple array-like data types. ARRAY values are for fixed-length, while LIST values are variable-length. To add a third column of type FLOAT[] (i.e. LIST of FLOAT, variable length), you'd do:

const chunk = DuckDBDataChunk.create([INTEGER, VARCHAR, LIST(FLOAT)]);
chunk.setColumns([
  [42, 123, 17],
  ['duck', 'mallad', 'goose'],
  [ listValue([1.23, 2.45, 3.45]), listValue([1.23, 2.45, 3.45]), listValue([1.23, 2.45, 3.45]) ]
]);

[jraymakers]

Note that, in the above, you also need to change the CREATE TABLE statement to add the new column, as in:
create or replace table target_table(i integer, v varchar, array float[3])
or
create or replace table target_table(i integer, v varchar, list float[])

[jraymakers]

To your second question: You don't need to create a new table to use the appender. You just need to pass the name of a table that has the appropriate column types - those matching what you intend to append.

[nshiab]

Thank you so much, @jraymakers ! It works! :)