EC
42P09
PostgreSQLERRORNotableSyntax Error or Access Rule ViolationHIGH confidence

ambiguous alias

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
  1. 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
trigger — this will error
SELECT e.name, COUNT(*) AS name
FROM employees e GROUP BY e.name
ORDER BY name; -- ambiguous: alias or column?

expected output

ERROR:  column reference "name" is ambiguous

Fix

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

WHEN When an alias conflicts with a column name.

Use a distinct alias name that does not conflict with column names
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 documentation ↗

Class 42 — Syntax Error or Access Rule Violation (Postgres-specific)

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors