/duckdb-sql

name: duckdb-sql
description: "Load when hitting DuckDB syntax errors or writing DuckDB-specific SQL. Covers gotchas that differ from PostgreSQL/MySQL."
type: skill

DuckDB SQL — Key Differences from PostgreSQL/MySQL

Gotchas

• **Integer division truncates**: `5/2 = 2`. Fix: `CAST(numerator AS DOUBLE) / denominator`
• **DATE_TRUNC returns TIMESTAMP**: Cast result if DATE needed: `CAST(DATE_TRUNC('month', col) AS DATE)`
• **INTERVAL syntax**: `INTERVAL '1' DAY` (quoted), NOT `INTERVAL 1 DAY`
• **No DATEADD/DATEDIFF**: Use `col + INTERVAL '1' DAY` and `DATE_DIFF('day', start, end)`
• **SUM(NULL) = NULL**: Not 0. Use `COALESCE(SUM(col), 0)` if 0 is needed.
• **ROUND precision**: If the YML specifies a decimal type like `decimal(6,2)`,

cast the FINAL output to match: `CAST(ROUND(AVG(col), 2) AS DECIMAL(6,2))`. Do NOT cast the input — cast the result after rounding.

• **Avoid CURRENT_DATE** in models with historical data — use `(SELECT MAX(date_col) FROM source)` to anchor to the data's actual date range

Date Parsing

• Non-ISO strings: `STRPTIME(col, '%d/%m/%Y')::DATE`
• `TRY_STRPTIME` returns NULL on failure (safe)
• Never `CAST(date_str AS DATE)` on non-ISO strings

QUALIFY Clause

Filter window function results without a subquery:

SELECT *, ROW_NUMBER() OVER (PARTITION BY group ORDER BY col DESC) AS rn
FROM table
QUALIFY rn <= 10

Date Spines

SELECT UNNEST(GENERATE_SERIES(min_date::DATE, max_date::DATE, INTERVAL '1' DAY)) AS date_day

Always use the primary fact table's max date as endpoint — call `get_date_boundaries` first.

Type Casting

`CAST(x AS INTEGER)`, `CAST(x AS DOUBLE)`, `CAST(x AS VARCHAR)`, `CAST(x AS DATE)` `TRY_CAST` returns NULL on failure.