DuckDB: Reference a SELECT Alias Inside CASE in the Same Query

On DuckDB 1.0.0, you can reference an alias declared in the same SELECT list directly inside a CASE expression — and the query runs without error. On Postgres and other strictly standard-SQL databases, the same query throws column "revenue" does not exist.

This is a DuckDB relaxation (sometimes called a lateral column alias), not the SQL standard. This guide shows the working query, the exact error people hit when they migrate to DuckDB expecting it to fail, and the portable rewrites that run on every engine.

Where this question comes from

People migrating from Postgres or MySQL to DuckDB often hit this confusion. The related searches behind this page include:

• duckdb alias column name for use in case expression
• duckdb binder error column does not exist case
• duckdb alias not working in case when
• duckdb case when use computed column
• duckdb column alias not found in select
• duckdb cte vs alias in case

The usual scenario: someone writes CASE WHEN alias > 100 in DuckDB and expects a Binder Error: column "xxx" does not exist, assuming their SQL is wrong. Or they saw that error on another engine and are unsure whether a same-level alias is allowed at all. The answer depends entirely on the engine — and DuckDB is more permissive here than Postgres.

Quick answer

On DuckDB 1.0.0, the following SQL runs and returns results without error:

SELECT
  total_amount AS revenue,
  CASE WHEN revenue > 100 THEN 'high' ELSE 'low' END AS level
FROM orders;

On Postgres, or any strictly standard-SQL database, the same query throws:

ERROR: column "revenue" does not exist

Test data

Create a temp table (dropped at session end, safe to re-run):

CREATE TEMP TABLE orders AS
SELECT * FROM (VALUES
    (1, 'Alice',  150.0, DATE '2026-09-01'),
    (2, 'Bob',     80.0, DATE '2026-09-02'),
    (3, 'Carol',  220.5, DATE '2026-09-03'),
    (4, 'Dave',    45.0, DATE '2026-09-04')
) AS t(order_id, customer, total_amount, order_date);

Verified output (DuckDB 1.0.0)

Running the CASE query above returns:

No error — the alias revenue is usable directly inside the same-level CASE.

Why DuckDB allows this (and Postgres doesn't)

In the SQL standard, the SELECT list is evaluated as a set of independent expressions. An alias is only a label for the output column, so it "does not yet exist" while other expressions in the same list are being evaluated. That is why Postgres rejects CASE WHEN revenue > 100 and asks you to repeat the expression or move it to an outer query.

DuckDB relaxes this: it resolves references to earlier aliases in the same SELECT list, so you can reuse a computed column without repeating it. It is convenient, but it is DuckDB-specific behavior rather than standard SQL.

Gotchas

1. This is a DuckDB relaxation, not the SQL standard. In standard SQL and Postgres, a SELECT-list alias does not yet exist for other expressions in the same SELECT, so referencing it throws column "revenue" does not exist.
2. Don't rely on it for cross-database SQL. If the SQL later moves to Postgres, MySQL, or a warehouse, same-level alias references will error. Use a repeated expression or a subquery/CTE wrapper instead.
3. Prefer the portable form when in doubt. The rewrites below behave identically on DuckDB and on every other engine.

Portable alternatives (work everywhere)

Repeat the expression directly — no alias reference needed:

-- Repeat the expression
SELECT
  total_amount AS revenue,
  CASE WHEN total_amount > 100 THEN 'high' ELSE 'low' END AS level
FROM orders;

Or wrap it in a subquery so the alias is referenced from an outer level:

-- Subquery wrapper, use alias in outer level
SELECT
  revenue,
  CASE WHEN revenue > 100 THEN 'high' ELSE 'low' END AS level
FROM (SELECT total_amount AS revenue FROM orders);

A CTE works the same way and is often easier to read when the expression is complex:

-- CTE wrapper
WITH base AS (
  SELECT total_amount AS revenue FROM orders
)
SELECT
  revenue,
  CASE WHEN revenue > 100 THEN 'high' ELSE 'low' END AS level
FROM base;

Both the subquery and CTE forms are standard SQL: they run on DuckDB, Postgres, MySQL, SQLite, SQL Server, and Oracle.

Frequently Asked Questions