Postgresql – column {table_name} of relation {table_name} does not exist SQL state: 42703

postgresqltableupdate

I am getting an error while running an update query with table name specified along with column name:

UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245'

This is the error:

ERROR:  column "temp" of relation "temp" does not exist
LINE 1:      UPDATE Temp SET Temp.Id='23...
                               ^
********** Error **********

ERROR: column "temp" of relation "temp" does not exist
SQL state: 42703
Character: 24

Best Answer

You cannot (and need not) use table aliases (or tablename qualified column names) in the SET clause of an UPDATE. This even makes sense, as you can only update a single table in a single UPDATE, so there is no ambiguity in column names there.

Fortunately, the ever helpful documentation explicitly mentions your case:

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 tab SET tab.col = 1 is invalid.

So, the solution is to simply remove temp. from the SET clause:

UPDATE temp SET id = '234', name = 'Test' WHERE id = '245'

Notes:

Are you really storing numbers as text? If yes, why? It is usually a recipe for disaster. For example, how do you prevent something like 'mkjcvnd7y78r3tgbhvcjh' entering your id column?
The way you are using object names starting with capital letters is confusing. Without double-quoting its name, your table in reality is called temp as opposed to Temp. Using it the latter way may decrease readability (depending on your preferences and habits, of course).

Related Solutions

Postgresql – How to find table name for each row result

There must be some kind of misunderstanding. tableoid is a system column that's available for any regular table in any version of Postgres since at least v7.3. Per documentation:

Every table has several system columns that are implicitly defined by the system.

tableoid is one of them. The solution I provided under the questions you refer to is also suggested in the manual here. And it works. I have been using it for years in various Postgres versions.

Either you are not using Postgres, or template is not a table. Is it a VIEW maybe? If you define a VIEW like:

CREATE VIEW template AS
SELECT * FROM some_table;

Then system columns are not included by default.

What do you get for

SELECT relnamespace, relname, relkind
FROM   pg_class
WHERE  relname = 'template';

Is relkind 'r', 'v' or something else?
Or do you get multiple rows? Then consider this:

Why does a PostgreSQL SELECT query return different results when a schema name is specified?

PostgreSQL CASE Expression – Resolving ‘Column Doesn’t Exist’ Error in WHERE Clause

The WHERE clause is evaluated before aliases in the SELECT clause. WHERE is used to locate rows from the base table which are the input to all expressions in the SELECT. Only after locating the rows your CASE can be evaluated with real values and the final_price alias is assigned its value.

There are multiple ways to solve that:

repeat the same expression in the WHERE clause - that way it will be used to locate the rows
wrap your query as a subquery in another query and move the WHERE to the outer query - as you select from a derived table, the alias becomes a "real" column that way
_{(this would work in MySQL, but not in PostgreSQL) use the HAVING clause instead of WHERE - HAVING (in MySQL) works after the SELECT is computed to do a "second wave" of filtering. In standard SQL (and in Postgres), SELECT is logically processed after HAVING, so this won't work.}
create a generated/virtual column from your CASE expression, that way it becomes a part of your base table and you might even be able to index it

Warning: your query will need to do fulltable/index scan unless you can add some "simpler" WHERE conditions or be able to use the indexed virtual column.

Best Answer

Related Solutions

Postgresql – How to find table name for each row result

PostgreSQL CASE Expression – Resolving ‘Column Doesn’t Exist’ Error in WHERE Clause

Related Question