Like all programming languages and libraries, DuckDB has its share of idiosyncrasies and inconsistencies.
Some are vestiges of our feathered friend's evolution; others are inevitable because we strive to adhere to the SQL Standard and specifically to PostgreSQL's dialect (see the “PostgreSQL Compatibility” page for exceptions). The rest may simply come down to different preferences, or we may even agree on what should be done but just haven’t gotten around to it yet.

Acknowledging these quirks is the best we can do, which is why we have compiled below a list of examples.

Aggregating Empty Groups

On empty groups, the aggregate functions sum, list, and string_agg all return NULL instead of 0, [] and '', respectively. This is dictated by the SQL Standard and obeyed by all SQL implementations we know. This behavior is inherited by the list aggregate list_sum, but not by the DuckDB original list_dot_product which returns 0 on empty lists.

Indexing

To comply with standard SQL, one-based indexing is used almost everywhere, e.g., array and string indexing and slicing, and window functions (row_number, rank, dense_rank). However, similarly to PostgreSQL, JSON features use a zero-based indexing.

While list functions use a 1-based indexing, list_reduce uses a 0-based indexing. This is a known issue.

Expressions

Results That May Surprise You

NaN Values

'NaN'::FLOAT = 'NaN'::FLOAT and 'NaN'::FLOAT > 3 violate IEEE-754 but mean floating point data types have a total order, like all other data types (beware the consequences for greatest / least).

age Function

age(x) is current_date - x instead of current_timestamp - x. Another quirk inherited from PostgreSQL.

Extract Functions

list_extract / map_extract return NULL on non-existing keys. struct_extract throws an error because keys of structs are like columns.

Clauses

Automatic Column Deduplication in SELECT

Column names are deduplicated with the first occurrence shadowing the others:

CREATE TABLE tbl AS SELECT 1 AS a;
SELECT a FROM (SELECT *, 2 AS a FROM tbl);

Case Insensitivity for SELECTing Columns

Due to case-insensitivity, it's not possible to use SELECT a FROM 'file.parquet' when a column called A appears before the desired column a in file.parquet.

USING SAMPLE

The USING SAMPLE clause is syntactically placed after the WHERE and GROUP BY clauses (same as the LIMIT clause) but is semantically applied before both (unlike the LIMIT clause).