TopolearnThese shortcuts remove most of the boilerplate in analytical SQL and are used in nearly all idiomatic DuckDB code.
DuckDB keeps standard SQL working but adds conveniences that make exploratory queries much shorter:
GROUP BY ALLgroups by every non-aggregated column in the SELECT list, so you never repeat column lists.ORDER BY ALLsorts by every column left to right.SELECT * EXCLUDE (a, b)returns all columns except some;SELECT * REPLACE (round(price, 2) AS price)swaps an expression in place of a column.- FROM-first:
FROM tblalone meansSELECT * FROM tbl, andFROM tbl SELECT xis allowed. COLUMNS('regex')orCOLUMNS(* EXCLUDE id)expands to many columns, soSELECT max(COLUMNS(*))aggregates each column.- Column aliases can be reused later in the same SELECT and in WHERE and GROUP BY.
count()without arguments, trailing commas in lists, andLIMIT 10%are accepted.
Other quality-of-life features include UNION BY NAME (match columns by name not position), INSERT INTO t BY NAME, and string slicing like 'hello'[1:3].
These are DuckDB-specific: if a query must also run on PostgreSQL or a warehouse, stick to standard syntax there. For notebooks, pipelines and ad-hoc work, they make SQL far less tedious.
Example
import duckdb
duckdb.sql("""
CREATE TABLE orders AS SELECT * FROM (VALUES
('north', 'pen', 2, 1.5::DOUBLE), ('north', 'book', 1, 12.0),
('south', 'pen', 5, 1.5)) t(region, item, qty, price)
""")
print(duckdb.sql("""
SELECT region, item, sum(qty * price) AS revenue
FROM orders GROUP BY ALL ORDER BY ALL
""").fetchall())
# [('north', 'book', 12.0), ('north', 'pen', 3.0), ('south', 'pen', 7.5)]
print(duckdb.sql("SELECT * EXCLUDE (price) FROM orders LIMIT 1").columns)
# ['region', 'item', 'qty']
print(duckdb.sql("SELECT max(COLUMNS(* EXCLUDE (region, item))) FROM orders").fetchall())
# [(5, 12.0)]Remember
GROUP BY ALL/ORDER BY ALLremove repeated column lists* EXCLUDEand* REPLACEreshape wide tables quicklyCOLUMNS(...)applies one expression to many columns- FROM-first queries like
FROM tare valid
Watch out for
- Copying DuckDB-only syntax into queries that also have to run on another database.