PostgreSQL

Allows to connect to databases on a remote PostgreSQL server. Supports read and write operations (SELECT and INSERT queries) to exchange data between ClickHouse and PostgreSQL. Gives the real-time access to table list and table structure from remote PostgreSQL with the help of SHOW TABLES and DESCRIBE TABLE queries. Supports table structure modifications (ALTER TABLE ... ADD|DROP COLUMN). If use_table_cache parameter (see the Engine Parameters below) is set to 1, the table structure is cached and not checked for being modified, but can be updated with DETACH and ATTACH queries.

Creating a database

CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `schema`, `use_table_cache`]);

Engine Parameters

host:port — PostgreSQL server address.
database — Remote database name.
user — PostgreSQL user.
password — User password.
schema — PostgreSQL schema.
use_table_cache — Defines if the database table structure is cached or not. Optional. Default value: 0.

TLS/SSL parameters are forwarded to libpq and can be supplied through a named collection or as 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. sslrootcert (CA certificate), sslcert (client certificate) and sslkey (client private key) are paths to server-local files, accepted only from a named collection defined in the server configuration file. sslrootcert_pem, sslcert_pem and sslkey_pem accept the literal contents of the corresponding file instead, can be specified from SQL (for example, ENGINE = PostgreSQL('host:port', 'database', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...')), and are masked in logs and SHOW queries like a password. When unset, libpq defaults apply (sslmode=prefer).

Data types support

PostgreSQL	ClickHouse
DATE	Date
TIMESTAMP	DateTime
REAL	Float32
DOUBLE	Float64
DECIMAL, NUMERIC	Decimal (see note below)
SMALLINT	Int16
INTEGER	Int32
BIGINT	Int64
SERIAL	UInt32
BIGSERIAL	UInt64
TEXT, CHAR	String
INTEGER	Nullable(Int32)
ARRAY	Array

PostgreSQL numeric(p, 0) with a precision p greater than 76 (the maximum supported by Decimal256) — for example numeric(78, 0), commonly used to store 256-bit integers — is mapped to Int256 instead of Decimal. Values that do not fit into the Int256 range are rejected with an error.

Examples of use

Database in ClickHouse, exchanging data with the PostgreSQL server:

CREATE DATABASE test_database
ENGINE = PostgreSQL('postgres1:5432', 'test_database', 'postgres', 'mysecretpassword', 'schema_name',1);

SHOW DATABASES;

┌─name──────────┐
│ default       │
│ test_database │
│ system        │
└───────────────┘

SHOW TABLES FROM test_database;

┌─name───────┐
│ test_table │
└────────────┘

Reading data from the PostgreSQL table:

SELECT * FROM test_database.test_table;

┌─id─┬─value─┐
│  1 │     2 │
└────┴───────┘

Writing data to the PostgreSQL table:

INSERT INTO test_database.test_table VALUES (3,4);
SELECT * FROM test_database.test_table;

┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘

Consider the table structure was modified in PostgreSQL:

postgre> ALTER TABLE test_table ADD COLUMN data Text

As the use_table_cache parameter was set to 1 when the database was created, the table structure in ClickHouse was cached and therefore not modified:

DESCRIBE TABLE test_database.test_table;

┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
└────────┴───────────────────┘

After detaching the table and attaching it again, the structure was updated:

DETACH TABLE test_database.test_table;
ATTACH TABLE test_database.test_table;
DESCRIBE TABLE test_database.test_table;

┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
│ data   │ Nullable(String)  │
└────────┴───────────────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Creating a database

Data types support

Examples of use

​Creating a database

​Data types support

​Examples of use

​Related content

Creating a database

Data types support

Examples of use

Related content