Update request using table alias

Executing this request:

update table t1 set t1.column = 0 where t1.column2 = 1234

Getting this error:


column "t1" of relation "table" does not exist

This request runs fine in MySQL.

Why do I get this error in PostgreSQL?

I'm not sure if that's your desired syntax or not. Check your syntax for UPDATE

Currently, that's

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

So if you provide table t1, it's getting parsed as a table named table. Actually, to do that you need to have it in quotes "table" t1 which you're doing, or your library is doing.

• As a design note, don't do that. In fact, don't name anything any SQL keywords.

• But, if you want to have some fun and see what's happening we can play..

Let's create some text data,

CREATE TABLE "table" AS
SELECT x AS column, x AS column2
FROM generate_series(1,12345) AS t(x);

Now we can try your original query and get your original result,

UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;

And that's the problem you're getting. As with the table, if you're going to use a SQL keyword, you need to quote it. Interestingly, that's not enough here.

UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;
ERROR:  column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;

In addition to that, it seems that table aliasing is not supported in the SET list, regardless of whether or not the column is reserved keyword.

UPDATE "table" t1 SET "column"=0 WHERE t1.column2=1234;

Why it's currently Working As Designed

Why you can not use aliases, xocolatl from IRC helps with that,


EvanCarroll: the reason you can't use the alias on the left of the = is because of composite types


EvanCarroll: so, it's not a bug but WAD

So in code to CREATE a table with a custom composite type an execute an UPDATE on it.

CREATE TYPE foo AS ( x int, y int );

CREATE TABLE foobar AS
  SELECT v::foo AS mycol
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE foobar SET mycol.x = 9;

So the syntax that permits the . is mycol.type-address, not tablealias.col-name.

Solving the ambiguous syntax problem

If that didn't make sense, any behavior but this behavior would give you an ambiguous syntax,

CREATE TYPE foo AS ( mycol int, x int );

CREATE TABLE mytable AS
  SELECT v::foo AS mycol, 1 AS x
  FROM ( VALUES (1,2), (2,100) ) AS v;

UPDATE mytable AS mycol SET mycol.x = 9;

What does mycol.x refer to there? As is it's not ambiguous, table-referencing and table-aliasing is disabled, so it's definintely 100% of the time a composite-type named mycol, on the table mytable.

StackExchange Database Administrators Q#158617, answer score: 17