Del via

PostgreSQL connector reference

Important

The PostgreSQL connector for Lakeflow Connect is in Public Preview. Reach out to your Databricks account team to enroll in the Public Preview.

This page contains reference material for the PostgreSQL connector in Databricks Lakeflow Connect.

Automatic data transformations

Databricks automatically transforms the following PostgreSQL data types to Delta-compatible data types:

PostgreSQL type Delta type
BOOLEAN BOOLEAN
SMALLINT SMALLINT
INTEGER INT
BIGINT BIGINT
DECIMAL(p,s) DECIMAL(p,s)
NUMERIC(p,s) DECIMAL(p,s)
REAL FLOAT
DOUBLE PRECISION DOUBLE
SMALLSERIAL SMALLINT
SERIAL INT
BIGSERIAL BIGINT
MONEY STRING
CHAR(n) STRING
VARCHAR(n) STRING
TEXT STRING
BYTEA BINARY
DATE DATE
TIME STRING
TIME WITH TIME ZONE STRING
TIMESTAMP STRING
TIMESTAMP WITH TIME ZONE TIMESTAMP
INTERVAL STRING
UUID BINARY
JSON STRING
JSONB STRING
XML STRING
ARRAY STRING
CIDR STRING
INET STRING
MACADDR STRING
BIT(n) BINARY
BIT VARYING(n) BINARY

Important notes about date and time types

  • DATE data type is ingested as the Delta DATE type. The full PostgreSQL date range is supported. Inf and -Inf values are converted to null. BC dates are stored using astronomical year numbering. For example, 1 BC maps to year 0 and 2 BC maps to -1.
  • TIMESTAMP (without time zone) data type is ingested as strings. Inf and -Inf values are preserved as strings.
  • TIMESTAMP WITH TIME ZONE data type is ingested as the Delta TIMESTAMP type. The PostgreSQL-supported range is 4713-01-01 00:00:00.000000 BC to 294276-12-31 23:59:59.999999 AD, whereas the Databricks-supported range is -290308-12-21 BCE 19:59:06 GMT to +294247-01-10 CE 04:00:54 GMT. Timestamps above the Databricks maximum supported timestamp are converted to null. BC dates are stored using astronomical year numbering. For example, 1 BC maps to year 0 and 2 BC maps to -1. Inf and -Inf values are converted to null.
  • TIME, TIME WITH TIME ZONE, and INTERVAL data types are ingested as strings in their original format. You can use Databricks SQL functions to parse these strings into the appropriate time representations if needed. For INTERVAL data type, infinity values are mapped to 0 years 0 mins 0 days 0 hours 0 mins 0.0 secs.

User-defined and third-party data types

User-defined types and data types from third-party PostgreSQL extensions are ingested as strings. If you need to transform these types, you can use Databricks SQL functions to parse the string representations in downstream processing.

Composite types

PostgreSQL composite types (also known as row types) are ingested as strings. The string representation follows PostgreSQL's composite type format: (value1,value2,value3).

  • Last updated on