42P09ERRORTier 2 — Caution✅ HIGH confidence

ambiguous alias

Category: Syntax Error or Access Rule ViolationVersions: All Postgres versions

What this means

SQLSTATE 42P09 is raised when a column alias in a SELECT list is used in an ambiguous context — for example, referenced in a HAVING or ORDER BY clause where it could match both the alias and a table column with the same name.

Why it happens

1A SELECT alias name is the same as a column name in the FROM tables, creating ambiguity in ORDER BY or GROUP BY

How to reproduce

Alias conflict with column name.

trigger — this will ERROR

SELECT e.name, COUNT(*) AS name
FROM employees e GROUP BY e.name
ORDER BY name; -- ambiguous: alias or column?

ERROR: column reference "name" is ambiguous

Fix 1: Use a distinct alias name that does not conflict with column names

When an alias conflicts with a column name.

fix

SELECT e.name, COUNT(*) AS count_per_name
FROM employees e GROUP BY e.name
ORDER BY count_per_name;

Why this works

Using a unique alias name removes the ambiguity between the alias and the table column.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 42 — Syntax Error or Access Rule Violation (Postgres-specific)

Confidence assessment

✅ HIGH confidence

Postgres-specific. Stable across versions.