A statement lists the same table, or the same alias, twice
ERROR: table name "orders" specified more than onceShort 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.
Fix it fast
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.sql2Match what you see, then jump to its fix
- 1
The 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
The statement starts with UPDATE orders and has orders again after FROMUPDATE ... FROM lists the table that is being updatedFix - 3
The statement starts with DELETE FROM films and has films again after USINGDELETE ... USING lists the table that is being deleted fromFix
- 1
What does the error look like?
ERROR: table name "orders" specified more than onceERROR: 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.sqlConfirmed by postgresql.org
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix