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?
postgresqlupdate
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?
Best Answer
I'm not sure if that's your desired syntax or not. Check your syntax for
UPDATE
Currently, that's
So if you provide
table t1
, it's getting parsed as a table namedtable
. Actually, to do that you need to have it in quotes"table" t1
which you're doing, or your library is doing.Let's create some text data,
Now we can try your original query and get your original result,
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.
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.
Why it's currently Working As Designed
Why you can not use aliases, xocolatl from IRC helps with that,
So in code to
CREATE
a table with a custom composite type an execute anUPDATE
on it.So the syntax that permits the
.
ismycol.type-address
, nottablealias.col-name
.Solving the ambiguous syntax problem
If that didn't make sense, any behavior but this behavior would give you an ambiguous syntax,
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 namedmycol
, on the tablemytable
.