DuckDB trips on columns that are SQL reserved words.

[iangow]

What happens?

DuckDB isn't quoting column names, which causes problems with column names are SQL reserved words.

psql (18.1 (Postgres.app))
Type "help" for help.

igow=# CREATE TABLE firm_data (
igow(#     firm_id  INTEGER,
igow(#     date     DATE,
igow(#     at       FLOAT8
igow(# );
CREATE TABLE
igow=# INSERT INTO firm_data (firm_id, date, at) VALUES
igow-#     (1001, '2024-12-31', 1250000.50),
igow-#     (1002, '2024-12-31', 987654.32),
igow-#     (1001, '2025-03-31', 1312500.75);
INSERT 0 3
igow=# SELECT * FROM firm_data ;
WARNING:  (PGDuckDB/CreatePlan) Prepared query returned an error: Parser Error: syntax error at or near "FROM"

LINE 1: SELECT firm_id, date, at FROM pgduckdb.public.firm_data
                                 ^

LINE 1: SELECT firm_id, date, at FROM pgduckdb.public.firm_data
                                 ^
 firm_id |    date    |     at     
---------+------------+------------
    1001 | 2024-12-31 |  1250000.5
    1002 | 2024-12-31 |  987654.32
    1001 | 2025-03-31 | 1312500.75
(3 rows)

igow=# 

To Reproduce

CREATE TABLE firm_data (
    firm_id  INTEGER,
    date     DATE,
    at       FLOAT8
);

INSERT INTO firm_data (firm_id, date, at) VALUES
    (1001, '2024-12-31', 1250000.50),
    (1002, '2024-12-31', 987654.32),
    (1001, '2025-03-31', 1312500.75);

SELECT * FROM firm_data;

OS:

MacOS

pg_duckdb Version (if built from source use commit hash):

1.1.0, dafe12d

Postgres Version (if built from source use commit hash):

18.1 (Postgres.app)

Hardware:

NULL

Full Name:

Ian Gow

Affiliation:

Unaffiliated

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a source build

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

[iangow]

Note that DuckDB even strips the " characters found in the original SQL.

igow=# SELECT "firm_id", "date", "at" FROM firm_data;
WARNING:  (PGDuckDB/CreatePlan) Prepared query returned an error: Parser Error: syntax error at or near "FROM"

LINE 1: SELECT firm_id, date, at FROM pgduckdb.public.firm_data
                                 ^

LINE 1: SELECT firm_id, date, at FROM pgduckdb.public.firm_data
                                 ^
 firm_id |    date    |     at     
---------+------------+------------
    1001 | 2024-12-31 |  1250000.5
    1002 | 2024-12-31 |  987654.32
    1001 | 2025-03-31 | 1312500.75
(3 rows)

[iangow]

There's discussion of a closely related issue in another package here.

[iangow]

Perhaps allowing SET duckdb.preserve_identifier_case=true could provide a solution (of sorts) to this tricky issue.