title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/database-engines/postgresql.md)

Allows to connect to databases on a remote [PostgreSQL](https://www.postgresql.org) 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[​](#creating-a-database "Direct link to Creating a database")

```
CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `schema`, `use_table_cache`]);
```

**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[​](#data_types-support "Direct link to Data types support")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| PostgreSQL ClickHouse|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | DATE [Date](/docs/sql-reference/data-types/date)| TIMESTAMP [DateTime](/docs/sql-reference/data-types/datetime)| REAL [Float32](/docs/sql-reference/data-types/float)| DOUBLE [Float64](/docs/sql-reference/data-types/float)| DECIMAL, NUMERIC [Decimal](/docs/sql-reference/data-types/decimal)| SMALLINT [Int16](/docs/sql-reference/data-types/int-uint)| INTEGER [Int32](/docs/sql-reference/data-types/int-uint)| BIGINT [Int64](/docs/sql-reference/data-types/int-uint)| SERIAL [UInt32](/docs/sql-reference/data-types/int-uint)| BIGSERIAL [UInt64](/docs/sql-reference/data-types/int-uint)| TEXT, CHAR [String](/docs/sql-reference/data-types/string)| INTEGER Nullable([Int32](/docs/sql-reference/data-types/int-uint))| ARRAY [Array](/docs/sql-reference/data-types/array) | | | | | | | | | | | | | | | | | | | | | | | | | | | |

## Examples of use[​](#examples-of-use "Direct link to Examples of use")

Database in ClickHouse, exchanging data with the PostgreSQL server:

```
CREATE DATABASE test_database
ENGINE = PostgreSQL('postgres1:5432', 'test_database', 'postgres', 'mysecretpassword', 'schema_name',1);
```

```
SHOW DATABASES;
```

```
┌─name──────────┐
│ default       │
│ test_database │
│ system        │
└───────────────┘
```

```
SHOW TABLES FROM test_database;
```

```
┌─name───────┐
│ test_table │
└────────────┘
```

Reading data from the PostgreSQL table:

```
SELECT * FROM test_database.test_table;
```

```
┌─id─┬─value─┐
│  1 │     2 │
└────┴───────┘
```

Writing data to the PostgreSQL table:

```
INSERT INTO test_database.test_table VALUES (3,4);
SELECT * FROM test_database.test_table;
```

```
┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘
```

Consider the table structure was modified in PostgreSQL:

```
postgre> ALTER TABLE test_table ADD COLUMN data Text
```

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:

```
DESCRIBE TABLE test_database.test_table;
```

```
┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
└────────┴───────────────────┘
```

After detaching the table and attaching it again, the structure was updated:

```
DETACH TABLE test_database.test_table;
ATTACH TABLE test_database.test_table;
DESCRIBE TABLE test_database.test_table;
```

```
┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
│ data   │ Nullable(String)  │
└────────┴───────────────────┘
```

## 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 database](#creating-a-database)- [Data types support](#data_types-support)- [Examples of use](#examples-of-use)- [Related content](#related-content)

Was this page helpful?