Open Blaumaus opened 2 years ago
I'm trying to do the same with an alter table update
statement.
create table asset (
id String,
class_id Nullable(UInt32)
)
engine = MergeTree
primary key (id);
await ch.query(
`alter table asset update class_id = {class_id:Nullable(UInt32)} where id = {id:String}`,
{ params: { id: 'test', class_id: null } }
).toPromise()
I get this error
TypeError: Cannot read properties of null (reading 'toString')
at ./node_modules/clickhouse/index.js:499:32
https://github.com/TimonKK/clickhouse/blob/master/index.js#L499 Perhaps this line can change to whatever syntax is needed for null via http? I've had a look through the clickhouse documentation and I can't understand how to pass a null value correctly. I've tried 'Null' and '' (empty string) which don't work.
I've worked around this by using:
await ch.query(
`alter table asset update class_id = Null where id = {id:String}`,
{ params: { id: 'test' } }
).toPromise()
I would prefer it to be data driven if possible.
I'm trying to insert some value into table via the prepared statement feature, similarly to this.
This is the array of data I'm trying to insert (some of the values are JSON null)
```javascript const data = [ { id: '6c7d1f07-4ebb-44c2-b62b-d8f4c2c4b8cd', pid: 'cp7cr6v1ryEC', pg: '/home/rand/dev/analytics/test_analytics.html', dv: 'desktop', br: 'Chrome', os: 'Linux', lc: 'en-GB', ref: null, so: null, me: null, ca: null, lt: null, cc: 'FI', unique: 0, created: '2022-04-20 21:14:01' }, { id: '67537e16-4a92-445b-8d35-cad802958194', pid: 'cp7cr6v1ryEC', pg: '/home/rand/dev/analytics/test_analytics.html', dv: 'desktop', br: 'Firefox', os: 'Windows', lc: 'en-GB', ref: null, so: null, me: null, ca: null, lt: null, unique: 0, created: '2022-04-20 21:14:11' } ] ```The query itself looks like this
```javascript const query = 'INSERT INTO analytics (id, pid, pg, dv, br, os, lc, ref, so, me, ca, lt, cc, unique, created)' await clickhouse.query(query, data).toPromise() ```The table schema
```sql CREATE TABLE IF NOT EXISTS analytics.analytics ( id UUID, pid FixedString(12), pg Nullable(String), dv Nullable(String), br Nullable(String), os Nullable(String), lc Nullable(String), ref Nullable(String), so Nullable(String), me Nullable(String), ca Nullable(String), lt Nullable(UInt16), cc Nullable(FixedString(2)), unique UInt8, created DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(created) ORDER BY (id, created, pid); ```When I execute the query above, the data is stored like this (instead of![image](https://user-images.githubusercontent.com/35810911/164325574-7e31fc7e-dd1f-429c-b64b-372b6605f698.png)
null
it stores the column as an empty string):I want it to be stored as
null
. Instead of JavaScriptnull
I tried supplying something like '\N', '\0', 'NULL' - none of this seems to work like I want it to.Is there a way to insert
null
-able values via the prepared statements? If yes, how?