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
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.
Data Types Support
| PostgreSQL | ClickHouse |
|---|---|
| DATE | Date |
| TIMESTAMP | DateTime |
| REAL | Float32 |
| DOUBLE | Float64 |
| DECIMAL, NUMERIC | Decimal |
| SMALLINT | Int16 |
| INTEGER | Int32 |
| BIGINT | Int64 |
| SERIAL | UInt32 |
| BIGSERIAL | UInt64 |
| TEXT, CHAR | String |
| INTEGER | Nullable(Int32) |
| ARRAY | Array |
Examples of Use
Database in ClickHouse, exchanging data with the PostgreSQL server:
Reading data from the PostgreSQL table:
Writing data to the PostgreSQL table:
Consider the table structure was modified in PostgreSQL:
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:
After detaching the table and attaching it again, the structure was updated: