DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 42712 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42712: duplicate alias

PostgreSQL error code 42712 occurs when the same alias name is assigned to more than one table, subquery, or CTE within the same query scope. Because PostgreSQL resolves table references by alias during the parsing phase, it cannot determine which source to use when two aliases collide, so it immediately raises this error before any data is touched.

Top 3 Causes

1. Duplicate Table Alias in FROM / JOIN Clause

The most common cause is accidentally reusing the same alias for two different tables in a single query block.

-- ❌ Error: both tables share alias 't'
SELECT t.name, t.amount
FROM customers t
JOIN orders t ON t.customer_id = t.id;
-- ERROR:  table name "t" specified more than once

-- ✅ Fix: use distinct aliases
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;
Enter fullscreen mode Exit fullscreen mode

Even in a self-join you must use two different aliases:

-- ✅ Self-join with unique aliases
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;
Enter fullscreen mode Exit fullscreen mode

2. Duplicate CTE Name in a WITH Clause

Declaring two CTEs with the same name inside a single WITH block triggers error 42712.

-- ❌ Error: 'report' defined twice
WITH report AS (
    SELECT * FROM sales WHERE year = 2023
),
report AS (                          -- 42712 here
    SELECT * FROM sales WHERE year = 2024
)
SELECT * FROM report;

-- ✅ Fix: give each CTE a unique name
WITH report_2023 AS (
    SELECT * FROM sales WHERE year = 2023
),
report_2024 AS (
    SELECT * FROM sales WHERE year = 2024
)
SELECT
    a.total AS total_2023,
    b.total AS total_2024
FROM
    (SELECT SUM(amount) AS total FROM report_2023) a,
    (SELECT SUM(amount) AS total FROM report_2024) b;
Enter fullscreen mode Exit fullscreen mode

3. Duplicate Alias on Derived Tables (Subqueries)

When multiple inline subqueries in the FROM clause share the same alias, PostgreSQL cannot distinguish between them.

-- ❌ Error: both subqueries use alias 'sub'
SELECT sub.total, sub.cnt
FROM (SELECT SUM(amount) AS total FROM orders WHERE status = 'done') sub,
     (SELECT COUNT(*)    AS cnt   FROM orders WHERE status = 'pending') sub;
-- ERROR:  table name "sub" specified more than once

-- ✅ Fix: unique alias per subquery
SELECT done.total, pending.cnt
FROM (SELECT SUM(amount) AS total FROM orders WHERE status = 'done')    done,
     (SELECT COUNT(*)    AS cnt   FROM orders WHERE status = 'pending') pending;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Scan every alias in your query — a quick text search for repeated words after AS or after a table name catches most mistakes instantly.
  2. Replace duplicate aliases with descriptive, role-based names (c → cust, ord, mgr, etc.).
  3. Prefer CTEs over nested subqueries for readability; list all CTE names in a comment at the top of the query so duplicates are immediately obvious.

Prevention Tips

Adopt a consistent alias naming convention. Use the first two or three letters of the table name, and append a number when a collision is unavoidable (emp1, emp2 for self-joins). Document this convention in your team's SQL style guide and enforce it during code review.

-- Convention example
SELECT cu.name, ord.amount, prd.name AS product
FROM customers  cu
JOIN orders     ord ON ord.customer_id = cu.id
JOIN products   prd ON prd.id = ord.product_id;
Enter fullscreen mode Exit fullscreen mode

Integrate a SQL linter into your CI pipeline. Tools like sqlfluff or pgFormatter detect duplicate aliases statically before the query ever reaches the database, eliminating the error in production entirely.

Related Errors

Code Name Brief Description
42701 duplicate_column Same column name used twice in a result set or INSERT target list
42P01 undefined_table References a table or alias that does not exist — often a follow-on error after fixing 42712
42703 undefined_column Column reference cannot be resolved, sometimes triggered after renaming aliases

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)