Module trase.tools.duckdb_postgres

Read the Trase PostgreSQL database from duckdb, and write query results to Parquet.

from trase.tools.duckdb_postgres import (
    connect_to_postgres_via_duckdb_read_only,
    write_query_to_parquet,
)

connection = connect_to_postgres_via_duckdb_read_only()
write_query_to_parquet(
    connection, "SELECT * FROM trase_postgres.views.regions", "regions.parquet"
)

Reading Postgres through duckdb's postgres extension rather than psycopg2 lets a query stream straight out to (Geo)Parquet without being held in memory, which is what the data release package export scripts need.

This is only for reading PostgreSQL. It is not a general duckdb connection: it sets up nothing else – no S3 credentials, no DuckLake catalog. If you need those, see trase.tools.ducklake.connection.

Functions

def connect_to_postgres_via_duckdb_read_only(alias='trase_postgres')

Open a duckdb connection with the Trase PostgreSQL database attached read-only.

The database is reached with the same libpq DSN as the rest of the codebase (see trase.config.postgres_dsn), so it honours the usual TRASE_POSTGRES__* settings and ~/.pg_service.conf. The spatial extension is loaded too, since a query that reads or writes geometry needs it.

def sql_literal(value)

Quote a string as a SQL literal. Names such as COTE D'IVOIRE contain quotes.

def write_query_to_parquet(connection, query, path, compression='zstd')

Run query on connection and write its result to path as Parquet.

A geometry column is written as GeoParquet, provided the duckdb spatial extension is loaded on the connection.

Raises if the query returned no rows. An empty file is never a wanted outcome for a published dataset, and writing one silently is worse than failing: it usually means an upstream table has not been built, or a filter no longer matches.

Returns the number of rows written.