PostgreSQL Duplicate Alias (SQLSTATE 42712)

When PostgreSQL encounters two items in the same query scope that share an alias, it raises:

ERROR:  table name "t" specified more than once

or, depending on the context:

ERROR:  column name "col" specified more than once

The SQLSTATE code is 42712, condition name duplicate_alias, belonging to error class 42 (Syntax Error or Access Rule Violation). PostgreSQL rejects the statement immediately during parsing or analysis — no rows are read or modified.

What This Error Means

SQLSTATE class 42 covers errors where a statement is syntactically valid SQL but violates a naming or access rule that PostgreSQL can detect before execution begins. The duplicate_alias condition (42712) specifically means two range-table entries in the same query level have been assigned the same identifier, making it impossible for the planner or any later reference to resolve which one is intended.

PostgreSQL builds an internal range table for each query level. Every table reference, subquery, CTE, function call in FROM, or JOIN target must have a unique name within that level. If two entries would share the same name — whether explicitly aliased or using the default table name — PostgreSQL raises 42712 before touching any data.

Because the error is raised at parse or analysis time, the transaction is not aborted. The connection remains healthy and subsequent statements can be issued normally. There is no need to rollback on account of this error alone, though if the statement was inside an explicit transaction block, the block is still marked as aborted in the usual way.

Common Causes

1. Joining the same table to itself without aliases — Self-joins require distinct aliases for each instance. Omitting an alias causes both sides to have the same implicit name.

   -- Raises 42712: both sides are named "employee"
   SELECT * FROM employee JOIN employee ON employee.manager_id = employee.id;
   

2. Using the same alias for two different tables or subqueries in a FROM clause — Accidentally reusing a short alias like t or e for two separate sources in the same query level.

   -- Raises 42712
   SELECT * FROM orders AS t JOIN order_items AS t ON t.id = t.order_id;
   

3. Duplicate CTE names in a WITH clause — Each CTE name within a single WITH block must be unique.

   -- Raises 42712
   WITH summary AS (SELECT count(*) FROM orders),
        summary AS (SELECT count(*) FROM customers)
   SELECT * FROM summary;
   

4. Column aliases repeated in the same SELECT list when referenced by a subsequent clause — Less common, but PostgreSQL may raise a variant of this error when a derived column name collides in certain window function or grouping contexts.

How to Fix duplicate_alias

1. Add distinct aliases to self-joins. Each appearance of the table in the FROM/JOIN clause needs a unique alias.

   -- Fixed: use distinct aliases
   SELECT e.name, m.name AS manager_name
   FROM employee AS e
   JOIN employee AS m ON e.manager_id = m.id;
   

2. Rename conflicting table or subquery aliases. Choose unambiguous aliases for every entry in the FROM clause.

   -- Fixed
   SELECT *
   FROM orders AS o
   JOIN order_items AS oi ON o.id = oi.order_id;
   

3. Rename duplicate CTEs. Each CTE in a WITH clause must have a unique name within that clause.

   -- Fixed
   WITH order_summary AS (SELECT count(*) AS cnt FROM orders),
        customer_summary AS (SELECT count(*) AS cnt FROM customers)
   SELECT order_summary.cnt, customer_summary.cnt
   FROM order_summary, customer_summary;
   

4. Check dynamically generated SQL. If queries are assembled programmatically (ORM, query builder, raw string concatenation), duplicate aliases often appear when the same join is added twice by different code paths. Log the final SQL string and inspect it for repeated alias tokens.

Additional Information

• SQLSTATE 42712 belongs to class 42 (Syntax Error or Access Rule Violation), the same class as common errors like 42601 (syntax_error), 42703 (undefined_column), and 42P01 (undefined_table).
• The error has been present since at least PostgreSQL 7.x and its behaviour has not changed across major versions.
• Most PostgreSQL client drivers (libpq, JDBC, psycopg, node-postgres) surface this as a server-side error with SQLSTATE 42712. ORMs like SQLAlchemy, Hibernate, and ActiveRecord do not typically prevent duplicate aliases at the ORM level and will propagate the raw database error.
• Because the error is detected before execution, it carries no performance cost beyond the parse phase and has no impact on connection state beyond the standard aborted-transaction semantics.

Frequently Asked Questions

Why does PostgreSQL reject the query instead of just picking one alias? SQL requires unambiguous name resolution. If two sources share the same alias, any reference to that alias — in WHERE, ON, SELECT, or ORDER BY — would be undefined behaviour. PostgreSQL rejects the statement to enforce correctness rather than silently picking one side.

Does this error abort my transaction? The statement is rejected, and if you are inside an explicit BEGIN/COMMIT block, that block is marked as aborted (you must ROLLBACK before issuing new statements). However, the error itself does not affect the database connection or any data — it is purely a query-authoring mistake caught at analysis time.

Can a CTE and a regular table reference share the same name? No. Within a given query level, a CTE name and any FROM-clause alias occupy the same namespace. If a CTE is named orders and you also reference a table called orders without an alias in the same query level, PostgreSQL will raise 42712. Use an explicit alias on the table reference to disambiguate.

My ORM generated this query — how do I find the duplicate alias? Enable query logging (log_min_duration_statement = 0 in postgresql.conf, or set it for the session with SET log_min_duration_statement = 0;) and inspect the logged SQL. Search the output for repeated words immediately following AS, JOIN, or at the start of CTE definitions. Alternatively, print or log the raw SQL string in your application layer before it is sent to the database.