[Bug] Inserting data Clickhouse `RETURNING is not supported`

[bastiaanv]

Bug report

Describe the bug

When inserting data via Supabase-js / Postgrest, I get a 500 error response with the following body:

{
    "code": "HV000",
    "details": "Wrappers",
    "hint": null,
    "message": "RETURNING is not supported"
}

To Reproduce

Clickhouse table:

CREATE TABLE glucose
(
    `timestamp` DateTime64(3, 'Europe/Amsterdam'),
    `profile_id` String,
    `sensor_id` String,
    `glucose_mmol` Float32,
    `glucose_mg` Int16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, profile_id);

Postgres table:

create server clickhouse_server
  foreign data wrapper clickhouse_wrapper
  options (
    conn_string 'tcp://default:@localhost:9000/default'
  );

create foreign table glucose (
  timestamp timestamp,
  profile_id text,
  sensor_id text,
  glucose_mmol real,
  glucose_mg integer
)
  server clickhouse_server
  options (
    table 'glucose',
    rowid_column 'timestamp'
  );

You can reproduce this error by trying to insert the following data (but any data will do):

const uploadData = {"sensor_id":"b4d0f5a6-5fe3-4341-90c2-c18baedeb31f","timestamp":"2023-05-20T19:34:00.000Z","glucose_mg":222,"glucose_mmol":12.3,"profile_id":"c7922835-bdc9-49eb-a3f5-0d3013c67bbf"};
Supabase.from('glucose').insert(uploadData)

Expected behavior

To insert the data and not give me an error

System information

• OS: MacOs
• Browser: Safari iOS
• Version of supabase-js: v2.21.0
• Version of Node.js: v19.6.1

[burmecia]

That's an expected result. Supabase uses PostgREST as API server which uses RETURNING for all insert sql statements, but Wrappers doesn't support RETURNING clause at this moment. Would it possible encapsulate the insert into a function, and call that function instead?

[bastiaanv]

Yes, that worked. Thanks!

Do you think postgrest can be configured to work without the returning?

[evokelektrique]

I have the same issue. I resolved it by using the official ClickHouse JavaScript client for inserting data.