PostgreSQL
Fivetran supports PostgreSQL database as a destination.
NOTE: Fivetran supports PostgreSQL as both a database connector and a destination.
PostgreSQL is not typically used as a destination because it is a row-based database more suited to transactional use-cases. Large analytical querying is much faster on columnar warehouses. PostgreSQL, however, can be effective as a destination for smaller data volumes.
Supported implementations
Fivetran supports connecting with six different PostgreSQL implementations:
- Generic PostgreSQL
- Amazon Aurora PostgreSQL
- Amazon RDS for PostgreSQL
- Azure PostgreSQL
- Google Cloud PostgreSQL
Type transformation mapping
The data types in your PostgreSQL database follow Fivetran's standard data type storage.
We use the following data type conversions:
| Fivetran Data Type | Destination Data Type | Notes |
|---|---|---|
| BOOLEAN | BOOLEAN | |
| SHORT | SMALLINT | |
| INT | INTEGER | |
| LONG | BIGINT | |
| BIGDECIMAL | DECIMAL | |
| FLOAT | REAL | |
| DOUBLE | DOUBLEPRECISION | |
| LOCALDATE | DATE | |
| LOCALDATETIME | TIMESTAMP WITHOUT TIME ZONE | |
| INSTANT | TIMESTAMP WITH TIME ZONE | |
| STRING | VARCHAR or TEXT | VARCHAR if bytelengthis present, else TEXT |
| XML | XML | For columns created before August 15, 2023, we convert XML to TEXT. |
| JSON | JSON | |
| BINARY | BYTEA |
NOTE: If both your data source and destination are based on PostgreSQL, you may observe unexpected replication results because the standard destination types do not cover all available types in PostgreSQL. For example, GEOMETRY and JSONB types are not going to be replicated as the same types, because the type transformations that we perform are intended to work for all destinations. Therefore, we convert JSONB into JSON and GEOMETRY into separate x and y columns of DOUBLE.
Setup guide
Follow our step-by-step setup guides for specific instructions on how to set up your PostgreSQL database as a destination:
Data load costs
Whether you are self-hosting PostgreSQL or using a managed service, you will not be charged extra when we load data into your database.
Column data type changes
To change the column's data type, Fivetran renames the existing column, creates a new column with the new data type, and then drops the previous version of the column.
Suppose you have set up a view on the table referencing the previous version of the column, then the DROP COLUMN operation will fail, and your destination table will have a deprecated column that won't be updated.
We recommend that you drop the existing views and recreate the views using the updated schema.
default_transaction_read_only parameter
Fivetran needs the default_transaction_read_only paramater value to be set at 0 (off) to be able to create schema and tables in your database. See PostgreSQL's documentation for more information.
pgbouncer pooling mode
If your PostgreSQL database uses pgbouncer as the connection pooler, make sure that it pools the connections in session mode.
System column name management
PostgreSQL does not allow to create user-defined columns with the following names:
tableoidxmincminxmaxcmaxctid
To avoid naming conflicts with the PostgreSQL system rules, we prefix these column names with # before writing them to the destination.