Support DuckDB Dialect

[chanon]

DuckDB is gaining a lot of popularity and version 1.0.0 was just released.

the project has attracted tens of thousands of stars and followers on GitHub and social media platforms. Download counts are in the millions each month

The SQL dialect I think is very similar to PostgreSQL and I have been able to use it in PostgreSQL mode initially, but the issue I'm running into which is intractable is parameterized queries use ? characters instead of $1, $2 ...

Not sure if there is another existing dialect that could work. (Tried MySQL, but more complex CTE / window function queries failed.)

Thank you so much for this awesome package!

[closedLoop]

I had Gemini 2.5 opine on how how to port postgres support here to duckdb. I fed it all of duckdb's docs as well as this repo.
I hope this is useful to anyone or agent trying to implement this feature!

---

Okay, I will generate a single, in-depth document about DuckDB syntax, its differences with PostgreSQL, and its various loading functions. This document will be structured to aid in porting a PostgreSQL SQL parser to support DuckDB.

DuckDB Syntax and PostgreSQL Compatibility Guide for Parser Development

Version: Based on DuckDB documentation reflecting features up to approximately v1.0/v1.1

Purpose: This document provides an in-depth overview of DuckDB's SQL syntax, focusing on aspects relevant for parser development, particularly for those familiar with PostgreSQL. It highlights key differences, data loading mechanisms, and unique DuckDB features.

1. Introduction to DuckDB's SQL Dialect

DuckDB aims for strong compatibility with PostgreSQL's SQL dialect, making it a familiar environment for many SQL users. However, as an in-process analytical data management system, DuckDB also introduces its own "Friendly SQL" features and optimizations that can lead to syntactic or semantic differences.

Key philosophies relevant to parsing:

• PostgreSQL Compatibility: When in doubt, DuckDB often follows PostgreSQL.
• Performance-Oriented: Syntax may be influenced by the desire for efficient execution, especially for analytical queries.
• Ease of Use: DuckDB often provides syntactic sugar or more lenient parsing for common tasks (e.g., FROM-first syntax).

2. Core SQL Syntax

DuckDB supports standard SQL DDL (Data Definition Language), DML (Data Manipulation Language), and DQL (Data Query Language) statements.

2.1. Keywords and Identifiers

• Case Insensitivity: Keywords (e.g., SELECT, FROM) and function names (e.g., SUM(), count()) are case-insensitive.
• Identifier Case Preservation:
  • By default (preserve_identifier_case = true), DuckDB is case-insensitive for matching identifiers (table names, column names) but preserves the original casing in the catalog. For example, CREATE TABLE MyTable (...) creates a table that can be queried as mytable, MYTABLE, or MyTable, and will be displayed as MyTable.
  • This differs from PostgreSQL, which folds unquoted identifiers to lowercase.
  • Quoted identifiers (e.g., "My Table") allow for spaces, special characters, and reserved keywords. DuckDB, unlike PostgreSQL, also treats quoted identifiers case-insensitively for matching purposes but preserves their catalog casing.
• Reserved Keywords: A list can be obtained via SELECT * FROM duckdb_keywords() WHERE keyword_category = 'reserved';. Unquoted identifiers cannot be reserved keywords.
• Identifier Rules: Unquoted identifiers must start with a letter or underscore, followed by letters, digits, or underscores.
• Deduplicating Identifiers: If an operation would result in duplicate column names (e.g., from UNNEST or certain joins), DuckDB automatically renames subsequent occurrences by appending _⟨count⟩ (e.g., a, a_1, a_2).

2.2. Data Definition Language (DDL)

2.2.1. CREATE TABLE

Standard syntax is supported:

CREATE TABLE table_name (
    column_name_1 data_type [column_constraint, ...],
    column_name_2 data_type [column_constraint, ...],
    ...
    [table_constraint, ...]
);

• CREATE OR REPLACE TABLE: Atomically replaces a table if it exists, or creates it if it does not. This is a DuckDB convenience.
• CREATE TABLE IF NOT EXISTS: Creates the table only if it does not already exist.
• CREATE TABLE AS SELECT (CTAS): A powerful feature for creating a table from the result of a query. DuckDB's CTAS is very flexible.

  CREATE TABLE new_table AS SELECT col1, col2*2 AS new_col FROM existing_table WHERE condition;
  -- DuckDB specific:
  CREATE TABLE new_table AS FROM read_csv('data.csv'); -- Infers schema

• Temporary Tables: CREATE TEMP TABLE or CREATE TEMPORARY TABLE creates session-scoped tables.
• Constraints:
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY (implicitly NOT NULL and UNIQUE)
  • CHECK (expression)
  • FOREIGN KEY (column) REFERENCES other_table(column)
  • Constraints are enforced. Violations raise errors.
  • Defining PRIMARY KEY or UNIQUE constraints automatically creates an ART index.
• Generated Columns:

  CREATE TABLE t1 (
      x FLOAT,
      two_x FLOAT GENERATED ALWAYS AS (2 * x) VIRTUAL -- or STORED (VIRTUAL is default and currently only supported)
  );

  The type can often be inferred.

2.2.2. ALTER TABLE

DuckDB supports a range of ALTER TABLE operations:

-- Add column
ALTER TABLE table_name ADD COLUMN column_name data_type [DEFAULT default_expr];

-- Drop column
ALTER TABLE table_name DROP COLUMN column_name;

-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;

-- Rename column
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

-- Change column type
ALTER TABLE table_name ALTER column_name TYPE new_data_type [USING expression];

-- Set/Drop column default
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_expr;
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

-- Set/Drop NOT NULL constraint
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

-- Add Primary Key (to existing table)
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

• Limitations: Dropping columns involved in indexes or multi-column check constraints might be restricted. Changes are transactional.

2.2.3. DROP TABLE

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

• IF EXISTS: Suppresses error if the table does not exist.
• CASCADE: Drops dependent objects (not fully implemented for all dependencies, e.g., views on tables might require manual dropping or recreation).
• RESTRICT: (Default) Prevents dropping if dependent objects exist.

2.2.4. Schemas

CREATE SCHEMA [IF NOT EXISTS] schema_name;
DROP SCHEMA [IF EXISTS] schema_name [CASCADE | RESTRICT];

• Default schema is main. Temporary objects reside in temp.main.

2.2.5. Views

CREATE [OR REPLACE] [TEMP] VIEW view_name [(column_name_list)] AS query;
DROP VIEW [IF EXISTS] view_name [CASCADE | RESTRICT];
ALTER VIEW view_name RENAME TO new_view_name;

• Views are not physically materialized. The query is re-run each time the view is referenced.

2.2.6. Indexes (ART Indexes)

CREATE [UNIQUE] INDEX index_name ON table_name (column_name | (expression) [, ...]);
DROP INDEX [IF EXISTS] index_name;

• ART indexes are automatically created for PRIMARY KEY and UNIQUE constraints.
• Mainly useful for point lookups and highly selective queries.
• DuckDB uses zonemaps extensively, which are automatic and often sufficient.

2.2.7. Sequences

CREATE [OR REPLACE] [TEMP] SEQUENCE [IF NOT EXISTS] sequence_name
    [INCREMENT [BY] increment_value]
    [MINVALUE min_value | NO MINVALUE]
    [MAXVALUE max_value | NO MAXVALUE]
    [START [WITH] start_value]
    [CACHE cache_size]
    [[NO] CYCLE];

DROP SEQUENCE [IF EXISTS] sequence_name [CASCADE | RESTRICT];

• Used for generating unique integer values (e.g., for primary keys).
• Use nextval('sequence_name') to get the next value.
• Use currval('sequence_name') to get the last value generated by nextval in the current session.

2.2.8. Types (User-Defined Types)

CREATE TYPE type_name AS ENUM ('value1', 'value2', ...);
CREATE TYPE type_name AS (field_name_1 field_type_1, ...); -- STRUCT
CREATE TYPE type_name AS UNION (tag_name_1 tag_type_1, ...);
CREATE TYPE type_alias AS existing_type;
DROP TYPE [IF EXISTS] type_name [CASCADE | RESTRICT];

• Enums, Structs, and Unions can be defined as named types.
• Type aliases allow giving an alternative name to an existing type.

2.2.9. Macros

Macros are SQL-level functions that are expanded at query planning time.

CREATE [OR REPLACE] [TEMP] MACRO [IF NOT EXISTS] macro_name ([param_name [:= default_value]] [, ...]) AS expression;
CREATE [OR REPLACE] [TEMP] MACRO [IF NOT EXISTS] macro_name ([param_name [:= default_value]] [, ...]) AS TABLE query;
DROP MACRO [IF EXISTS] macro_name;
DROP MACRO [IF EXISTS] TABLE macro_name;

• Scalar macros return a single value. Table macros return a table.
• Useful for creating reusable SQL snippets or emulating UDFs without C++/Python.
• Overloading based on the number of parameters is supported.
• Default parameters must be specified with :=.

2.3. Data Manipulation Language (DML)

2.3.1. INSERT INTO

INSERT INTO table_name [(column_list)]
    { VALUES (value_list_1), (value_list_2), ... | query };

-- DuckDB Specific Enhancements
INSERT INTO table_name BY NAME (SELECT ...); -- Matches columns by name
INSERT OR IGNORE INTO table_name ...; -- Ignores conflicts on UNIQUE/PRIMARY KEY
INSERT OR REPLACE INTO table_name ...; -- Replaces conflicting rows (UPSERT)
-- SQL Standard UPSERT (also supported)
INSERT INTO table_name ... ON CONFLICT (target_column) DO UPDATE SET ... WHERE ...;
INSERT INTO table_name ... ON CONFLICT (target_column) DO NOTHING;

• RETURNING clause is supported to return inserted/updated rows.

2.3.2. UPDATE

UPDATE table_name
    SET column_name_1 = expression_1,
        column_name_2 = expression_2, ...
    [FROM from_list]
    [WHERE condition]
    [RETURNING returning_list];

• The FROM clause allows updating based on values from other tables.

2.3.3. DELETE FROM

DELETE FROM table_name
    [USING from_list]
    [WHERE condition]
    [RETURNING returning_list];

• TRUNCATE table_name is an alias for DELETE FROM table_name;.

2.3.4. COPY

The COPY statement moves data between DuckDB tables and external files (CSV, Parquet, JSON).

-- Import data from a file into a table
COPY table_name [(column_list)]
    FROM 'file_path' | STDIN
    [(option [, ...])];

-- Export data from a table or query to a file
COPY table_name [(column_list)] | (query)
    TO 'file_path' | STDOUT
    [(option [, ...])];

• Formats: CSV, PARQUET, JSON.
• Common Options: HEADER, DELIMITER (for CSV), COMPRESSION (for Parquet).
• AUTO_DETECT TRUE (for COPY FROM CSV/JSON) enables schema and dialect sniffing.
• For a detailed list of options, see the respective file format documentation.
• DuckDB prefers read_csv_auto, read_parquet, etc., for ad-hoc querying over COPY FROM.

2.4. Data Query Language (DQL) - The SELECT Statement

DuckDB's SELECT statement is highly flexible.

[WITH cte_name AS (subquery) [, ...]]
SELECT [DISTINCT [ON (expression [, ...])]] select_list
    [FROM from_item [, ...]]
    [USING SAMPLE sample_clause]
    [WHERE search_condition]
    [GROUP BY grouping_element [, ...]]
    [HAVING search_condition]
    [WINDOW window_name AS (window_definition) [, ...]]
    [QUALIFY search_condition]
    [ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
    [LIMIT count [OFFSET start]];

Key aspects relevant to parsing:

• FROM-First Syntax:

  FROM table_name SELECT col1, col2;
  FROM table_name; -- Equivalent to SELECT * FROM table_name

• SELECT * EXCLUDE / REPLACE:

  SELECT * EXCLUDE (col_to_exclude) FROM tbl;
  SELECT * REPLACE (col_to_replace + 1 AS col_to_replace) FROM tbl;

• COLUMNS Expression:

  SELECT COLUMNS(c -> c LIKE 'prefix_%') FROM tbl; -- Lambda function
  SELECT COLUMNS('^prefix_.*') FROM tbl; -- Regex
  SELECT COLUMNS(['colA', 'colB']) FROM tbl; -- List of names
  SELECT min(COLUMNS(*)) FROM tbl; -- Apply function to all selected columns
  SELECT coalesce(*COLUMNS(*)) FROM tbl; -- Unpack columns into a function

• Column Aliases: Can be referenced in WHERE, GROUP BY, HAVING, ORDER BY.
• GROUP BY ALL: Groups by all non-aggregate expressions in the SELECT list.
• ORDER BY ALL: Orders by all columns in the SELECT list.
• QUALIFY Clause: Filters results of window functions.
• USING SAMPLE Clause: Samples data from the FROM clause. TABLESAMPLE is also supported directly on table references.
• WINDOW Clause: Defines named window specifications.
• Set Operations: UNION [ALL] [BY NAME], INTERSECT [ALL], EXCEPT [ALL].
  • BY NAME is a DuckDB extension for UNION that matches columns by name rather than position.
• Joins:
  • Standard: INNER JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN, CROSS JOIN, NATURAL JOIN.
  • Join conditions: ON boolean_expression or USING (column_list).
  • Specialized:
    • ASOF JOIN: For time-series data, finding the latest record before or at a certain point.
    • POSITIONAL JOIN: Joins tables based on row order (useful for dataframes).
    • LATERAL JOIN: Allows a subquery in the FROM clause to reference preceding items. DuckDB often infers this, so the LATERAL keyword is optional.

2.5. Other Statements

• EXPLAIN [ANALYZE] query: Shows the query plan. ANALYZE executes and shows runtime statistics.
• DESCRIBE table_name | query: Shows schema of a table or query result. SHOW is an alias.
• SUMMARIZE table_name | query: Computes and displays summary statistics for all columns.
• ATTACH 'database_file' [AS alias] [(TYPE DUCKDB|SQLITE|POSTGRES|MYSQL, READ_ONLY)]: Makes another database accessible.
• DETACH alias: Detaches a previously attached database.
• USE database_name[.schema_name]: Sets the default database/schema.
• PREPARE name [(data_type [, ...])] AS statement: Creates a prepared statement.
• EXECUTE name [(parameter [, ...])]: Executes a prepared statement.
• DEALLOCATE [PREPARE] name: Drops a prepared statement.
• Transaction Control: BEGIN [TRANSACTION], COMMIT [TRANSACTION], ROLLBACK [TRANSACTION].
• SET option = value | SET option TO value: Configures DuckDB settings.
• RESET option: Resets a configuration option to its default.
• INSTALL 'extension_name' [FROM 'repository_url']: Installs an extension.
• LOAD 'extension_name': Loads an installed extension.
• UPDATE EXTENSIONS [(extension_name [, ...])]: Updates installed extensions.
• PIVOT table_or_query ON pivot_column [IN (pivot_value_list)] USING aggregate_function(value_column) [GROUP BY group_column_list]: Transforms rows to columns.
• UNPIVOT table_or_query ON (unpivot_column_list) INTO NAME name_column VALUE value_column_list: Transforms columns to rows.
• CREATE SECRET / DROP SECRET: Manages secrets for accessing external services (S3, GCS, etc.).

3. Data Types

DuckDB supports a rich set of data types. Parser implementers should be aware of how these are declared and how they cast.

3.1. Primitive Types

DuckDB Type	Aliases	Notes
BOOLEAN	BOOL, LOGICAL	TRUE, FALSE, NULL
TINYINT	INT1	1-byte signed integer
SMALLINT	INT2, SHORT	2-byte signed integer
INTEGER	INT, INT4, SIGNED	4-byte signed integer
BIGINT	INT8, LONG	8-byte signed integer
HUGEINT	INT128	16-byte signed integer
UTINYINT		1-byte unsigned integer
USMALLINT		2-byte unsigned integer
UINTEGER		4-byte unsigned integer
UBIGINT		8-byte unsigned integer
UHUGEINT		16-byte unsigned integer
FLOAT	REAL, FLOAT4	4-byte floating-point
DOUBLE	FLOAT8	8-byte floating-point
DECIMAL(width,scale)	NUMERIC(width,scale)	Fixed-point number. width up to 38. Default DECIMAL(18,3).
VARCHAR	CHAR, BPCHAR, TEXT, STRING	Variable-length string. Length specifier (e.g., VARCHAR(255)) is parsed but ignored.
BLOB	BYTEA, BINARY, VARBINARY	Variable-length binary data.
DATE		Calendar date (year, month, day).
TIME	TIME WITHOUT TIME ZONE	Time of day (hour, minute, second, microsecond).
TIMETZ	TIME WITH TIME ZONE	Time of day with time zone.
TIMESTAMP	DATETIME, TIMESTAMP WITHOUT TIME ZONE	Timestamp (date and time) with microsecond precision.
TIMESTAMP_NS		Timestamp with nanosecond precision.
TIMESTAMP_MS		Timestamp with millisecond precision.
TIMESTAMP_S		Timestamp with second precision.
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE	Timestamp with time zone, microsecond precision. Stores as UTC.
INTERVAL		Time duration.
UUID		Universally Unique Identifier.
BIT	BITSTRING	Variable-length string of 1s and 0s.
JSON		JSON data type (requires json extension).
INET		IPv4/IPv6 address (requires inet extension).

3.2. Nested Types

• LIST: Variable-length list of elements of the same type. Syntax: element_type[] or LIST(element_type).
  • Example: INTEGER[], LIST(VARCHAR).
  • Creation: [1, 2, 3], list_value(1, 2, 3).
• ARRAY: Fixed-length list of elements of the same type. Syntax: element_type[length] or ARRAY(element_type, length).
  • Example: INTEGER[3], ARRAY(VARCHAR, 5).
  • Creation: array_value(1, 2, 3).
  • Postgres ARRAY is variable-length, similar to DuckDB's LIST.
• STRUCT: Ordered collection of named fields, each with its own type. Syntax: STRUCT(field_name_1 field_type_1, ...) or ROW(field_type_1, ...) for unnamed structs.
  • Example: STRUCT(i INTEGER, s VARCHAR).
  • Creation: {'key1': value1, 'key2': value2} or struct_pack(key1 := value1, ...) or row(value1, value2).
• MAP: Collection of key-value pairs. Keys must be of the same type, values must be of the same type. Syntax: MAP(key_type, value_type).
  • Example: MAP(VARCHAR, INTEGER).
  • Creation: map(['key1', 'key2'], [value1, value2]) or map {'key1': value1, 'key2': value2}.
• UNION: Tagged union type, can hold a value of one of several specified types. Syntax: UNION(tag_name_1 type_1, ...)
  • Example: UNION(i INTEGER, s VARCHAR).
  • Creation: union_value(tag_name := value).

3.3. User-Defined Types (Enums)

DuckDB supports ENUM types.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (name TEXT, current_mood mood);

3.4. Literal Types

• NULL: Represents a missing value.
• Integer Literals: E.g., 123. Parsed as INTEGER_LITERAL, implicitly castable to any fitting integer type (TINYINT, INTEGER, BIGINT, HUGEINT, DECIMAL, etc.).
• String Literals: E.g., 'hello'. Parsed as STRING_LITERAL, implicitly castable to any other type if the string content is valid for that type (e.g., '123' can cast to INTEGER, '1992-01-01' to DATE).
  • Escaping: '' for a single quote. e'Hello\nworld' for C-style escapes.
  • Dollar-quoting: $$Hello world$$ or $tag$Hello $$ world$tag$.
  • Underscores in numeric literals: 1_000_000 is 1000000.

3.5. Type Casting

• Explicit Cast: CAST(expression AS type) or expression::type.
• TRY_CAST(expression AS type): Returns NULL if cast fails, instead of an error.
• Implicit Casts: DuckDB performs implicit casts in many situations (e.g., function arguments, comparisons). Generally, casts that cannot fail (e.g., INTEGER to DOUBLE) are allowed implicitly.
• Lossy Casts: Explicit casts that lose precision are allowed (e.g., DOUBLE to INTEGER rounds).
• Overflows: Casts resulting in overflow error (e.g., CAST(999 AS TINYINT)).
• VARCHAR as Universal Target: Any type can be cast to VARCHAR.
• STRING_LITERAL to Anything: String literals can be implicitly cast to any type. This is a key difference from VARCHAR.

4. Data Loading Functions

DuckDB emphasizes efficient data loading through specialized functions, which are often preferred over COPY FROM for ad-hoc querying and offer more flexibility. These functions are table-producing functions used in the FROM clause.

4.1. read_csv_auto() / read_csv()

Reads CSV files. read_csv_auto (or just providing the filename if it ends in .csv or .csv.gz etc.) is the most common variant, automatically detecting schema and CSV parameters.

-- Auto-detection
SELECT * FROM 'data.csv';
SELECT * FROM read_csv_auto('data.csv');

-- Manual configuration
SELECT * FROM read_csv('data.csv', header=true, delim=',', columns={'colA': 'INTEGER', 'colB': 'VARCHAR'});

Common Parameters for Parser:

• auto_detect: (Boolean, default true for read_csv_auto, false for read_csv) Enable/disable auto-detection of dialect and types.
• header: (Boolean) Whether the file has a header row.
• delim or sep: (VARCHAR) The delimiter character.
• quote: (VARCHAR) The quote character.
• escape: (VARCHAR) The escape character.
• skip: (BIGINT) Number of lines to skip at the start.
• columns: (STRUCT) Define column names and types (e.g., {'name': 'VARCHAR', 'age': 'INTEGER'}). Disables type auto-detection for specified columns.
• types or dtypes: (LIST or STRUCT) Similar to columns but can be a list for positional type assignment.
• names or column_names: (LIST) Provide column names if header=false.
• all_varchar: (Boolean) Read all columns as VARCHAR.
• dateformat, timestampformat: Format strings for parsing dates/timestamps.
• compression: (auto, none, gzip, zstd)
• filename: (Boolean) If true, adds a filename column with the source file path.
• hive_partitioning: (Boolean) If true, infers columns from Hive-style directory paths.
• union_by_name: (Boolean) For multiple files, unions columns by name instead of position.
• sample_size: (BIGINT, default 20480) Number of rows to sample for auto-detection. -1 for whole file.
• ignore_errors: (Boolean) Skip lines that cause parsing errors.
• store_rejects: (Boolean) If true, stores rejected rows and scan info in temporary tables (reject_errors, reject_scans).

4.2. read_parquet()

Reads Parquet files. Schema is read from Parquet metadata.

SELECT * FROM 'data.parquet';
SELECT * FROM read_parquet('data.parquet');

Common Parameters for Parser:

• binary_as_string: (Boolean) Interpret BYTE_ARRAY Parquet columns as VARCHAR.
• filename: (Boolean) Adds a filename column.
• file_row_number: (Boolean) Adds a file_row_number column.
• hive_partitioning: (Boolean)
• union_by_name: (Boolean)
• encryption_config: (STRUCT) For reading encrypted Parquet files.

4.3. read_json_auto() / read_json() / read_json_objects()

Reads JSON files. read_json_auto (or just 'data.json') tries to detect the JSON structure (array of objects, newline-delimited objects) and schema.

SELECT * FROM 'data.json';
SELECT * FROM read_json_auto('data.json');

SELECT * FROM read_json('data.json', format='array', columns={'id': 'INTEGER', 'data': 'VARCHAR'});

Common Parameters for Parser:

• auto_detect: (Boolean)
• columns: (STRUCT) Define schema.
• format: (auto, newline_delimited, array, unstructured)
• records: (auto, true, false) Whether objects should be unpacked into columns or returned as structs.
• dateformat, timestampformat.
• compression.
• filename.
• hive_partitioning.
• union_by_name.
• maximum_object_size: (UINTEGER) Max size of a JSON object.
• ignore_errors: (Boolean, only for newline_delimited).

4.4. Multiple File Reading

All read_* functions support reading multiple files:

• Globs: read_csv('data/*.csv'), read_parquet('s3://bucket/data/**/*.parquet')
• Lists of files/globs: read_csv(['file1.csv', 'data/file*.csv'])

4.5. HTTPFS and Other Filesystems

• The httpfs extension (often autoloaded) enables reading from HTTP(S) and S3-compatible object stores (s3://, gs://, r2://, etc.).

  SELECT * FROM 'https://example.com/data.parquet';
  SELECT * FROM 's3://my-bucket/data.csv';

• Python client can use fsspec filesystems.
• Azure Blob Storage via azure extension (az://).
• Delta Lake via delta extension (delta_scan('path')).
• Iceberg via iceberg extension (iceberg_scan('path_to_metadata.json')).

4.6. COPY FROM

The COPY table_name FROM 'file_path' (options) statement is also available for CSV, Parquet, and JSON. It loads data into an existing table. The table schema dictates the types, though some options like auto_detect=true for CSV can help.

5. Key Differences with PostgreSQL

This section is crucial for parser porting.

1. Identifier Case Sensitivity:

   • DuckDB: Case-insensitive for matching (both quoted and unquoted), but preserves original case in catalog by default. SET preserve_identifier_case = false; forces lowercase storage, aligning more with PostgreSQL's unquoted behavior.
   • PostgreSQL: Folds unquoted identifiers to lowercase. Quoted identifiers are case-sensitive and preserve case.
   • Parser Implication: A parser targeting DuckDB may choose to always fold identifiers to lowercase internally if preserve_identifier_case is off, or handle case-insensitivity while remembering original casing if it's on. For lookups, case-insensitivity is key.

2. VARCHAR Length:

   • DuckDB: VARCHAR(N) is parsed, but N is ignored. VARCHAR has no practical length limit beyond memory/disk.
   • PostgreSQL: VARCHAR(N) enforces the length.
   • Parser Implication: Accept VARCHAR(N) but treat it as VARCHAR.

3. Integer Division:

   • DuckDB: / performs float division (e.g., 5 / 2 = 2.5). // performs integer division (e.g., 5 // 2 = 2).
   • PostgreSQL: / on integers performs integer division.
   • Parser Implication: Distinguish / and //.

4. UNION of BOOLEAN and INTEGER:

   • DuckDB: Allows this, casting boolean to integer (e.g., TRUE to 1).
   • PostgreSQL: Errors.
   • Parser Implication: DuckDB's type resolution for UNION is more flexible.

5. VACUUM:

   • DuckDB: VACUUM ANALYZE recomputes statistics. Plain VACUUM currently does little. Space reclamation is primarily via CHECKPOINT.
   • PostgreSQL: VACUUM reclaims space and VACUUM ANALYZE also updates statistics.
   • Parser Implication: Accept the syntax, but understand semantic differences.

6. CREATE OR REPLACE TABLE:

   • DuckDB: Supported convenience.
   • PostgreSQL: Requires DROP TABLE IF EXISTS ...; CREATE TABLE ...;.
   • Parser Implication: Support this common DDL variant.

7. COPY Command:

   • DuckDB: Supports FORMAT PARQUET, FORMAT JSON. Auto-detection is more prevalent.
   • PostgreSQL: More traditional CSV/binary focus.
   • Parser Implication: DuckDB's COPY options are richer for modern formats.

8. Data Types:

   • DuckDB ARRAY vs. PostgreSQL ARRAY:
     • DuckDB ARRAY: Fixed-size, e.g., INTEGER[3]. Created with array_value().
     • DuckDB LIST: Variable-size, e.g., INTEGER[]. Created with [] or list_value(). This is closer to PostgreSQL's ARRAY.
     • PostgreSQL ARRAY: Variable-size.
   • DuckDB TIMESTAMPTZ: Stored as UTC. Display and operations depend on session TimeZone setting (ICU extension needed for full time zone name support).
   • DuckDB HUGEINT, UHUGEINT: 128-bit integers.
   • Parser Implication: Be precise with array/list syntax and understand type mapping.

9. Functions:

   • DuckDB to_date (deprecated/removed in favor of strptime) vs. PostgreSQL to_date: PostgreSQL's to_date has specific formatting. DuckDB uses strptime for string-to-date/timestamp conversion.
   • current_date, current_time, current_timestamp: DuckDB returns these in UTC for naive types, and in session time zone for TIMESTAMPTZ. PostgreSQL's behavior can depend more on session settings for naive types.
   • Parser Implication: Function mapping and argument handling will be a significant part of porting.

10. Friendly SQL Features (Mostly DuckDB specific):

    • SELECT * EXCLUDE/REPLACE
    • COLUMNS(...) expression
    • GROUP BY ALL, ORDER BY ALL
    • FROM-first syntax
    • PIVOT/UNPIVOT (SQL Standard syntax also supported, but DuckDB simplified syntax is common)
    • UNION BY NAME
    • ASOF JOIN, POSITIONAL JOIN
    • Parser Implication: These are new syntactic constructs or extensions to existing ones.

11. Indexing and Constraints:

    • DuckDB: ART indexes for PRIMARY KEY/UNIQUE. [Zonemaps automatically created]({% link docs/1.1/guides/performance/indexing.md %}#zonemaps) for all columns. CREATE INDEX creates ART indexes.
    • PostgreSQL: More varied index types (B-Tree, GIN, GiST, etc.).
    • Parser Implication: Syntax for CREATE INDEX might differ for specific index types if those were ever to be supported beyond ART by DuckDB.

12. NULL Ordering:

    • DuckDB: Default is NULLS LAST (like PostgreSQL). Configurable via SET default_null_order.
    • PostgreSQL: Default is NULLS LAST for ASC and NULLS FIRST for DESC.
    • Parser Implication: NULLS FIRST/NULLS LAST in ORDER BY clauses should be parsed.

13. Macros vs. SQL Functions:

    • DuckDB: Supports CREATE MACRO for SQL-level "functions" that are expanded textually.
    • PostgreSQL: Primarily uses CREATE FUNCTION with PL/pgSQL, C, Python, etc. (DuckDB also supports UDFs in Python/C++/etc. via extensions, but SQL macros are simpler).
    • Parser Implication: Recognize CREATE MACRO syntax.

14. ATTACH Database:

    • DuckDB: Allows attaching other DuckDB files, or even SQLite/Postgres/MySQL databases (via extensions), making them queryable in the same session.
    • PostgreSQL: Uses Foreign Data Wrappers (FDWs) for similar cross-database access, which is a different mechanism.
    • Parser Implication: ATTACH is a top-level command.

6. Advanced/Unique DuckDB Features for Parser Awareness

• PIVOT/UNPIVOT Statements:
  • DuckDB supports a simplified PIVOT table ON cols USING aggs GROUP BY groups syntax.
  • And SQL Standard PIVOT (aggs FOR cols IN (values)).
  • UNPIVOT has similar dual syntax.
  • Parser Implication: These are top-level query structures, not just functions.
• QUALIFY Clause: Filters results of window functions without a subquery.

  SELECT *, row_number() OVER (ORDER BY col) AS rn FROM tbl QUALIFY rn <= 5;

• USING SAMPLE / TABLESAMPLE:

  SELECT * FROM tbl USING SAMPLE 10%;
  SELECT * FROM tbl TABLESAMPLE 10%;

  Supports SYSTEM, BERNOULLI, RESERVOIR methods. Row or percentage based. REPEATABLE(seed) for deterministic sampling (conditionally).
• CREATE SECRET / DROP SECRET: Manages credentials for external services (S3, GCS, Azure, etc.).
• EXPORT DATABASE / IMPORT DATABASE: Serializes/deserializes an entire database to/from a directory.
• List/Struct Creation and Access:
  • List creation: [1, 2, 3] or list_value(1,2,3)
  • Struct creation: {'key': 'val', 'num': 1} or struct_pack(key := 'val', ...) or ROW(val1, val2)
  • Access: my_list[1], my_struct.key, my_struct['key'].
  • Slicing: my_list[1:3].
• Lambda Functions: Used with list functions, e.g., list_transform([1,2,3], x -> x * 2). Syntax: (params) -> expression or param -> expression.
• query_table('table_name_as_string'): Allows table name to be a dynamic string, useful in macros or with prepared statement parameters.

  PREPARE s1 AS SELECT * FROM query_table($1);
  EXECUTE s1('my_actual_table');

• Window Clause (WINDOW w AS ...): Defines named windows for reuse in OVER clauses.

7. Tips for Parser Development/Porting

1. Leverage DuckDB's Parser: For complex parsing logic or to ensure full compatibility with DuckDB's evolving syntax, consider shelling out to DuckDB itself to parse or validate queries (e.g., using EXPLAIN or duckdb_query_parse from C API if embedding, or using the Python client's extract_statements).
2. Focus on read_* functions: These are the primary way DuckDB ingests external data files and they have many parameters. COPY FROM is also important but perhaps secondary for direct file interaction.
3. PostgreSQL as a Baseline: Start with PostgreSQL syntax rules, then layer DuckDB's extensions and deviations on top.
4. Case Handling: Decide how your parser will handle identifier casing internally. DuckDB's default (preserve_identifier_case = true) means your parser might need to store original casing while performing case-insensitive lookups.
5. Type System: Pay close attention to DuckDB's specific nested types (LIST vs. ARRAY) and literal type inference.
6. Friendly SQL: Many of DuckDB's "friendly" SQL features simplify queries but add complexity for a strict parser. Decide which ones are critical to support.
7. Test Extensively: Use DuckDB's own test suites (sqllogictests) as a reference for valid syntax and behavior.
8. Stay Updated: DuckDB is rapidly evolving. New syntax and features are added regularly. Plan for maintainability.
9. Error Reporting: DuckDB's error messages for syntax issues are generally quite good and can be a valuable resource during development.

This document should provide a solid foundation for understanding DuckDB's SQL dialect with a focus on parser implementation. For the most precise and up-to-date details, always refer to the official DuckDB documentation and source code.

Okay, this is a comprehensive guide! It provides a lot of detail that will be invaluable for creating a robust DuckDB parser and accurate lineage tool.

Based on this new information, here's an updated and more detailed plan to expand the node-sql-parser repository:

Phase 1: Core DuckDB Parser Implementation (duckdb.pegjs and AST adjustments)

1. Create pegjs/duckdb.pegjs:
   • Base: Start by copying pegjs/postgresql.pegjs. This is a good starting point due to DuckDB's high compatibility.
   • Identifier Handling:
     • The PEG.js grammar typically defines identifiers broadly (e.g., ident_name = start_char subsequent_char*). This usually captures identifiers as they are.
     • Keywords are made case-insensitive (e.g., 'SELECT'i).
     • Action: No immediate change to the core identifier parsing rule might be needed in PEG.js itself for case preservation during parsing (it captures what's there). The sqlify part of node-sql-parser will need to be made aware of preserve_identifier_case if it aims to reproduce original casing exactly. For lineage, case-insensitive matching during AST processing is key.
     • Quoted Identifiers: Ensure rules for quoted identifiers ("My Table") correctly capture the content. The note about DuckDB also treating quoted identifiers case-insensitively for matching is an internal DuckDB behavior; the parser should still treat them as distinct if the database were, for example, PostgreSQL. For lineage, we'd likely normalize.
   • DDL Statements:
     • CREATE TABLE:
       • Add OR REPLACE option. Modify the create_table_stmt rule.
       • Add IF NOT EXISTS (likely already there from PostgreSQL).
       • Support CREATE TABLE ... AS FROM func_call specifically for DuckDB's CTAS from table functions like read_csv().

         // In create_table_stmt rule alternatives
         / a:KW_CREATE __ or:(KW_OR __ KW_REPLACE)? __ KW_TABLE __ t:table_name __ KW_AS __ KW_FROM __ fc:func_call { /* AST node for CTAS from function */ }

       • Generated Columns: Add syntax GENERATED ALWAYS AS (expression) [VIRTUAL | STORED] to column_definition_opt.
     • ALTER TABLE: Review existing PostgreSQL ALTER TABLE rules and add/modify for DuckDB specifics if major differences are found (e.g., RENAME COLUMN old TO new). The list in the doc is a good checklist.
     • CREATE SCHEMA: Add IF NOT EXISTS.
     • CREATE [OR REPLACE] [TEMP] VIEW ...: Ensure all options are covered.
     • CREATE [UNIQUE] INDEX ...: Likely similar to PostgreSQL.
     • CREATE [OR REPLACE] [TEMP] SEQUENCE [IF NOT EXISTS] ...: Ensure all sequence options are covered.
     • CREATE TYPE: Add support for AS ENUM (...), AS (field type, ...) (STRUCT), AS UNION (...), and type aliases AS existing_type. This will require new rules for these type definitions.
     • CREATE MACRO: Add new rules for CREATE [OR REPLACE] [TEMP] MACRO [IF NOT EXISTS] name (params) AS expression and AS TABLE query. This is a significant new DDL type.
   • DML Statements:
     • INSERT INTO:
       • Add BY NAME option.
       • Add OR IGNORE and OR REPLACE prefixes.
       • Ensure ON CONFLICT (target) DO UPDATE SET ... and ON CONFLICT (target) DO NOTHING are fully supported (PostgreSQL base should be close).
       • RETURNING clause (likely present).
     • UPDATE: Add FROM from_list and RETURNING.
     • DELETE FROM: Add USING from_list and RETURNING.
     • COPY: Review existing COPY rules from PostgreSQL. Add DuckDB specific options like FORMAT PARQUET, FORMAT JSON, AUTO_DETECT TRUE, FILENAME, HIVE_PARTITIONING, UNION_BY_NAME, COMPRESSION.
   • DQL (SELECT) Statement - "Friendly SQL":
     • FROM-First Syntax: This is a major structural addition.

       start_query = from_first_select_stmt / select_stmt / other_stmt_types;
       
       from_first_select_stmt
         = KW_FROM __ from_clause (__ select_core_clauses)? { /* AST node combining from and optional select parts */ }
       
       select_core_clauses // a rule capturing SELECT [DISTINCT] cols WHERE ... GROUP BY ... etc.
         = KW_SELECT __ distinct_opt? __ column_clause ... ;

     • SELECT * EXCLUDE / REPLACE: Modify star_expr or column_clause.

       star_clause = tbl:(ident __ DOT)? STAR __ exclude_replace_opt:exclude_replace_clause? { /* ... */ }
       exclude_replace_clause = kw:(KW_EXCLUDE / KW_REPLACE) __ LPAREN __ items:expr_list RPAREN { /* ... */ }

     • COLUMNS(...) Expression: Add a new alternative to primary_expression or column_list_item.

       columns_func_expr
         = KW_COLUMNS __ LPAREN args:(
             literal_string  // regex
           / (LBRACKET __ column_name_list __ RBRACKET) // list of names
           / lambda_expression_for_columns // ident __ '->' __ expr
         ) RPAREN { /* ... */ }

     • GROUP BY ALL / ORDER BY ALL: Add ALL as an alternative to expr_list in group_by_clause and order_by_clause.
     • QUALIFY Clause: Add this clause to the select_stmt_nake rule structure, typically after HAVING and before WINDOW or ORDER BY.
     • USING SAMPLE / TABLESAMPLE:
       • TABLESAMPLE can be part of table_base: t:table_name __ ts:tablesample_clause?.
       • USING SAMPLE is a clause at the SELECT statement level.

       tablesample_clause = KW_TABLESAMPLE __ method:(KW_SYSTEM / KW_BERNOULLI / KW_RESERVOIR) __ LPAREN size_expr:expr RPAREN (__ KW_REPEATABLE __ LPAREN seed_expr:expr RPAREN)? { /* ... */ }
       using_sample_clause = KW_USING __ KW_SAMPLE __ size_expr:expr { /* ... */ }

     • UNION BY NAME: Add BY NAME to the set_op rule for UNION.
     • Specialized Joins: Add ASOF JOIN and POSITIONAL JOIN to join_op alternatives. LATERAL keyword parsing (often optional).
   • Other Statements:
     • ATTACH 'database_file' [AS alias] [(TYPE ..., READ_ONLY)]: New DDL.
     • DETACH alias: New DDL.
     • USE database_name[.schema_name]: (Likely already handled if based on MySQL/PostgreSQL).
     • PIVOT/UNPIVOT: These are complex and transform the table source. They would be modifiers on a table_ref or from_clause.

       // In from_clause or table_base modification
       table_source __ pivot_unpivot_clause?
       
       pivot_unpivot_clause
         = KW_PIVOT __ LPAREN aggr_func_list __ KW_FOR __ pivot_column:column_ref __ KW_IN __ LPAREN pivot_values:expr_list RPAREN RPAREN (__ KW_AS alias:ident)?
         / KW_UNPIVOT ...

     • CREATE SECRET / DROP SECRET: New DDL.
   • Operators:
     • Add // for integer division.
   • Data Types in Grammar:
     • Ensure all primitive types from the markdown (e.g., HUGEINT, UTINYINT, TIMESTAMP_NS, TIMESTAMPTZ, BIT, JSON, INET) are recognized by the data_type rule.
     • Nested Types:
       • LIST: element_type LBRAKE RBRAKE or KW_LIST LPAREN element_type RPAREN.
       • ARRAY: element_type LBRAKE length_expr RBRAKE or KW_ARRAY LPAREN element_type COMMA length_expr RPAREN.
       • STRUCT: KW_STRUCT LPAREN field_def_list RPAREN.
       • MAP: KW_MAP LPAREN key_type COMMA value_type RPAREN.
       • UNION (type): KW_UNION LPAREN tagged_type_list RPAREN.
     • User-Defined Types: CREATE TYPE name AS ENUM (...) (and others) will add to a list of recognized types the parser might need to consult for subsequent DDL/DML if type checking is part of its goal (though for AST parsing, just recognizing it as an identifier used as a type is often enough).
   • Literal Types:
     • Dollar-quoting: $$string$$ and $tag$string$tag$.
     • Underscores in numeric literals: 1_000_000.

• Update src/parser.all.js and src/parser.js to include and dispatch to the new duckdb parser.
• Initial sqlify support: Focus on getting the new AST nodes to convert back to some valid DuckDB SQL. Perfect 1:1 reproduction of original formatting can be refined later.

Phase 2: Dynamic Schema Fetching and Lineage Logic for read_csv and similar

1. Install @duckdb/node-api: npm install @duckdb/node-api
2. Create src/duckdbLineage.js (or integrate into Parser class):
   • expandPath helper: Keep as is.
   • reconstructReadFunctionForDuckDB(funcCallAstNode, parserInstance) function:
     • Input: The AST node for read_csv_auto, read_parquet, read_json_auto etc.
     • Output: An object { duckDbQueryFragment: "read_csv_auto('path', opt1=val1)", originalFilePath: "path", expandedFilePath: "/expanded/path" }.
     • Logic:
       • Extract function name (e.g., read_csv_auto).
       • First argument is the path (or list of paths/globs). Handle string literals, expand tilde. If it's a list, convert the list AST to SQL list string.
       • Iterate remaining arguments from funcCallAstNode.args.value. These can be:
         • Named parameters (e.g., header=true): The parser needs to represent these as assign-like structures in the AST (e.g., left: {type:'ident', value:'header'}, right: {type:'boolean', value:true}). Convert these back to name=value SQL.
         • columns or types struct/map: columns={'colA': 'VARCHAR', 'colB': 'INTEGER'}. This is the most complex part. The AST for this needs to be rich.
           • sqlify would need to be able to convert this struct/map AST back into the DuckDB SQL representation for the columns option. Example: COLUMNS = { 'colA': 'VARCHAR', ... }.
         • The parserInstance.exprToSQL() will be vital here for converting argument values.
   • getDuckDBSchema(connection, queryFragment, functionName) function:
     • Takes a DuckDB connection and the reconstructed query fragment (e.g., read_csv_auto('path')).
     • Executes DESCRIBE SELECT * FROM ${queryFragment} or SELECT * FROM ${queryFragment} LIMIT 0.
     • Uses reader.columnNames() and reader.columnTypes() to get schema.
     • Transforms this into a consistent schema object: { columns: [{ name: 'colA', type: 'VARCHAR' }, ...], sourceIdentifier: 'path_or_function_call' }.
     • Handle potential errors from DuckDB (file not found, parse errors in options).
   • findColumnReferencesInExpr helper: Keep as is, but ensure it correctly navigates the AST structure your parser produces for DuckDB.
   • getColumnLineage(sql, dbOptions) function (main logic):
     • Parse SQL to get AST using dbOptions = { database: 'duckdb' }.
     • Iterate ast.from items:
       • Table Function (read_csv, etc.):
         • Call reconstructReadFunctionForDuckDB to get the SQL fragment and file paths.
         • Call getDuckDBSchema to get actual columns.
         • sourceIdentifierForLineage becomes the expandedFilePath or the list/glob string.
       • Regular Table: sourceIdentifierForLineage is db.schema.table. Schema is "unknown" unless a catalog lookup is added (out of scope for now).
       • Subquery: sourceIdentifierForLineage is alias or (SUBQUERY). Recursively call getColumnLineage on fromItem.expr.ast to get its output columns, which become the "source schema" for this level.
       • Join: This requires more complex logic to track which side of the join a column reference originates from. Initially, you might list all joined tables as potential sources for columns not explicitly qualified.
     • Process ast.columns:
       • SELECT *:
         • If sourceSchemaInfo is available (from read_csv or resolved subquery), use its columns.
         • Apply EXCLUDE / REPLACE modifiers from the AST to this column list.
         • Create lineage: each target column maps to itself from the identified source.
       • SELECT COLUMNS(...):
         • If sourceSchemaInfo available, apply the COLUMNS filter/regex/lambda to sourceSchemaInfo.columns to determine the target columns. This is complex, especially for lambdas. For lambdas, might initially mark as "derived from multiple columns of source".
         • Create lineage.
       • Explicit Columns (SELECT expr AS alias, ...):
         • Target column is alias or derived name from expr.
         • Use findColumnReferencesInExpr(col.expr, ...) to find source columns.
         • Map these raw source columns (e.g., colA, t1.colB) to their fully identified sources (/path/to/file.csv.colA, table_name.colB) based on the FROM clause analysis.
         • Create lineage entry.
     • Handle literals/functions not referencing table columns (source is empty).
     • Handle PIVOT/UNPIVOT transformations if encountered in the FROM clause by showing how columns are remapped. This is advanced.

Phase 3: Testing and Refinement

• Unit Tests: Test duckdb.pegjs with a wide variety of DuckDB-specific syntax from the markdown provided (CTAS, COLUMNS(), PIVOT, read_csv with many options, etc.).
• Lineage Tests:
  • Simple SELECT col FROM read_csv(...).
  • SELECT * FROM read_csv(...).
  • SELECT * EXCLUDE (...) FROM read_csv(...).
  • SELECT expr AS alias FROM read_csv(...).
  • SELECT COLUMNS('regex') FROM read_csv(...).
  • Test with different read_csv options affecting schema (e.g., header=false, names=['x','y']).
  • Test with globs/lists of files in read_csv.
• Integration Tests: Test the end-to-end flow of parsing and lineage generation.

Key Considerations from DuckDB Document:

• VARCHAR(N) length ignorance: Parser accepts it, sqlify can omit (N).
• Integer division //: Add to grammar and sqlify.
• Error Handling in DuckDB Client: Wrap DuckDB calls in try...catch.
• Macros: Parsing CREATE MACRO is DDL. Expanding macros during lineage analysis is very advanced and likely out of scope for an initial version.
• query_table('string_name'): For lineage, if the argument to query_table is a literal string, that's the table name. If it's a parameter/variable, static lineage is hard.
• auto_detect=true vs. explicit columns in read_csv: The dynamic schema fetching will be most valuable when auto_detect=true or columns are not fully specified in the query. If columns are specified, those should ideally take precedence as the "view" of the CSV for the query.
• Performance of getDuckDBSchema: This is an external call. For complex scripts with many read_csv calls, this could be a bottleneck. Caching results based on file path and potentially file mtime could be an optimization.

Initial Focus for read_csv('~/Documents/data.csv'):

1. Ensure duckdb.pegjs correctly parses func_call in FROM clause, capturing read_csv and its string literal argument.
2. Implement reconstructReadFunctionForDuckDB to handle the simple string literal path.
3. Implement getDuckDBSchema using SELECT * FROM read_csv_auto('expanded_path') LIMIT 0 and reader.columnNames().
4. Update getColumnLineage to:
   • Identify read_csv in FROM.
   • Call the schema fetching logic.
   • If SELECT *, map all fetched CSV columns to target columns.
   • If SELECT specific_cols, map those specific target columns to the same-named source columns from the CSV.

This step-by-step approach, starting with grammar and then layering on dynamic analysis, should make the task manageable. The provided DuckDB document is an excellent resource to guide the .pegjs file modifications.