Going to re:Invent this December? Come party with us and The Chainsmokers ->->
Back to integrations

PostgreSQL

core

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.

Use the the PostgreSQL table function

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

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

Create a PostgreSQL table without providing a schema

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.

Create a PostgreSQL table with a custom schema

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

Other integrations

Get started with ClickHouse Cloud for free

We'll get you started on a 30 day trial and $300 credits to spend at your own pace.