SQL & clients

Join PostgreSQL sources#

Declare orders_db and crm in [[sources]], then query them using qualified names. The following query requires the illustrated orders and contacts tables in your own databases; they are not built-in fixtures.

SELECT o.id, c.email, o.total
FROM orders_db.public.orders AS o
JOIN crm.public.contacts AS c
  ON o.contact_id = c.id
WHERE o.total > 100
ORDER BY o.total DESC;

Join Parquet and PostgreSQL#

iceberg is the local Parquet-backed table. pg_table is a legacy alias registered when an introspected source has a my_pg_table table. Your Parquet files must match the fixed user_id: Int64, data: Utf8 schema. Prefer canonical names for new PostgreSQL queries.

SELECT i.user_id, i.data, p.extra_info
FROM iceberg AS i
JOIN pg_table AS p ON i.user_id = p.user_id
WHERE i.user_id = 42;

Discover registered tables#

Igloo enables DataFusion’s information_schema. PostgreSQL catalogs are created at startup from discovered base tables; unsupported column types are skipped with a warning.

SHOW TABLES;

SELECT table_catalog, table_schema, table_name
FROM information_schema.tables;

Prepared statements#

The PostgreSQL endpoint supports Parse / Bind / Execute with numbered parameters. DataFusion infers parameter types from context when clients leave them unspecified. Text and binary result formats are supported.

Match the client’s bound value type to the inferred type. For example, current Psycopg integer auto-encoding may choose int2 for a small value while the query expects int8; use psycopg.types.numeric.Int8 for a bigint column. The implementation does not guarantee every PostgreSQL driver behavior.

Prepared statements with parameters bypass result caching. Use your client library’s parameter API rather than interpolating values into SQL.

SELECT * FROM app.public.customers WHERE id = $1;

Compatibility boundaries#

The endpoint speaks the PostgreSQL wire protocol, but SQL is planned by DataFusion. PostgreSQL-specific functions, DDL, transactions, and extensions should not be assumed to work.

Authentication, TLS, Arrow Flight SQL, REST/gRPC query endpoints, and documented BI-tool walkthroughs remain roadmap work. The pgwire server is a development endpoint.