SQL SELECT * EXCLUDE, REPLACE, and COLUMNS()

SELECT * takes every column. DuckDB adds modifiers that edit that list: * EXCLUDE (a, b) leaves columns out, * REPLACE (expr AS a) swaps one for an expression, and * RENAME (a AS b) renames it. COLUMNS() goes further: it picks columns by name, pattern, or test, and repeats an expression across each one.

select * exclude (campaign_name, objective)
from campaigns
order by campaign_id
limit 3

The portable form names the five columns that are left. These examples use Marketing Campaigns, whose tables are wide enough for the shortcut to pay off.

When to use it

• Exploring a wide table, leaving out a long text column or an internal id.
• Cleaning one column while passing the rest through, with REPLACE.
• The same aggregate over many columns, such as a total for every metric, with COLUMNS().

In a view or a report that other people depend on, list the columns. A star picks up every column added to the table later.

REPLACE and RENAME

REPLACE keeps the column's name and position and changes its value. RENAME keeps the value and changes the name:

select * replace (upper(objective) as objective)
from campaigns
order by campaign_id
limit 2;

select * rename (budget as budget_usd)
from campaigns
order by campaign_id
limit 2;

COLUMNS(): one expression, many columns

COLUMNS() takes a list of names, a regular expression, a lambda that tests each name, or * with its modifiers. An aggregate wrapped around it runs once per matched column:

select ad_group_id,
       count(*) as days,
       sum(columns(['impressions', 'clicks', 'conversions']))::integer
from daily_performance
group by ad_group_id
order by ad_group_id
limit 3

The result has impressions, clicks, and conversions columns, each a sum. The ways to pick:

Count the gaps in every column

count(column) skips NULL, so subtracting it from count(*) for every column profiles a table in one line:

select count(*) - count(columns(*))
from campaigns

Every column comes back 0 except ended_on: 14 of the 62 campaigns haven't ended.

Naming the results

A plain alias gives every matched column the same name: sum(columns([...])) as total returns three columns all called total. Put \0 in a quoted alias to stand for each original name:

select ad_group_id,
       sum(columns(['impressions', 'clicks', 'conversions']))::integer as "total_\0"
from daily_performance
group by ad_group_id
order by ad_group_id
limit 3

That returns total_impressions, total_clicks, and total_conversions.

What can go wrong

• A regex matches anywhere in the name. columns('id') on channels returns channel_id and is_paid. Anchor it: '_id$'.
• Everything around COLUMNS() applies to every column. A ::integer meant for the counts also turns spend of 4175.17 into 4175. Keep money columns in a separate COLUMNS().
• A misspelled name stops the query. * exclude (nope) fails with Column "nope" in EXCLUDE list not found in FROM clause, and REPLACE only takes names that already exist.

select * exclude (nope)
from campaigns

How other databases differ

select * except (budget)
from campaigns