ClickHouse / clickhouse-js

Official JS client for ClickHouse DB
https://clickhouse.com
Apache License 2.0
205 stars 25 forks source link

Allow to specify or exclude insert columns #218

Closed slvrtrn closed 7 months ago

slvrtrn commented 8 months ago

Summary

It is now possible to either specify a list of columns to insert the data into or a list of excluded columns:

// Generated query: INSERT INTO mytable (message) FORMAT JSONEachRow
await client.insert({
  table: 'mytable',
  format: 'JSONEachRow',
  values: [{ message: 'foo' }],
  columns: ['message'],
})

// Generated query: INSERT INTO mytable (* EXCEPT (message)) FORMAT JSONEachRow
await client.insert({
  table: 'mytable',
  format: 'JSONEachRow',
  values: [{ id: 42 }],
  columns: { exclude: ['message'] },
})

Added more examples:

Resolves https://github.com/ClickHouse/clickhouse-js/issues/217

NB: the EXCEPT clause works with ephemeral types rather strangely (see the tests). It is also possible to exclude every single column via HTTP + streams, and the query does not fail, though the data is not inserted - I couldn't simulate that via CLI as it does not allow empty values. It is also possible to exclude a column that does not exist (same in the CLI). All of this is to be investigated.

Checklist