Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Why Postgres rejects a table alias in front of the column in UPDATE SET

ErrorUPDATE 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.

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

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The statement has `SET alias.column = ...` or `SET table.column = ...`.The target column in SET carries the table name or the aliasFix
    2. 2The 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. 3Prefixed columns inside a parenthesised list: `SET (o.a, o.b) = (SELECT ...)`.Several columns are set from one subqueryFix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

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