pg_clickhouse と chdb のアップデート: エンコーディング、ネスト、データ型

1ERROR:  invalid byte sequence for encoding "UTF8": 0x81

1ALTER SERVER ch_server_name OPTIONS (ADD check_encoding 'replace');

1CREATE TABLE logs () WITH (
2    copy_from      = 's3://chdb-lakedata-public/logs/logs-2026-08-26.csv',
3    format         = 'CSVWithNames',
4    check_encoding = 'replace'
5);

1try=# SELECT * FROM ch_table ORDER BY id;
2 id |   name
3----+---------
4  1 | Barrack
5  2 | Ale�y
6  3 | Leopold
7  4 | An�n�e

1try=# SELECT * FROM ch_table ORDER BY id;
2 id |   name
3----+---------
4  1 | Barrack
5  2 | Aley
6  3 | Leopold
7  4 | Ann

1ALTER FOREIGN TABLE ch_table ALTER name TYPE bytea;

1try=# SELECT * FROM ch_table ORDER BY id;
2id |      name
3----+------------------
4  1 | \x4261727261636b
5  2 | \x416c650079
6  3 | \x4c656f706f6c64
7  4 | \x416e006e8165
8(4 rows)

1CREATE TABLE logs (
2    req_id    Int64                NOT NULL,
3    start_at  DateTime64(6, 'UTC') NOT NULL,
4    duration  IntervalMillisecond  NOT NULL,
5    resource  Text                 NOT NULL,
6    method    Enum8('GET' = 1, 'HEAD', 'POST', 'PUT', 'DELETE', 'PATCH') NOT NULL,
7    node_id   Int64                NOT NULL,
8    response  Int32                NOT NULL
9) ENGINE = MergeTree
10  ORDER BY start_at;

1try=# EXPLAIN (VERBOSE, COSTS OFF)
2 SELECT COUNT(*)
3   FROM logs
4  WHERE start_at + duration < date_trunc('day', now());
5                                                QUERY PLAN
6-----------------------------------------------------------------------------------------------------------
7 Foreign Scan
8   Output: (count(*))
9   Relations: Aggregate on (logs)
10   Remote SQL: SELECT count(*) FROM "default".logs WHERE (((start_at + duration) < toStartOfDay(now64())))
11(4 rows)

1SELECT * FROM things ORDER BY data ->> 'name';

1DB::Exception: Data types Variant/Dynamic are not allowed in ORDER BY keys, because it can lead to unexpected results.
2Consider using a subcolumn with a specific data type instead

1CREATE TABLE things (
2    id    Int32 NOT NULL,
3    data  JSON(
4      id      UInt32,
5      name    String,
6      size    Enum('small', 'medium', 'large'),
7      stocked Bool
8    ) NOT NULL
9) ENGINE = MergeTree PARTITION BY id ORDER BY (id);

1try=# SELECT * FROM things ORDER BY data ->> 'name';
2 id |                              data
3----+-----------------------------------------------------------------
4  4 | {"id": 4, "name": "doodad", "size": "large", "stocked": false}
5  3 | {"id": 3, "name": "gizmo", "size": "medium", "stocked": true}
6  2 | {"id": 2, "name": "sprocket", "size": "small", "stocked": true}
7  1 | {"id": 1, "name": "widget", "size": "large", "stocked": true}
8(4 rows)

1CREATE TABLE visits(
2    visit_id  UInt64,
3    user_id   UInt64,
4    goals     Nested(
5        serial    UInt32,
6        order_id  String
7    )
8) ENGINE = MergeTree ORDER BY visit_id SETTINGS flatten_nested = 0;

1try=# SELECT * FROM nest_bin.visits WHERE visit_id < 3 ORDER BY visit_id;
2 visit_id | user_id |      goals      
3----------+---------+-----------------
4        1 |       1 | {{1,xx},{2,yy}}
5(1 row)

1INSERT INTO visits
2VALUES (2, 2, ARRAY[ ['3', 'aa'], ['4', 'bb'] ]);

1Tuple(serial UInt32, order_id String)

1CREATE TYPE goal_type AS (serial bigint, order_id text);
2ALTER FOREIGN TABLE visits ALTER goals TYPE goal_type[];

1try=# SELECT * FROM nest_bin.visits WHERE visit_id < 3 ORDER BY visit_id;
2 visit_id | user_id |        goals        
3----------+---------+---------------------
4        1 |       1 | {"(1,xx)","(2,yy)"}
5        2 |       2 | {"(3,aa)","(4,bb)"}

1INSERT INTO visits
2VALUES (3, 3, ARRAY[row(5, 'jj'), row(6, 'zz')]::goal_type[]);

1CREATE TYPE event_status AS ENUM ('new', 'done');
2CREATE TYPE event_point AS (x integer, y integer);
3CREATE TYPE event_label AS (key text, value bigint);
4CREATE TYPE event_item AS (id integer, name text);
5
6CREATE TABLE events (
7    status event_status,
8    point  event_point,
9    labels event_label[],
10    items  event_item[]
11);

1COPY events FROM 's3://chdb-lakedata-public/examples/events.parquet' (
2    structure $$
3        status Enum8('new' = 1, 'done' = 2),
4        point  Tuple(Int32, Int32),
5        labels Map(String, Int64),
6        items  Array(Tuple(id Int32, name String))
7    $$
8);

1COPY events FROM 's3://chdb-lakedata-public/examples/events.parquet' (
2    structure $$
3        status Enum8('new' = 1, 'done' = 2),
4        point  Tuple(Int32, Int32),
5        labels Map(String, Int64),
6        items  Nested(id Int32, name String)
7    $$
8);