Foreign Table Schema
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.
-- 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?