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 时，表函数列上的外层过滤条件会抛出异常，而不是在本地执行，因为它无法被推入传入的查询。该检查涵盖顶层 WHERE 谓词以及顶层 AND 中的每个合取项。此表列上的 PREWHERE 不属于该设置的处理范围：此表引擎不支持 PREWHERE，无论该设置如何，此类查询都会因 ILLEGAL_PREWHERE 而被拒绝。只有在过滤条件本可被下推的情况下才会执行检查：当此表是查询中唯一的表、位于 INNER JOIN 的任一侧，或位于外连接的保留侧 (LEFT JOIN 的左侧、RIGHT JOIN 的右侧) 时。在 LEFT/RIGHT JOIN 的非保留侧以及 FULL JOIN 的任一侧，都不会下推任何内容，也不会执行任何检查，因此即使在严格模式下，对此表列的过滤条件也会在 join 后于本地执行。在执行检查的位置，引用周围查询中其他 join 表的谓词不会被下推，也会被排除在检查之外，无论它仅引用 join 侧，还是在一个非 AND 表达式中与此表混合引用 (例如 OR) ；此类谓词保留其通常的 ClickHouse 执行位置 (join 后的 WHERE、join 前的 PREWHERE) ，且不会被拒绝。

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');

SQL 参考文档

数据类型

引擎

函数

格式

设置

系统表

数据湖

语法

参数

返回值

设置

Implementation Details

传递查询而非表名

示例

相关内容

使用 PeerDB 复制或迁移 Postgres 数据

​语法

​参数

​返回值

​设置

​Implementation Details

​传递查询而非表名

​示例

​相关内容

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

语法

参数

返回值

设置

Implementation Details

传递查询而非表名

示例

相关内容

使用 PeerDB 复制或迁移 Postgres 数据