Duckdb sql

Load when hitting DuckDB syntax errors or writing DuckDB-specific SQL. Covers gotchas that differ from PostgreSQL/MySQL.From its SKILL.md

Install

npx -y skills add SignalPilot-Labs/SignalPilot --skill duckdb-sql

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

SKILL.md

1.9 KB, as published. Nobody here has run it

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: In standalone queries, use col + INTERVAL '1' DAY and DATE_DIFF('day', start, end) - DuckDB has no DATEADD/DATEDIFF function.
SUM(NULL) = NULL: Not 0. Use COALESCE(SUM(col), 0) if 0 is needed.
inf/NaN checks: use isinf(col) and isnan(col), NOT IS_INF/IS_NAN (those are BigQuery function names).
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 for date spines - when generating or capping a calendar over historical data, use (SELECT MAX(date_col) FROM source) to anchor to the data's actual date range

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

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.

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