15

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?

1
  • postgresql does not allow a dot operator . to be used in the SET clause. It makes sense because postgresql update only allows update of one table at a time and so set anytablename.anycolumnname throws this error. – Eric Leschinski Jan 26 at 17:28
13

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,

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

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

7
  • Thanks for the detailed answer, the request i've shown is just an example. i am working on automatically (as in writing a tool that does the conversion for me) converting old pervasive requests, some of those looks like my example. thing is, this query is valid in MySQL, so why not in PostgreSQL? Is it a bug in the parser? – justesting Dec 19 '16 at 22:07
  • 1
    @justesting: If you actually look carefully at the syntax... you should have column_name = expression. That is, you do not have a table name together with the column name. So, this doesn't have anything to do with a column called "column" or a table called "table". You can use non-reserved words, and PostgreSQL will still complain. Try: CREATE TABLE normal_table_name AS SELECT x AS c1, x AS c2 FROM generate_series(1, 1000) AS s(x); UPDATE normal_table_name t SET t.c1 = 2 WHERE t.c2 = 123; and you will get ERROR: column "t" of relation "normal_table_name" does not exist. – joanolo Dec 19 '16 at 22:10
  • This is depressing -.- because i'll need to write some more code that will parse those aliases in case it's an update request... Thank you for making my life harder! Maybe i'll ditch PostgreSQL and move to MySQL. – justesting Dec 19 '16 at 22:21
  • 2
    @justesting please. MySQL has much more weirdness in the way it has implenented SQL. – ypercubeᵀᴹ Dec 19 '16 at 22:23
  • @justesting updated^ – Evan Carroll Dec 19 '16 at 23:02
10

It's a Postgres weirdness. As stated in the documentation for UPDATE, table names should not be used for target columns.

column_name

The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid.

Only one table can be updated in an UPDATE clause, so there is no room for misinterpretation of the statement.

7
  • That's true, but i am talking about legacy requests looking like this: update table t1 set t1.column = (select t2.column2 from table2 t2 where t2.column3 = t1.column3). i am in for some pain, and LOTS of it! – justesting Dec 19 '16 at 22:29
  • 1
    Yeah, I understand that when you are trying to convert queries from other DBMS. If you are doing a MySQL -> Postgres migration, you'll have more trouble with other UPDATEs as well. MySQL syntax differs from the standard when the UPDATE has joins between the table to be updated and other ones. It (mysql) also allows an UPDATE to update more than 1 tables. This is not allowed in any other DBMS. Postgres can use modifying CTEs to update multiple tables in 1 statement but the syntax differs wildly from MySQL. – ypercubeᵀᴹ Dec 19 '16 at 22:32
  • 1
    MySQL also allows UPDATE .... ORDER BY some_column; which is non-standard syntax. (those ones you won't have trouble converting to Postgres though, you'll just need to remove the ORDER BY). It also processes UPDATE statement row by row (checking UNIQUE constraints row by row) and no at the end of statements, as all other DBMS do. – ypercubeᵀᴹ Dec 19 '16 at 22:35
  • 1
    justesting No worries, I'm Top-2 users at mysql tag at the site. There is nothing wrong in pointing the differences between 2 products anyway. Postgres modifiable CTEs are a non-standard extension, too. – ypercubeᵀᴹ Dec 19 '16 at 22:44
  • 1
    @justesting I rolled back the previous edit and edited again (mainly the format). You can edit yourself, too, and correct anything you think could improve the question. – ypercubeᵀᴹ Dec 19 '16 at 22:47
-3

While update Query : If you use Allis with Table Name Then in Set data you does not need to give reference through Allis like this

UPDATE data Q SET is_active = FALSE ,is_delete = TRUE WHERE Q.name = 'XYZ';
1
  • You do know that you can remove the aliases altogether in this statement? – Colin 't Hart Jun 11 '20 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.