PostgreSQL

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

See Supported Data Formats for database source categories and schema column types supported on PostgreSQL connections.

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

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

Example connection

{
  "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

{
  "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

{
  "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" }
  ]
}

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.

Partitioned table example

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

{
  "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

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

Troubleshooting

Connection refused

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

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

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

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.

​PostgreSQL

​Connection configuration

​URI format

​Required fields

​Example connection

​Dataset configuration (data_args)

​Required fields

​Optional fields

​table_options entry

​Complete example

​Connection

​Dataset

​Partitioned table example

​Driver and dependencies

​Troubleshooting

​Connection refused

​Authentication failed

​SSL/TLS errors

​Empty results from partitioned tables

​Slow queries

PostgreSQL

Connection configuration

URI format

Required fields

Example connection

Dataset configuration (data_args)

Required fields

Optional fields

table_options entry

Complete example

Connection

Dataset

Partitioned table example

Driver and dependencies

Troubleshooting

Connection refused

Authentication failed

SSL/TLS errors

Empty results from partitioned tables

Slow queries