Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A statement lists the same table, or the same alias, twice

ErrorERROR: table name "orders" specified more than once

Short answer

PostgreSQL found two items with the same name in one statement. In a self-join every copy of the table needs its own alias, and in UPDATE ... FROM or DELETE ... USING the target table must not be listed again unless the second copy has an alias. Give each item its own alias, or move the second use into a subquery.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    The name in the error is the table or alias that appears twice. Find every place it is used in the statement, then read below which kind of statement you have: SELECT with joins, UPDATE with FROM, or DELETE with USING.

    grep -n -i -w "orders" query.sql
  2. 2Match what you see, then jump to its fix

    1. 1The failing statement is a SELECT where the same table appears twice in FROM or JOIN, for example employees joined to employeesA self-join or repeated join has no aliases, or two items share one aliasFix
    2. 2The statement starts with UPDATE orders and has orders again after FROMUPDATE ... FROM lists the table that is being updatedFix
    3. 3The statement starts with DELETE FROM films and has films again after USINGDELETE ... USING lists the table that is being deleted fromFix

What does the error look like?

  • ERROR: table name "orders" specified more than once
  • ERROR: table name "a1" specified more than once

Cause 1 of 3

A self-join or repeated join has no aliases, or two items share one alias

1Check

An alias is what tells two scans of one table apart in a self-join. Without it, both items carry the table's own name. Two different items given the same alias clash in the same way.

SELECT e.name, m.name AS manager
FROM employees JOIN employees ON employees.manager_id = employees.id;

2Fix

Give each occurrence its own alias and refer to the columns through those aliases. Once an alias is set, the rest of the statement must use it and not the table name.

SELECT e.name, m.name AS manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id;

3Verify

The query returns rows instead of the error.

psql -d mydb -f query.sql

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

UPDATE ... FROM lists the table that is being updated

1Check

The table named after UPDATE is already part of the statement. Do not repeat it in the FROM list, unless you really want a self-join, and then the copy must carry an alias.

UPDATE orders
SET status = 'late'
FROM orders JOIN customers ON customers.id = orders.customer_id
WHERE customers.tier = 'gold';

2Fix

Leave the target out of FROM and join only the other tables. If you do need a second copy of the target, give it an alias. A sub-select in WHERE is another valid shape.

UPDATE orders
SET status = 'late'
FROM customers
WHERE customers.id = orders.customer_id
  AND customers.tier = 'gold';

-- the same with a sub-select
UPDATE orders
SET status = 'late'
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'gold');

3Verify

Run it in a transaction first and roll back, to see that it completes without the error.

BEGIN;
UPDATE orders SET status = 'late' FROM customers
  WHERE customers.id = orders.customer_id AND customers.tier = 'gold';
ROLLBACK;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

DELETE ... USING lists the table that is being deleted from

1Check

USING works like FROM in a SELECT. As with UPDATE, the target table must not be repeated there unless a self-join is meant, and then the copy needs an alias.

DELETE FROM films
USING films JOIN producers ON producers.id = films.producer_id
WHERE producers.name = 'foo';

2Fix

Name only the other table in USING, or use the sub-select form, which the manual calls the more standard way.

DELETE FROM films USING producers
  WHERE producer_id = producers.id AND producers.name = 'foo';

-- the same with a sub-select
DELETE FROM films
  WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');

3Verify

Run it in a transaction first and roll back, to see that it completes without the error.

BEGIN;
DELETE FROM films USING producers
  WHERE producer_id = producers.id AND producers.name = 'foo';
ROLLBACK;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Alias every table in a statement that has more than one, and use the alias for every column reference.
  • In UPDATE and DELETE, treat the target table as already present. Add only the other tables that supply values or conditions.
  • Prefer short aliases that cannot repeat, such as e and m for employee and manager, over a single letter reused in two places.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.