Don't let the minor version increment fool you: pg_clickhouse v0.11.1,
released last week, delivers a slew of improvements and bug fixes, including
UPDATE
, DELETE
, TRUNCATE
, and pgvector search pushdown.
pgvector type mapping
Let's start with the latter: pgvector type mapping. Given this ClickHouse table:
CALL clickhouse_perform('ch_server', $$
CREATE TABLE items (
id Int32,
category String,Array(Float32)
v = MergeTree ORDER BY (id);
) ENGINE $$);
pg_clickhouse v0.11.1 lets us map the v
column to the vector
type:
CREATE EXTENSION vector;
CREATE FOREIGN TABLE items (
int NOT NULL,
id NOT NULL,
category text 4) NOT NULL
v vector( ) SERVER ch_server;
We use an explicit CREATE FOREIGN TABLE rather than IMPORT FOREIGN SCHEMA
to map the ClickHouse Array(Float32)
column to a PostgreSQL vector
column.
It works for ClickHouse Array(Float64)
columns, too. Now we can insert
vector
array values:
INSERT INTO items VALUES (0, 'a', '[0,0,0,0]');
INSERT INTO items
SELECT i, (ARRAY['a', 'b', 'c'])[i % 3 + 1],
'[%s,%s,%s,%s]', sin(i), cos(i), sin(i * 2), cos(i * 3))::vector
format(FROM generate_series(1, 100) i;
And of course select them:
SELECT * FROM…