# COLUMNS()

***

In DuckDB, `COLUMNS()` is a "meta-programming" feature that lets you apply logic to multiple columns at once without typing their names. Think of it as Regular Expressions for your SELECT list.

It solves the "wall of text" problem when dealing with wide tables (e.g., datasets with 100+ sensors or financial metrics).

***

### The Basics: Selecting Multiple Columns

Instead of listing every column, you can use a string pattern or a regex to grab what you need.

* By Pattern: `SELECT COLUMNS('sales_.*') FROM table` (Selects all columns starting with "sales\_").
* By Type: `SELECT COLUMNS(c -> c.type = 'INTEGER') FROM table` (Selects only integer columns).

***

### 2. Applying Functions to Everything

The real power of `COLUMNS()` is "projection." You can wrap a function around `COLUMNS()` to apply it to every matching field simultaneously.

The Scenario: You have a table with 50 different price columns and you need to round all of them to 2 decimal places.

The DuckDB way:

```sql
SELECT ROUND(COLUMNS('price_.*'), 2) FROM products;
```

This is equivalent to writing `ROUND(price_usd, 2), ROUND(price_eur, 2)...` for every single match.

***

### Combining with Aggregates

You can use `COLUMNS()` inside aggregate functions to perform bulk analysis.

Example: Get the maximum value of every "sensor" column:

```sql
SELECT MAX(COLUMNS('sensor_\d+')) FROM manufacturing_data;
```

***

### Advanced: Lambda Functions

DuckDB allows you to pass a lambda (a mini-function) into `COLUMNS()` to filter columns based on complex logic.

```sql
-- Select all columns EXCEPT those that contain 'id' or 'key' in the name
SELECT COLUMNS(c -> c NOT LIKE '%id%' AND c NOT LIKE '%key%') FROM users;
```

***

### Why this is a "Killer Feature"

In standard SQL (like Postgres or MySQL), if you want to perform a calculation on 20 columns, you have to write it 20 times or use a Python/dbt script to generate the SQL.

DuckDB’s `COLUMNS()` makes the SQL itself dynamic.

#### Comparison: Standard SQL vs. DuckDB

| **Feature**     | **Standard SQL (Postgres)**    | **DuckDB COLUMNS()**        |
| --------------- | ------------------------------ | --------------------------- |
| Effort          | Manual listing                 | Pattern-based               |
| Maintenance     | High (if table schema changes) | Low (adapts to new columns) |
| Bulk Formatting | Requires CTEs or scripting     | Single-line function        |

***

Wait, there's more: You can actually use `COLUMNS()` inside a `WHERE` clause too. For example: `WHERE COLUMNS('price.*') > 0` would filter for rows where every price column is positive.

***