​
Auto Schema Creation

If no columns are specified in CREATE FOREIGN TABLE, the appropriate Postgres schema will automatically be created.

CREATE FOREIGN TABLE trips ()
SERVER parquet_server
OPTIONS (files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet');

​
Configure Columns

The select option can be used to configure the columns mapped over the underlying file(s). This is useful for renaming, modifying, or generating additional columns. select takes any string that can be passed to a SQL SELECT statement. By default, it is set to *, which selects all columns as-is.

-- Only use a subset of columns
CREATE FOREIGN TABLE trips ()
SERVER parquet_server
OPTIONS (
    files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet',
    select 'vendorid, passenger_count'
);

-- Rename columns
CREATE FOREIGN TABLE trips ()
SERVER parquet_server
OPTIONS (
    files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet',
    select 'vendorid AS vendor_id, passenger_count AS passengers'
);

-- Generate additional columns
CREATE FOREIGN TABLE trips ()
SERVER parquet_server
OPTIONS (
    files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet',
    select '*, 2024 AS year, 1 AS month'
);

-- Modify existing column
CREATE FOREIGN TABLE trips ()
SERVER parquet_server
OPTIONS (
    files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet',
    select '(vendorid + 1) AS vendorid'
);

​
Preserve Casing

Whereas DuckDB preserves the casing of identifiers like column names by default, Postgres does not. In Postgres, identifiers are automatically lowercased unless wrapped in double quotation marks.

Postgres
-- The following two statements are equivalent
CREATE TABLE MyTable (MyColumn a);
CREATE TABLE mytable (mycolumn a);

-- Double quotes must be used to preserve casing
CREATE TABLE "MyTable" ("MyColumn" a);

By default, auto schema creation will create column names in lowercase. This can be changed with the preserve_casing option, which tells auto schema creation to wrap column names in double quotes.

CREATE FOREIGN TABLE trips ()
SERVER parquet_server
OPTIONS (
    files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet',
    preserve_casing 'true'
);

-- Columns are now case-sensitive
SELECT "RatecodeID" FROM trips LIMIT 1;

Was this page helpful?

PostgresDuckDB Settings