Fivetran supports PostgreSQL database as a destination. PostgreSQL is not typically used as a data warehouse 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 warehouse for smaller data volumes.
Supported implementationslink
Fivetran supports connecting with four different PostgreSQL implementations:
NOTE: We do not support Amazon Aurora Serverless PostgreSQL.
Type transformation mappinglink
The data types in your PostgreSQL destination 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 | |
| FLOAT | REAL | |
| DOUBLE | DOUBLEPRECISION | |
| BIGDECIMAL | DECIMAL | |
| LOCALDATE | DATE | |
| INSTANT | TIMESTAMP WITH TIME ZONE | |
| LOCALDATETIME | TIMESTAMP WITHOUT TIME ZONE | |
| STRING | VARCHAR or TEXT | VARCHAR if bytelengthis present, else 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 guidelink
Follow our step-by-step setup guides for specific instructions on how to set up your PostgreSQL database as a destination:
Data load costslink
Whether you are self-hosting PostgreSQL or using a managed service, you will not be charged extra when we load data into your warehouse.
Column data type changeslink
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 parameterlink
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 destination. See PostgreSQL’s documentation for more information.