Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

postgresql

View as MarkdownEdit this pageReport an issueSet up docs MCP

允许对存储在远程 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 表相同的列。

设置

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 表引擎也支持相同的语法。

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

PostgreSQL 的 Array 类型会转换为 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 而设计的工具。

Navigation