Why Postgres rejects a table alias in front of the column in UPDATE SET
UPDATE orders AS o SET o.status = 'shipped' WHERE o.id = 7;Short answer
In PostgreSQL the column on the left side of SET must be a bare name. It cannot be prefixed with the table name or the alias, so UPDATE orders AS o SET o.status = 'x' is invalid. Remove the prefix on the left side. The alias is still fine on the right side, in WHERE, and in a FROM list.
Fix it fast
1Find out which cause you have
Look only at the left side of every assignment in SET. If any of them has a dot in it, that is the problem; the right side and the WHERE clause may keep their prefixes.
-- invalid in PostgreSQL: a dot on the left of the equals sign UPDATE orders AS o SET o.status = 'shipped' WHERE o.id = 7;2Match what you see, then jump to its fix
- 1
The statement has `SET alias.column = ...` or `SET table.column = ...`.The target column in SET carries the table name or the aliasFix - 2
The UPDATE names two tables, or uses JOIN right after UPDATE.A join was written MySQL-style, with the second table in the UPDATE lineFix - 3
Prefixed columns inside a parenthesised list: `SET (o.a, o.b) = (SELECT ...)`.Several columns are set from one subqueryFix
- 1
What does the error look like?
UPDATE orders AS o SET o.status = 'shipped' WHERE o.id = 7;UPDATE orders SET orders.status = 'shipped';Statement works in MySQL or SQL Server but fails after moving to PostgreSQL
Cause 1 of 3
The target column in SET carries the table name or the alias
1Check
The UPDATE reference says not to include the table name in the target column, with `UPDATE table_name SET table_name.col = 1` given as the invalid example. It also says an alias completely hides the real table name, so the alias is no exception.
grep -rn -E 'SET +[a-z_]+\.[a-z_]+ *=' db/ migrations/2Fix
Write the bare column name on the left. Keep the alias everywhere else in the statement.
UPDATE orders AS o
SET status = 'shipped'
WHERE o.id = 7;3Verify
The statement runs and reports the number of rows updated.
psql "$DATABASE_URL" -c "UPDATE orders AS o SET status = 'shipped' WHERE o.id = 7;"Confirmed by postgresql.org
Cause 2 of 3
A join was written MySQL-style, with the second table in the UPDATE line
1Check
PostgreSQL takes the extra tables from a separate FROM clause, which uses the same syntax and aliases as SELECT. Only the table after UPDATE is changed.
grep -rn -i -E 'UPDATE .* JOIN ' db/ migrations/2Fix
Move the second table to FROM and put the join condition in WHERE. Do not repeat the target table in FROM unless you want a self-join, in which case give it an alias.
UPDATE orders AS o
SET total = s.amount
FROM shipments AS s
WHERE s.order_id = o.id;3Verify
Check a few rows after the update.
psql "$DATABASE_URL" -c "SELECT id, total FROM orders ORDER BY id LIMIT 5;"Confirmed by postgresql.org
Cause 3 of 3
Several columns are set from one subquery
1Check
The multi-column form takes a list of bare column names on the left, and either a row of expressions or a sub-SELECT on the right.
grep -rn -E 'SET *\([a-z_]+\.' db/ migrations/2Fix
Drop the prefixes from the column list; the sub-SELECT can keep its own aliases.
UPDATE orders AS o
SET (status, total) = (
SELECT s.state, s.amount FROM shipments AS s WHERE s.order_id = o.id
);3Verify
The statement runs and the columns hold the values from the subquery.
psql "$DATABASE_URL" -c "SELECT id, status, total FROM orders ORDER BY id LIMIT 5;"Confirmed by postgresql.org
How do I stop it coming back?
- Run SQL that came from another database through psql early; this is a syntax rule, so it fails at once.
- Use an alias only where a name could be ambiguous, and never on the left side of SET.
- Keep ORM-generated update statements in the query log while migrating, to spot prefixed columns.
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?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
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
An EC2 instance cannot reach its RDS database
Could not open connection to the host, on port <port>: Connect failed
RDS denies all access until a security group allows it.
3 causes, each with a fixDatabases
Spring Boot fails on startup: HikariPool-1 exception during pool initialization
Caused by: ... (the underlying driver exception)
HikariCP opens one connection when the pool starts, and the startup fails when that connection fails.
3 causes, each with a fixDatabases
Your laptop cannot reach an RDS database, and ping does not work either
Ping to the RDS endpoint gets no reply
When a client times out against an RDS endpoint, the network path is blocked, not the database login.
4 causes, each with a fixDatabases
Spring Boot does not create the schema on MySQL or PostgreSQL
No CREATE TABLE statements from Hibernate appear at startup
Spring Boot only defaults ddl-auto to create-drop for an embedded database with no Flyway or Liquibase.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fix