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:

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 `bytelength`is 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.

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:

tableoid
xmin
cmin
xmax
cmax
ctid

To avoid naming conflicts with the PostgreSQL system rules, we prefix these column names with # before writing them to the destination.