PostgreSQL

Note: It's possible that you need to authorize ClickHouse Cloud to access to your PostgreSQL instance. The full list of ClickHouse Cloud egress IP addresses is available here.

You can use the PostgreSQL table function to query remote PostgreSQL tables.

SELECT * FROM PostgreSQL('<postgres_host>:<port>', '<schema>', '<database>', '<user>', '<password>');

You can also create a table using PostgreSQL table engine in your ClickHouse Cloud service without specifying a schema using the CREATE TABLE AS syntax. When ClickHouse creates the table locally, types in PostgreSQL will be mapped to equivalent ClickHouse types.

CREATE TABLE mycountries AS PostgreSQL('<postgres_host>:5432', '', 'countries', 'postgres', '<password>');

Note: You can use the setting external_table_functions_use_nulls = 0 to ensure Null values are represented as their default values (instead of Null). If set to 1 (the default), ClickHouse will create Nullable variants of each column.

You can also decide to create a table with the PostgreSQL table engine in your ClickHouse service by specifying a custom schema.

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('<postgres_host>:<port>', '<schema>', '<database>', '<user>', '<password>');