Postgres Reference

Authentication options and data type handling for Postgres connections.

Server

servers:
  - server: postgres
    type: postgres
    host: localhost
    port: 5432
    database: postgres
    schema: public

Authentication

Variable	Example	Description
DATACONTRACT_POSTGRES_USERNAME	postgres	Username
DATACONTRACT_POSTGRES_PASSWORD	mysecretpassword	Password

host, port (default 5432), database, and schema come from the contract's servers block, and can be overridden with DATACONTRACT_POSTGRES_HOST, DATACONTRACT_POSTGRES_PORT, DATACONTRACT_POSTGRES_DATABASE, and DATACONTRACT_POSTGRES_SCHEMA. For datacontract import postgres, they come from --source, --port, --database, and --schema.

Data types

Importing

datacontract import postgres reads the declared type from information_schema.columns and keeps it as physicalType exactly as the test path reads it back (character varying(36), numeric(10,2), timestamp with time zone), so an imported contract passes datacontract test without hand-editing.

datacontract import sql --dialect postgres maps DDL types as follows; the normalized SQL type is kept as physicalType.

Postgres type	logicalType
VARCHAR, CHAR, TEXT	string (maxLength for varchar/char)
SMALLINT, INT, INTEGER, BIGINT	integer
NUMERIC, DECIMAL, REAL, DOUBLE PRECISION, MONEY	number
BOOLEAN	boolean
DATE	date
TIME	time
TIMESTAMP, TIMESTAMPTZ	timestamp
BYTEA	string (format binary)
JSON	object
XML	string
UUID, SERIAL, BIGSERIAL, JSONB, INTERVAL, INET, array types (INT[])	(unset) — physicalType is still written

Testing

Postgres supports native type introspection: the declared physicalType is checked against information_schema.columns. Note that on Postgres text and varchar are distinct types — declare the one the column actually has. decimal and numeric are interchangeable, as are timezone variants of timestamps; length/precision is only enforced when declared (varchar matches varchar(255), varchar(255) does not match varchar(100)). A physicalType that isn't valid Postgres SQL falls back to the logical type category comparison.

The same applies to Postgres-compatible databases tested with type: postgres (e.g. RisingWave).

Logical type mapping

When no physicalType is declared, the CLI derives the native type from the logicalType — for example in datacontract export sql and the dbt exports. This table is generated from the converter in the CLI's code:

logicalType	Postgres type
string	text
integer	integer
number	numeric
boolean	boolean
date	date
timestamp	timestamptz
time	time
object	jsonb
array	text[]