PostgreSQL
PostgreSQL datasources use a high-performance native driver for optimized data transfer. No ODBC installation is required.
Connection Configuration
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.
