PostgreSQL table Engine

The PostgreSQL engine allows SELECT and INSERT queries on data stored on a remote PostgreSQL server.

Creating a table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = PostgreSQL({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})
SETTINGS
    [ postgresql_connection_pool_size=16, ]
    [ postgresql_connection_pool_wait_timeout=5000, ]
    [ postgresql_connection_pool_retries=2, ]
    [ postgresql_connection_pool_auto_close_connection=false, ]
    [ postgresql_connection_attempt_timeout=2 ]
;

See a detailed description of the CREATE TABLE query.

The table structure can differ from the original PostgreSQL table structure:

Column names should be the same as in the original PostgreSQL table, but you can use just some of these columns and in any order.
Column types may differ from those in the original PostgreSQL table. ClickHouse tries to cast values to the ClickHouse data types.
The external_table_functions_use_nulls setting defines how to handle Nullable columns. Default value: 1. If 0, the table function does not make Nullable columns and inserts default values instead of nulls. This is also applicable for NULL values inside arrays.

Engine Parameters

host:port — PostgreSQL server address.
database — Remote database name.
table — Remote table name, or a query passed to PostgreSQL as is (see Passing a query instead of a table name).
user — PostgreSQL user.
password — User password.
schema — Non-default table schema. Optional.
on_conflict — Conflict resolution strategy. Example: ON CONFLICT DO NOTHING. Optional. Note: adding this option will make insertion less efficient.

Named collections (available since version 21.11) are recommended for production environment. Here is an example:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <schema>schema1</schema>
    </postgres_creds>
</named_collections>

Some parameters can be overridden by key value arguments:

SELECT * FROM postgresql(postgres_creds, table='table1');

TLS/SSL

TLS/SSL parameters are forwarded to libpq and can be set as named collection keys or trailing key-value arguments: sslmode (disable, allow, prefer, require, verify-ca or verify-full), and the certificates and the key, in one of two forms. When unset, libpq defaults apply (sslmode=prefer).

sslrootcert (CA certificate, or the special value system), sslcert (client certificate) and sslkey (client private key) are paths to server-local files. They can only be specified in a named collection defined in the server configuration file and cannot be overridden in a query: the server opens the files with its own privileges.
sslrootcert_pem, sslcert_pem and sslkey_pem accept the literal contents of the corresponding file instead of a path. They can be specified anywhere — in a query, in a named collection created with SQL, or as an override of a named collection — and are masked in logs and SHOW queries like a password.

For example, to require an encrypted connection and verify the server certificate:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <sslmode>verify-full</sslmode>
        <sslrootcert>/etc/clickhouse-server/postgresql-ca.crt</sslrootcert>
    </postgres_creds>
</named_collections>

The same without a configuration file, passing the certificate contents in the query:

CREATE TABLE postgres_table (id UInt64, value String)
ENGINE = PostgreSQL('localhost:5432', 'database', 'table', 'user', 'password',
                    sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

Settings

The connection pool used by the PostgreSQL table engine (and the postgresql table function) can be configured per table with a SETTINGS clause. When a setting is not specified, it defaults to the value of the corresponding query-level postgresql_* setting.

`postgresql_connection_pool_size`

Connection pool size (if all connections are in use, the query waits until some connection is freed). Must be non-zero.

Default value: 16.

`postgresql_connection_pool_wait_timeout`

Connection pool push/pop timeout in milliseconds on an empty pool. 0 means it blocks on an empty pool.

Default value: 5000.

`postgresql_connection_pool_retries`

Connection pool push/pop retries number.

Default value: 2.

`postgresql_connection_pool_auto_close_connection`

Close the connection before returning it to the pool.

Default value: false.

`postgresql_connection_attempt_timeout`

Connection timeout in seconds of a single attempt to connect to the PostgreSQL end-point. The value is passed as a connect_timeout parameter of the connection URL.

Default value: 2.

Example:

CREATE TABLE pg_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
SETTINGS postgresql_connection_pool_size = 32, postgresql_connection_pool_auto_close_connection = 1;

Implementation details

SELECT queries on PostgreSQL side run as COPY (SELECT ...) TO STDOUT inside read-only PostgreSQL transaction with commit after each SELECT query.

Simple WHERE clauses such as =, !=, >, >=, <, <=, and IN are executed on the PostgreSQL server.

All joins, aggregations, sorting, IN [ array ] conditions and the LIMIT sampling constraint are executed in ClickHouse only after the query to PostgreSQL finishes.

Passing a query instead of a table name

Instead of a table name, the table argument can be a SELECT query that is passed to PostgreSQL as is. The structure of the table is inferred from the query result. The query can be written either as a subquery, or wrapped into the query function:

CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

This is useful to push down joins, aggregations or any other processing to PostgreSQL. Such a table is read-only: INSERT into it is not allowed. The same syntax is supported by the postgresql table function.

INSERT queries on PostgreSQL side run as COPY "table_name" (field1, field2, ... fieldN) FROM STDIN inside PostgreSQL transaction with auto-commit after each INSERT statement.

PostgreSQL Array types are converted into ClickHouse arrays.

Supports multiple replicas that must be listed by |. For example:

CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');

Replicas priority for PostgreSQL dictionary source is supported. The bigger the number in map, the less the priority. The highest priority is 0.

In the example below replica example01-1 has the highest priority:

<postgresql>
    <port>5432</port>
    <user>clickhouse</user>
    <password>qwerty</password>
    <replica>
        <host>example01-1</host>
        <priority>1</priority>
    </replica>
    <replica>
        <host>example01-2</host>
        <priority>2</priority>
    </replica>
    <db>db_name</db>
    <table>table_name</table>
    <where>id=10</where>
    <invalidate_query>SQL_QUERY</invalidate_query>
</postgresql>
</source>

Usage example

Table in 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)

Creating Table in ClickHouse, and connecting to PostgreSQL table created above

This example uses the PostgreSQL table engine to connect the ClickHouse table to the PostgreSQL table and use both SELECT and INSERT statements to the PostgreSQL database:

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

Inserting initial data from PostgreSQL table into ClickHouse table, using a SELECT query

The postgresql table function copies the data from PostgreSQL to ClickHouse, which is often used for improving the query performance of the data by querying or performing analytics in ClickHouse rather than in PostgreSQL, or can also be used for migrating data from PostgreSQL to ClickHouse. Since we will be copying the data from PostgreSQL to ClickHouse, we will use a MergeTree table engine in ClickHouse and call it postgresql_copy:

CREATE TABLE default.postgresql_copy
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = MergeTree
ORDER BY (int_id);

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

Inserting incremental data from PostgreSQL table into ClickHouse table

If then performing ongoing synchronization between the PostgreSQL table and ClickHouse table after the initial insert, you can use a WHERE clause in ClickHouse to insert only data added to PostgreSQL based on a timestamp or unique sequence ID.

This would require keeping track of the max ID or timestamp previously added, such as the following:

SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;

Then inserting values from PostgreSQL table greater than the max

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
WHERE int_id > (SELECT max(int_id) FROM default.postgresql_copy);

Selecting data from the resulting ClickHouse table

SELECT * FROM postgresql_copy WHERE str IN ('test');

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

Using non-default 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');

See Also