SQLite vs. PostgreSQL Syntax Differences for Analytical Queries

1. Key Dialect Divergences

• Relative Date Math: SQLite uses date('now', '-7 days'); Postgres uses CURRENT_DATE - INTERVAL '7 days'.
• String Concatenation: SQLite uses ||; Postgres supports CONCAT(a, b) which gracefully handles NULLs.
• JSON Extraction: SQLite uses json_extract(d, '$.k'); Postgres uses d->>'k'.

2. Rolling 30-Day Sum Example

-- SQLite (In-Memory Sandbox)
SELECT strftime('%Y-%m', order_date), SUM(amount)
FROM orders
WHERE order_date >= date('now', '-30 days')
GROUP BY 1;

-- PostgreSQL (Production)
SELECT TO_CHAR(order_date, 'YYYY-MM'), SUM(amount)::numeric
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1;