> ## Documentation Index
> Fetch the complete documentation index at: https://knowledge.cloudquant.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Configure PostgreSQL datasources with high-performance native driver support

# PostgreSQL

PostgreSQL datasources use a high-performance native driver for optimized data transfer. No ODBC installation is required.

<Note>
  See [Supported Data Formats](/datasource-config/supported-formats) for database source categories and schema column types supported on PostgreSQL connections.
</Note>

## Connection configuration

### URI format

```
postgresql://user:password@host:5432/database
```

### Required fields

| Field                 | Type   | Description                  |
| --------------------- | ------ | ---------------------------- |
| `connection_type`     | string | Must be `"postgresql"`       |
| `connection_sub_type` | string | Must be `"database"`         |
| `uri`                 | string | PostgreSQL connection string |
| `server`              | string | Database host address        |
| `database`            | string | Database name                |
| `username`            | string | Database user                |
| `password`            | string | Database password            |

<Note>
  The native driver communicates with the database using the PostgreSQL binary protocol and produces data batches directly, avoiding row-by-row conversion overhead.
</Note>

### Example connection

```json theme={null}
{
  "name": "postgres-market-data",
  "connection_type": "postgresql",
  "connection_sub_type": "database",
  "uri": "postgresql://libuser:s3cureP@ss@db.example.com:5432/marketdata",
  "server": "db.example.com",
  "database": "marketdata",
  "username": "libuser",
  "password": "s3cureP@ss"
}
```

## Dataset configuration (data\_args)

### Required fields

| Field              | Type                   | Description                                                |
| ------------------ | ---------------------- | ---------------------------------------------------------- |
| `table_options`    | list                   | Tables to query (see below)                                |
| `database`         | string                 | Connection reference name                                  |
| `data_dt_column`   | string or list         | Column(s) containing the datetime value                    |
| `data_dt_format`   | bool or string or list | `true` for native datetime auto-detect, or strptime format |
| `data_dt_timezone` | string                 | Timezone of the source data                                |
| `data_key_column`  | string or list         | Column(s) used as the symbol/key for query filtering       |

### Optional fields

| Field           | Type | Default | Description                              |
| --------------- | ---- | ------- | ---------------------------------------- |
| `batch_size`    | int  | `10000` | Number of rows per query batch           |
| `data_dt_nudge` | int  | `0`     | Microsecond offset applied to timestamps |

### table\_options entry

Each entry in `table_options` describes a table to query:

| Field          | Type   | Description                                         |
| -------------- | ------ | --------------------------------------------------- |
| `db`           | string | Database name                                       |
| `table_like`   | string | Table name or pattern                               |
| `token`        | string | Table identifier token                              |
| `zone`         | string | Timezone, default `"America/New_York"`              |
| `dt_mode`      | string | *(Optional)* Datetime mode                          |
| `dt_regex`     | string | *(Optional)* Regex for partitioned tables           |
| `dt_format`    | string | *(Optional)* Datetime format for partitioned tables |
| `nudge_micros` | int    | *(Optional)* Microsecond nudge                      |

## Complete example

### Connection

```json theme={null}
{
  "name": "postgres-trades",
  "connection_type": "postgresql",
  "connection_sub_type": "database",
  "uri": "postgresql://libuser:s3cureP@ss@db.example.com:5432/trades_db",
  "server": "db.example.com",
  "database": "trades_db",
  "username": "libuser",
  "password": "s3cureP@ss"
}
```

### Dataset

```json theme={null}
{
  "name": "us-equity-trades",
  "connection": "postgres-trades",
  "data_args": {
    "table_options": [
      {
        "db": "trades_db",
        "table_like": "daily_trades",
        "token": "daily_trades",
        "zone": "America/New_York"
      }
    ],
    "database": "postgres-trades",
    "batch_size": 10000,
    "data_dt_column": "trade_time",
    "data_dt_format": true,
    "data_dt_timezone": "America/New_York",
    "data_dt_nudge": 0,
    "data_key_column": "symbol"
  },
  "schema": [
    { "name": "symbol", "type": "string", "group": "key", "description": "Ticker symbol" },
    { "name": "trade_time", "type": "string", "group": "time", "description": "Trade timestamp" },
    { "name": "price", "type": "double", "group": "value", "description": "Trade price" },
    { "name": "volume", "type": "int64", "group": "value", "description": "Trade volume" },
    { "name": "exchange", "type": "string", "group": "value", "description": "Exchange code" }
  ]
}
```

<Tip>
  Set `data_dt_format` to `true` when using native PostgreSQL `timestamp` or `timestamptz` columns. CloudQuant Data Liberator will auto-detect the datetime type and convert directly without string parsing.
</Tip>

## Partitioned table example

For tables partitioned by date (e.g., `trades_2024_01`, `trades_2024_02`):

```json theme={null}
{
  "table_options": [
    {
      "db": "trades_db",
      "table_like": "trades_%",
      "token": "trades",
      "zone": "America/New_York",
      "dt_regex": "trades_(\\d{4})_(\\d{2})",
      "dt_format": "%Y_%m"
    }
  ]
}
```

## Driver and dependencies

| Component       | Details                    |
| --------------- | -------------------------- |
| Driver          | PostgreSQL native driver   |
| Protocol        | PostgreSQL binary protocol |
| Data conversion | Native (high-performance)  |
| ODBC required   | No                         |
| Default port    | 5432                       |

<Note>
  The native driver is bundled with CloudQuant Data Liberator. No additional driver installation is needed on the host system.
</Note>

## Troubleshooting

### Connection refused

Verify the PostgreSQL server is accepting connections on the specified host and port. Check `pg_hba.conf` for client authentication rules.

```bash theme={null}
psql -h db.example.com -p 5432 -U libuser -d marketdata
```

### Authentication failed

Ensure the `username` and `password` in the connection match a valid PostgreSQL role. Check that the role has `CONNECT` privilege on the database and `SELECT` privilege on the target tables.

### SSL/TLS errors

If the server requires SSL, append `?sslmode=require` to the URI:

```
postgresql://libuser:s3cureP@ss@db.example.com:5432/marketdata?sslmode=require
```

<Warning>
  Using `sslmode=disable` in production is not recommended. Always use `sslmode=require` or `sslmode=verify-full` when connecting over untrusted networks.
</Warning>

### Empty results from partitioned tables

Verify that `dt_regex` correctly matches the table name suffix and that `dt_format` corresponds to the captured groups. Test the regex against your actual table names.

### Slow queries

* Increase `batch_size` for large result sets (up to `100000`).
* Ensure the datetime and key columns are indexed in PostgreSQL.
* Use `table_like` to target specific tables rather than scanning all partitions.