SELECT * EXCLUDE is DuckDB’s projection syntax for returning every column except the named exclusions. It solves a common data-preparation problem: keeping nearly all fields without manually listing a long, fragile column list. section 1.2 section 3.5.1
Use it when a relation contains many useful columns and only a few irrelevant ones. The query stays readable while avoiding columns you do not need, although ordinary SELECT * can become unstable as table definitions change and may access more data than necessary. section 3.5.1
Keep prices, drop the identifier
A prices relation contains id, value, valid_from, and valid_until. Return the three business fields without enumerating them: SELECT * EXCLUDE (id) FROM prices;. You can name multiple columns in the exclusion list. section 3.5.1
SELECT * EXCLUDE (id) FROM prices;
Run the compact projection directly against the prices relation and inspect the resulting columns. section 3.5.1
Useful variations and nearby DuckDB projection features include:
- Use SELECT * REPLACE to retain all columns while substituting a transformed expression, such as a rounded kWh value. section 3.5.1
- Use COLUMNS with a pattern or expression when columns should be selected by name, such as fields beginning with valid. section 3.5.1
