PostgreSQL vs. DuckDB
Syntax & Function Guide
KaizenCodes uses DuckDB as its execution engine. DuckDB uses a SQL parser derived from PostgreSQL, meaning ~95% of standard PostgreSQL syntax works perfectly. However, there are a few key dialect differences you might encounter.
⚙️ Common Function Mismatches
| Concept / Operation | 🐘 PostgreSQL / SQL Server | 🦆 DuckDB Standard |
|---|---|---|
| Null Coalescing | IFNULL(a, b)NVL(a, b) | COALESCE(a, b) |
| Date Addition | DATEADD(day, 7, col) | col + INTERVAL 7 DAYcol + INTERVAL '7 days' |
| Date Difference | DATEDIFF(day, a, b) | date_diff('day', a, b)Or simply: b - a |
| Date Truncation | DATE_TRUNC('month', col) | DATE_TRUNC('month', col)Same as Postgres! But DuckDB enforces strict types (ensure `col` is TIMESTAMP). |
| String Formatting | TO_CHAR(col, 'YYYY-MM') | strftime(col, '%Y-%m') |
🔬 Strict Type Signatures (Binder Errors)
PostgreSQL is famous for its "implicit casting." For example, if you pass a VARCHAR string to a date function, PostgreSQL will automatically convert it to a TIMESTAMP under the hood.
DuckDB is strictly typed. If a function expects a date, you must give it a date. If you see an error like:
The Fix: Explicitly cast your column before passing it to the function:
-- Instead of this:
SELECT strftime(date_string_col, '%Y-%m');
-- Do this:
SELECT strftime(CAST(date_string_col AS TIMESTAMP), '%Y-%m');
📝 Column Identifier Casing
PostgreSQL automatically folds all unquoted identifiers to lowercase. Writing SELECT col AS MONTH returns a column named month.
DuckDB preserves the casing you type exactly. SELECT col AS MONTH returns MONTH. KaizenCodes uses strict schema contracts, so if a question asks for a column named month, you must alias it in lowercase!
The Ultimate SQL Patterns Cheatsheet
Master execution order, window frames, gaps & islands, sessionization, and recursive CTEs.