> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

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

# postgresql

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

## 语法

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

## 参数

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

参数也可以通过[命名集合](/zh/concepts/features/configuration/server-config/named-collections)传递。在这种情况下，需要分别指定 `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 表相同的列。

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

## 设置

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

示例：

```sql theme={null}
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` 函数中：

```sql theme={null}
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`](/zh/reference/engines/table-engines/integrations/postgresql) 表引擎也支持相同的语法。

<Note>
  子查询形式 `(SELECT ...)` 会先由 ClickHouse 解析，并在发送到服务器之前按 PostgreSQL 方言 (PostgreSQL 标识符引用和字符串字面量转义) 重新序列化。因此，它必须是有效的 ClickHouse SQL。要传递 ClickHouse 无法解析的 PostgreSQL 特有语法，请使用 `query('...')` 形式，其文本会被原样发送到 PostgreSQL。

  外层 ClickHouse 查询中的任何 `WHERE`、`LIMIT`、聚合等都**不会**下推到传入的查询中——而是在完整查询结果被拉取后由 ClickHouse 执行。要限制从 PostgreSQL 读取的数据，请将过滤条件放在传入的查询内部。使用 [`external_table_strict_query = 1`](/zh/reference/settings/session-settings/external-table#external_table_strict_query) 时，表函数列上的外层过滤条件会抛出异常，而不是在本地执行，因为它无法被推入传入的查询。该检查涵盖顶层 `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`) ，且不会被拒绝。
</Note>

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

PostgreSQL 的 Array 类型会转换为 ClickHouse 数组。

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

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

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

或

```sql theme={null}
SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');
```

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

## 示例

PostgreSQL 中的表：

```text theme={null}
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 中查询数据：

```sql theme={null}
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');
```

或者使用[命名集合](/zh/concepts/features/configuration/server-config/named-collections)：

```sql theme={null}
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');
```

```text theme={null}
┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘
```

插入：

```sql theme={null}
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');
```

```text theme={null}
┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
│      2 │         ᴺᵁᴸᴸ │     3 │      │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘
```

使用非默认 schema：

```text theme={null}
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)
```

```sql theme={null}
CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');
```

## 相关内容

* [PostgreSQL 表引擎](/zh/reference/engines/table-engines/integrations/postgresql)
* [使用 PostgreSQL 作为字典源](/zh/reference/statements/create/dictionary/sources/postgresql)

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

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