> ## 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` | デフォルト以外のテーブルスキーマ。省略可能です。 | | `on_conflict` | 競合解決の戦略。例: `ON CONFLICT DO NOTHING`。省略可能です。 | 引数は [named collections](/ja/concepts/features/configuration/server-config/named-collections) を使用して渡すこともできます。この場合、`host` と `port` は別々に指定する必要があります。この方法は本番環境に推奨されます。 TLS/SSL パラメータは `libpq` に転送され、named collection のキーまたは末尾のキー・バリュー引数として指定できます。指定できるのは `sslmode` (`disable`、`allow`、`prefer`、`require`、`verify-ca`、`verify-full`。未設定の場合は `libpq` のデフォルトである `prefer` が適用されます) と、2 つの形式のいずれかで指定する証明書および秘密鍵です。`sslrootcert` (CA 証明書、または特別な値 `system`) 、`sslcert` (クライアント証明書) 、`sslkey` (クライアント秘密鍵) はサーバーローカルファイルへのパスであり、サーバー設定ファイルで定義された named collection でのみ指定できます。代わりに、`sslrootcert_pem`、`sslcert_pem`、`sslkey_pem` には対応するファイルの内容を直接指定できます。たとえば、`postgresql('host:port', 'database', 'table', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...')` のように指定します。これらはパスワードと同様に、ログおよび `SHOW` クエリではマスクされます。 ## 戻り値 元の PostgreSQL テーブルと同じカラムを持つテーブルオブジェクト。 テーブル関数 `postgresql(...)` を、カラム名リスト付きのテーブル名と区別するには、`INSERT` クエリでキーワード `FUNCTION` または `TABLE FUNCTION` を使用する必要があります。以下の例を参照してください。 ## 設定 `postgresql` テーブル関数 (および[`PostgreSQL`](/ja/reference/engines/table-engines/integrations/postgresql)テーブルエンジン) で使用される接続プールは、末尾に `SETTINGS` 句を付けて設定できます。設定が指定されていない場合は、対応するクエリレベルの `postgresql_*` 設定の値がデフォルトで使用されます。`postgresql_connection_pool_*` および `postgresql_connection_attempt_timeout` の各設定とそのデフォルト値の一覧については、テーブルエンジンの[設定](/ja/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); ``` ## 実装の詳細 PostgreSQL 側の `SELECT` クエリは、読み取り専用の PostgreSQL トランザクション内で `COPY (SELECT ...) TO STDOUT` として実行され、各 `SELECT` クエリの後にコミットされます。 `=`, `!=`, `>`, `>=`, `<`, `<=`, `IN` などの単純な `WHERE` 句は、PostgreSQL サーバー上で実行されます。 すべての JOIN、集計、ソート、`IN [ array ]` 条件、および `LIMIT` サンプリング制約は、PostgreSQL へのクエリが完了した後にのみ ClickHouse で実行されます。 ## テーブル名の代わりにクエリを渡す テーブル名の代わりに、第 3 引数には、そのまま 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`](/ja/reference/engines/table-engines/integrations/postgresql) テーブルエンジンでもサポートされています。 サブクエリ形式 `(SELECT ...)` は ClickHouse によって解析され、サーバーに送信される前に PostgreSQL 方言 (PostgreSQL の識別子のクォートと文字列リテラルのエスケープ) で再シリアライズされます。したがって、有効な ClickHouse SQL でなければなりません。ClickHouse が解析しない PostgreSQL 固有の構文を渡すには、`query('...')` 形式を使用してください。この形式のテキストはそのまま PostgreSQL に送信されます。 渡されたクエリには、外側の ClickHouse クエリの `WHERE`、`LIMIT`、集計などは **プッシュダウンされません**。これらは完全なクエリ結果が取得された後に ClickHouse で適用されます。PostgreSQL から読み取るデータを制限するには、渡すクエリ内にフィルターを含めてください。[`external_table_strict_query = 1`](/ja/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` のいずれの側でも、何もプッシュダウンされず、何もチェックされないため、このテーブルのカラムに対するフィルターは、strict モードであっても JOIN の後にローカルで適用されます。チェックが実行される箇所では、外側のクエリで結合された他のテーブルを参照する述語はプッシュダウンされず、チェックの対象から除外されます。これは、結合された側のみを参照する場合でも、`AND` 以外の 1 つの式 (たとえば `OR`) の中でこのテーブルと混在している場合でも同様です。そのような述語は通常の ClickHouse の評価タイミング (JOIN の後の `WHERE`、その前の `PREWHERE`) を維持し、拒否されません。 PostgreSQL 側の `INSERT` クエリは、各 `INSERT` ステートメントの後に自動コミットされる PostgreSQL トランザクション内で、`COPY "table_name" (field1, field2, ... fieldN) FROM STDIN` として実行されます。 PostgreSQL の Array 型は ClickHouse の配列に変換されます。 PostgreSQL では、Integer\[] のような配列データ型のカラムに、行ごとに次元数の異なる配列を含めることができますが、ClickHouse ではすべての行で同じ次元数の多次元配列しか許可されないため、注意してください。 複数のレプリカをサポートしており、`|` で列挙する必要があります。例: ```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 の Dictionary ソースで、レプリカの優先度をサポートしています。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'); ``` または、[named collections](/ja/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 │ │ ᴺᵁᴸᴸ │ └────────┴──────────────┴───────┴──────┴────────────────┘ ``` 非デフォルトのスキーマを使用する: ```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 テーブルエンジン](/ja/reference/engines/table-engines/integrations/postgresql) * [PostgreSQL を Dictionary ソースとして使用する](/ja/reference/statements/create/dictionary/sources/postgresql) ### PeerDB を使用した Postgres データのレプリケーションまたは移行 > テーブル関数に加えて、ClickHouse の [PeerDB](https://docs.peerdb.io/introduction) を使って、Postgres から ClickHouse への継続的なデータパイプラインを構築することもできます。PeerDB は、CDC (変更データキャプチャ) を使用して Postgres から ClickHouse にデータをレプリケートするために専用設計されたツールです。