Using the EXCLUDE() function in DuckDB

Tired of copying/pasting many column names to select all columns except a handful? The EXCLUDE() function in SQL allows you to exclude specific columns from the result set. Together with COLUMNS() and REPLACE(), DuckDB provides an easy way to specify the data you want returned in the result set.

Select all columns except a fewSQL

// This will select all information about ducks
// except their height and weight
SELECT * EXCLUDE (height, weight) FROM ducks;
Copy code

Select all columns matching a RegexSQL

// Only return the columns matching a regular expression
// In this case, we're storing colors as individual columns
// named color_1, color_2, etc.  We want to return all
// species of ducks along with their specified colors.
SELECT species, COLUMNS('^color_\d+$') FROM ducks;

// alternatively, we could store the colors in a 
// column named colors which is a list type, allowing us
// to have multiple color values for each species.
Copy code

Ryan Boyd

Edited 11/02/24