PostgreSQL table engine

### Inserting incremental data from PostgreSQL table into ClickHouse table[​](#inserting-incremental-data-from-postgresql-table-into-clickhouse-table "Direct link to Inserting incremental data from PostgreSQL table into ClickHouse table")

If then performing ongoing synchronization between the PostgreSQL table and ClickHouse table after the initial insert, you can use a WHERE clause in ClickHouse to insert only data added to PostgreSQL based on a timestamp or unique sequence ID.

This would require keeping track of the max ID or timestamp previously added, such as the following:

```
SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;
```

Then inserting values from PostgreSQL table greater than the max

```
INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postges_user', 'postgres_password');
WHERE int_id > maxIntID;
```

### Selecting data from the resulting ClickHouse table[​](#selecting-data-from-the-resulting-clickhouse-table "Direct link to Selecting data from the resulting ClickHouse table")

```
SELECT * FROM postgresql_copy WHERE str IN ('test');
```

```
┌─float_nullable─┬─str──┬─int_id─┐
│           ᴺᵁᴸᴸ │ test │      1 │
└────────────────┴──────┴────────┘
```

### Using non-default schema[​](#using-non-default-schema "Direct link to Using non-default schema")

```
postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)
```

```
CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');
```

**See Also**

- [The `postgresql` table function](/docs/sql-reference/table-functions/postgresql)
- [Using PostgreSQL as a dictionary source](/docs/sql-reference/statements/create/dictionary/sources/postgresql)

## Related content[​](#related-content "Direct link to Related content")

- Blog: [ClickHouse and PostgreSQL - a match made in data heaven - part 1](https://clickhouse.com/blog/migrating-data-between-clickhouse-postgres)
- Blog: [ClickHouse and PostgreSQL - a Match Made in Data Heaven - part 2](https://clickhouse.com/blog/migrating-data-between-clickhouse-postgres-part-2)

- [Creating a table](#creating-a-table)- [Implementation details](#implementation-details)- [Usage example](#usage-example)
      - [Table in PostgreSQL](#table-in-postgresql)- [Creating Table in ClickHouse, and connecting to PostgreSQL table created above](#creating-table-in-clickhouse-and-connecting-to--postgresql-table-created-above)- [Inserting initial data from PostgreSQL table into ClickHouse table, using a SELECT query](#inserting-initial-data-from-postgresql-table-into-clickhouse-table-using-a-select-query)- [Inserting incremental data from PostgreSQL table into ClickHouse table](#inserting-incremental-data-from-postgresql-table-into-clickhouse-table)- [Selecting data from the resulting ClickHouse table](#selecting-data-from-the-resulting-clickhouse-table)- [Using non-default schema](#using-non-default-schema)- [Related content](#related-content)

Was this page helpful?