PostgreSQL dictionary source

Example of settings:

DDL
Configuration file

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'
))

<source>
  <postgresql>
      <host>postgresql-hostname</hoat>
      <port>5432</port>
      <user>clickhouse</user>
      <password>qwerty</password>
      <db>db_name</db>
      <table>table_name</table>
      <where>id=10</where>
      <invalidate_query>SQL_QUERY</invalidate_query>
      <query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
  </postgresql>
</source>

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.

The table or where fields cannot be used together with the query field. And either one of the table or query fields must be declared.

sslrootcert, sslcert and sslkey name files that the server opens with its own privileges, so they are only accepted for a dictionary defined in a server configuration file, or through a named collection defined there. A dictionary created with a CREATE DICTIONARY query must pass the contents instead, in sslrootcert_pem, sslcert_pem and sslkey_pem. Those values are masked in logs and in SHOW queries, the same way passwords are.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes