Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs

PostgreSQL dictionary source

Example of settings:

SOURCE(POSTGRESQL(
    port 5432
    host 'postgresql-hostname'
    user 'postgres_user'
    password 'postgres_password'
    db 'db_name'
    table 'table_name'
    replica(host 'example01-1' port 5432 priority 1)
    replica(host 'example01-2' port 5432 priority 2)
    where 'id=10'
    invalidate_query 'SQL_QUERY'
    query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))

Setting fields:

Setting Description
host The host on the PostgreSQL server. You can specify it for all replicas, or for each one individually (inside <replica>).
port The port on the PostgreSQL server. You can specify it for all replicas, or for each one individually (inside <replica>).
user Name of the PostgreSQL user. You can specify it for all replicas, or for each one individually (inside <replica>).
password Password of the PostgreSQL user. You can specify it for all replicas, or for each one individually (inside <replica>).
replica Section of replica configurations. There can be multiple sections.
replica/host The PostgreSQL host.
replica/port The PostgreSQL port.
replica/priority The replica priority. When attempting to connect, ClickHouse traverses the replicas in order of priority. The lower the number, the higher the priority.
db Name of the database.
table Name of the table.
where The selection criteria. The syntax for conditions is the same as for WHERE clause in PostgreSQL. For example, id > 10 AND id < 20. Optional.
invalidate_query Query for checking the dictionary status. Optional. Read more in the section Refreshing dictionary data using LIFETIME.
background_reconnect Reconnect to replica in background if connection fails. Optional.
query The custom query. Optional.
sslmode TLS/SSL mode passed to libpq: disable, allow, prefer, require, verify-ca or verify-full. When unset, the libpq default of prefer applies. Optional.
sslrootcert_pem Contents of the CA certificate that the PostgreSQL server certificate is verified against. Optional.
sslcert_pem Contents of the client certificate, for certificate-based authentication. Optional.
sslkey_pem Contents of the private key belonging to sslcert_pem. Optional.
sslrootcert, sslcert, sslkey The same credentials as paths to files on the server. Only allowed for a dictionary defined in a server configuration file, or through a named collection defined there, see below. Optional.
Navigation