# Postgres Reference

> All Postgres authentication options and data type mappings.

#  Postgres Reference

Authentication options and data type handling for [Postgres connections](../connect/postgres.md).

## 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.

## Data types

### Importing

`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).

{/* AUTOGENERATED TYPE MAPPING: do not edit by hand; regenerate with update_reference_types.py */}

### 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[]` |

{/* END AUTOGENERATED TYPE MAPPING */}