postgresql

允许对存储在远程 PostgreSQL 服务器上的数据执行 SELECT 和 INSERT 查询。

语法

postgresql({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]} [, SETTINGS name=value, ...])

参数

参数	描述
`host:port`	PostgreSQL 服务器地址。
`database`	远程数据库名称。
`table`	远程表名称，或原样传递给 PostgreSQL 的查询 (参见传递查询而非表名) 。
`user`	PostgreSQL 用户。
`password`	用户密码。
`schema`	非默认的表 schema。可选。
`on_conflict`	冲突解决策略。示例：`ON CONFLICT DO NOTHING`。可选。

参数也可以通过命名集合传递。在这种情况下，需要分别指定 host 和 port。建议在生产环境中使用这种方式。 TLS/SSL 参数会转发给 libpq，可作为命名集合键或尾随的键值参数提供：sslmode (disable、allow、prefer、require、verify-ca 或 verify-full；未设置时，使用 libpq 的默认值 prefer) ，以及以下两种形式之一的证书和私钥。sslrootcert (CA 证书或特殊值 system) 、sslcert (客户端证书) 和 sslkey (客户端私钥) 是服务器本地文件的路径，只能在服务器配置文件中定义的命名集合中指定。sslrootcert_pem、sslcert_pem 和 sslkey_pem 则接受相应文件的字面内容——例如，postgresql('host:port', 'database', 'table', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...')——并且会像密码一样在日志和 SHOW 查询中被屏蔽。

返回值

一个表对象，包含与原始 PostgreSQL 表相同的列。

在 INSERT 查询中，为了将表函数 postgresql(...) 与带列名列表的表名区分开，必须使用关键字 FUNCTION 或 TABLE FUNCTION。请参见下面的示例。

设置

postgresql 表函数 (以及 PostgreSQL 表引擎) 使用的连接池，可以通过在语句末尾添加 SETTINGS 子句来配置。未指定某项设置时，默认采用对应查询级别 postgresql_* 设置的值。有关 postgresql_connection_pool_* 和 postgresql_connection_attempt_timeout 设置及其默认值的完整列表，请参见表引擎的设置部分。示例：

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password', SETTINGS postgresql_connection_pool_size = 32);

Implementation Details

PostgreSQL 端的 SELECT 查询会在只读 PostgreSQL 事务中以 COPY (SELECT ...) TO STDOUT 的形式运行，并在每个 SELECT 查询后提交。简单的 WHERE 子句 (如 =, !=, >, >=, <, <= 和 IN) 会在 PostgreSQL 服务器上执行。所有 join、聚合、排序、IN [ array ] 条件以及 LIMIT 采样约束，都只会在对 PostgreSQL 的查询完成后由 ClickHouse 执行。

传递查询而非表名

第三个参数可以不是表名，而是一个原样传递给 PostgreSQL 的 SELECT 查询。结果表的结构会从查询结果中推断出来。该查询既可以写成子查询，也可以包装在 query 函数中：

SELECT * FROM postgresql('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
SELECT * FROM postgresql('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

这对于将 join、聚合或任何其他处理下推到 PostgreSQL 很有用。这样的表是只读的：不允许向其中执行 INSERT。PostgreSQL 表引擎也支持相同的语法。

子查询形式 (SELECT ...) 会先由 ClickHouse 解析，并在发送到服务器之前按 PostgreSQL 方言 (PostgreSQL 标识符引用和字符串字面量转义) 重新序列化。因此，它必须是有效的 ClickHouse SQL。要传递 ClickHouse 无法解析的 PostgreSQL 特有语法，请使用 query('...') 形式，其文本会被原样发送到 PostgreSQL。外层 ClickHouse 查询中的任何 WHERE、LIMIT、聚合等都不会下推到传入的查询中——而是在完整查询结果被拉取后由 ClickHouse 执行。要限制从 PostgreSQL 读取的数据，请将过滤条件放在传入的查询内部。使用 external_table_strict_query = 1 时，无法下推的外层过滤条件会抛出异常，而不是在本地执行。

PostgreSQL 端的 INSERT 查询会在 PostgreSQL 事务中以 COPY "table_name" (field1, field2, ... fieldN) FROM STDIN 的形式运行，并在每个 INSERT 语句后自动提交。 PostgreSQL 的 Array 类型会转换为 ClickHouse 数组。

请注意，在 PostgreSQL 中，像 Integer[] 这样的数组数据类型列在不同的行中可能包含维度不同的数组，但在 ClickHouse 中，只允许所有行中的多维数组具有相同的维度。

支持多个副本，必须使用 | 分隔列出。例如：

SELECT name FROM postgresql(`postgres{1|2|3}:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

或

SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

支持为 PostgreSQL 字典源设置副本优先级。map 中的数值越大，优先级越低。最高优先级为 0。

示例

PostgreSQL 中的表：

postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));

CREATE TABLE

postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1

postgresql> SELECT * FROM test;
  int_id | int_nullable | float | str  | float_nullable
 --------+--------------+-------+------+----------------
       1 |              |     2 | test |
(1 row)

使用普通参数从 ClickHouse 中查询数据：

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

或者使用命名集合：

CREATE NAMED COLLECTION mypg AS
        host = 'localhost',
        port = 5432,
        database = 'test',
        user = 'postgresql_user',
        password = 'password';
SELECT * FROM postgresql(mypg, table='test') WHERE str IN ('test');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

插入：

INSERT INTO TABLE FUNCTION postgresql('localhost:5432', 'test', 'test', 'postgrsql_user', 'password') (int_id, float) VALUES (2, 3);
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
│      2 │         ᴺᵁᴸᴸ │     3 │      │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

使用非默认 schema：

postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');

使用 PeerDB 复制或迁移 Postgres 数据

除了表函数之外，您也始终可以使用 ClickHouse 的 PeerDB，建立从 Postgres 到 ClickHouse 的持续数据管道。PeerDB 是一款专为通过 CDC (变更数据捕获) 将数据从 Postgres 复制到 ClickHouse 而设计的工具。

SQL 参考

数据类型

引擎

函数

格式

接口

设置

系统表

数据湖

语法

参数

返回值

设置

Implementation Details

传递查询而非表名

示例

使用 PeerDB 复制或迁移 Postgres 数据

​语法

​参数

​返回值

​设置

​Implementation Details

​传递查询而非表名

​示例

​相关内容

​使用 PeerDB 复制或迁移 Postgres 数据

语法

参数

返回值

设置

Implementation Details

传递查询而非表名

示例

相关内容

使用 PeerDB 复制或迁移 Postgres 数据