> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Allows to connect to databases on a remote PostgreSQL server.

# PostgreSQL

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.

<h2 id="creating-a-database">
  Creating a database
</h2>

```sql theme={null}
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`.

<h2 id="data_types-support">
  Data types support
</h2>

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

<h2 id="examples-of-use">
  Examples of use
</h2>

Database in ClickHouse, exchanging data with the PostgreSQL server:

```sql theme={null}
CREATE DATABASE test_database
ENGINE = PostgreSQL('postgres1:5432', 'test_database', 'postgres', 'mysecretpassword', 'schema_name',1);
```

```sql theme={null}
SHOW DATABASES;
```

```text theme={null}
┌─name──────────┐
│ default       │
│ test_database │
│ system        │
└───────────────┘
```

```sql theme={null}
SHOW TABLES FROM test_database;
```

```text theme={null}
┌─name───────┐
│ test_table │
└────────────┘
```

Reading data from the PostgreSQL table:

```sql theme={null}
SELECT * FROM test_database.test_table;
```

```text theme={null}
┌─id─┬─value─┐
│  1 │     2 │
└────┴───────┘
```

Writing data to the PostgreSQL table:

```sql theme={null}
INSERT INTO test_database.test_table VALUES (3,4);
SELECT * FROM test_database.test_table;
```

```text theme={null}
┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘
```

Consider the table structure was modified in PostgreSQL:

```sql theme={null}
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:

```sql theme={null}
DESCRIBE TABLE test_database.test_table;
```

```text theme={null}
┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
└────────┴───────────────────┘
```

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

```sql theme={null}
DETACH TABLE test_database.test_table;
ATTACH TABLE test_database.test_table;
DESCRIBE TABLE test_database.test_table;
```

```text theme={null}
┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
│ data   │ Nullable(String)  │
└────────┴───────────────────┘
```

<h2 id="related-content">
  Related content
</h2>

* 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)