> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://apidocs.polytomic.com/2024-02-08/guides/configuring-your-connections/connections/postgresql/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://apidocs.polytomic.com/_mcp/server.

# PostgreSQL

Supports:
* ✅ Models
* ✅ Model sync destination
* ✅ Bulk sync source
* ✅ Bulk sync destination




## Connection

### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>change_detection</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Use logical replication for bulk syncs <a href="#change-detection">↓</a></td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>client_certs</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Use client certificates <a href="#client-certs">↓</a></td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>database</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Database</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>hostname</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Hostname</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>password</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Password</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>port</code></td>
<td><code>integer</code></td>
<td style="vertical-align: top;">Port</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>ssh</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Connect over SSH tunnel <a href="#ssh">↓</a></td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>ssl</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Use SSL</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>username</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Username</td>
<td><code>true</code></td>
</tr>
</tbody>
</table>

<h4 id="client-certs">`client_certs`</h4>

<h5 id="client-certs-true">When `client_certs` is `true`</h5>

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>ca_cert</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">CA certificate</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>client_certificate</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Client certificate</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>client_key</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Client key</td>
<td><code>true</code></td>
</tr>
</tbody>
</table>


<h4 id="change-detection">`change_detection`</h4>

<h5 id="change-detection-true">When `change_detection` is `true`</h5>

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>publication</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Publication</td>
<td><code>true</code></td>
</tr>
</tbody>
</table>


<h4 id="ssh">`ssh`</h4>

<h5 id="ssh-true">When `ssh` is `true`</h5>

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>ssh_host</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">SSH host</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>ssh_port</code></td>
<td><code>integer</code></td>
<td style="vertical-align: top;">SSH port</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>ssh_private_key</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Private key</td>
<td><code>true</code></td>
</tr>
<tr>
<td><code>ssh_user</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">SSH user</td>
<td><code>true</code></td>
</tr>
</tbody>
</table>



#### Example

```json
{
  "name": "PostgreSQL connection",
  "type": "postgresql",
  "configuration": {
    "change_detection": false,
    "client_certs": false,
    "database": "sampledb",
    "hostname": "database.example.com",
    "password": "password",
    "port": 5432,
    "ssh": false,
    "ssl": false,
    "username": "postgres"
  }
}
```

## Model Sync
### Source
#### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>query</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;"></td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>table</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Table</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>view</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">View</td>
<td><code>false</code></td>
</tr>
</tbody>
</table>


#### Example
```json
{
  ...
  "configuration": {
    "query": "SELECT * from users",
    "table": "users",
    "view": "active_users"
  }
}
```

### Target
PostgreSQL connections may be used as the destination in a model sync.
#### All targets

##### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>created_column</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">'Created at' timestamp column</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>preserve_table_on_resync</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Preserve destination table when resyncing</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>updated_column</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">'Updated at' timestamp column</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>write_null_values</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Copy null values<br/><br/>When enabled updates will set fields to NULL when the source value is null</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>write_record_timestamps</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Write row timestamp metadata</td>
<td><code>false</code></td>
</tr>
</tbody>
</table>


##### Example
```json
{
  ...
  "target": {
      "configuration": {
        "created_column": "",
        "preserve_table_on_resync": false,
        "updated_column": "",
        "write_null_values": false,
        "write_record_timestamps": false
    }
  }
}
```





### Target creation

PostgreSQL connections may be used to create a new target for a model sync. The
following parameters are required to create a new target:

<table>
<thead>
<tr>
  <th>NAME</th>
  <th>DESCRIPTION</th>
  <th>ENUM</th>
</tr></thead>
<tbody>
<tr>
  <td>name</td>
  <td>Table name</td>
  <td>false</td>
</tr>

</tbody>
</table>




## Bulk Sync
### Source
#### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>automatically_add_new_fields</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Automatically add new fields on selected tables</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>automatically_add_new_objects</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Automatically add new tables</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>mirror_publication_selections</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Mirror table and column selection with database publication</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>mirror_tables_without_unique_id</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Mirror table without a unique identifier<br/><br/>If set to true</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>replication_slot</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Replication slot<br/><br/>Leave blank to allow Polytomic to manage a replication slot for this sync.</td>
<td><code>false</code></td>
</tr>
</tbody>
</table>


#### Example
```json
{
  ...
  "source_configuration": {
    "automatically_add_new_fields": false,
    "automatically_add_new_objects": false,
    "mirror_publication_selections": false,
    "mirror_tables_without_unique_id": false,
    "replication_slot": "polytomic"
  }
}
```

### Destination
#### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>advanced</code></td>
<td><code>object</code></td>
<td style="vertical-align: top;"></td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>mirror_schemas</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Mirror schemas</td>
<td><code>false</code></td>
</tr>
<tr>
<td><code>schema</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Output schema</td>
<td><code>false</code></td>
</tr>
</tbody>
</table>


#### Example
```json
{
  ...
  "destination_configuration": {
    "advanced": {
        "empty_strings_null": false,
        "hard_deletes": false,
        "initial_execution": "",
        "table_prefix": "",
        "truncate_existing": false
    },
    "mirror_schemas": false,
    "schema": "schema"
  }
}
```



## Type handling
### Destination types
|       POLYTOMIC TYPE        |      POSTGRESQL TYPE       |
|-----------------------------|----------------------------|
| `array<>`                   | `JSON`                     |
| `bigint`                    | `INT8`                     |
| `boolean`                   | `BOOL`                     |
| `date`                      | `DATE`                     |
| `datetime`                  | `TIMESTAMP`                |
| `decimal(precision, scale)` | `NUMERIC(precision,scale)` |
| `double`                    | `DOUBLE PRECISION`         |
| `int`                       | `INT4`                     |
| `json`                      | `JSON`                     |
| `jsonarray`                 | `JSON`                     |
| `number`                    | `NUMERIC`                  |
| `object{}`                  | `JSON`                     |
| `single`                    | `REAL`                     |
| `smallint`                  | `INT2`                     |
| `string`                    | `TEXT`                     |
| `time`                      | `TIME`                     |


### Source types
|       POSTGRESQL TYPE       |       POLYTOMIC TYPE        |
|-----------------------------|-----------------------------|
| `ANYARRAY`                  | `jsonarray`                 |
| `CIDR`                      | `string`                    |
| `CSTRING`                   | `string`                    |
| `DATE`                      | `date`                      |
| `DOUBLE PRECISION`          | `double`                    |
| `FLOAT`                     | `single`                    |
| `FLOAT4`                    | `single`                    |
| `FLOAT8`                    | `double`                    |
| `INET`                      | `string`                    |
| `INT`                       | `int`                       |
| `INT2`                      | `smallint`                  |
| `INT4`                      | `int`                       |
| `INT8`                      | `bigint`                    |
| `INTERVAL`                  | `string`                    |
| `JSON`                      | `json`                      |
| `JSONB`                     | `json`                      |
| `MACADDR`                   | `string`                    |
| `MONEY`                     | `number`                    |
| `NAME`                      | `string`                    |
| `NUMERIC(precision, scale)` | `decimal(precision, scale)` |
| `REAL`                      | `single`                    |
| `TEXT`                      | `string`                    |
| `TIME`                      | `time`                      |
| `TIMESTAMP`                 | `datetime`                  |
| `TIMESTAMPTZ`               | `datetime_tz`               |
| `TIMETZ`                    | `time`                      |
| `UUID`                      | `string`                    |
| `_BOOL`                     | `jsonarray`                 |
| `_BPCHAR`                   | `jsonarray`                 |
| `_BYTEA`                    | `jsonarray`                 |
| `_CHAR`                     | `jsonarray`                 |
| `_CSTRING`                  | `jsonarray`                 |
| `_DATE`                     | `jsonarray`                 |
| `_FLOAT4`                   | `jsonarray`                 |
| `_FLOAT8`                   | `jsonarray`                 |
| `_INT2`                     | `jsonarray`                 |
| `_INT2VECTOR`               | `jsonarray`                 |
| `_INT4`                     | `jsonarray`                 |
| `_INT8`                     | `jsonarray`                 |
| `_JSON`                     | `jsonarray`                 |
| `_JSONB`                    | `jsonarray`                 |
| `_LSEG`                     | `jsonarray`                 |
| `_MONEY`                    | `jsonarray`                 |
| `_NAME`                     | `jsonarray`                 |
| `_NUMERIC`                  | `jsonarray`                 |
| `_PATH`                     | `jsonarray`                 |
| `_TEXT`                     | `jsonarray`                 |
| `_TIME`                     | `jsonarray`                 |
| `_TIMESTAMP`                | `jsonarray`                 |
| `_TIMESTAMPTZ`              | `jsonarray`                 |
| `_TIMETZ`                   | `jsonarray`                 |
| `_UUID`                     | `jsonarray`                 |
| `_VARCHAR`                  | `jsonarray`                 |
| `_XML`                      | `jsonarray`                 |