---
title: Foreign Table Schema
---

## Auto Schema Creation

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

```sql
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.

```sql
-- 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.

```sql 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.

```sql
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;
```