Postgres won't accept table alias before column name

Question

I'm using a framework (Jodd) which is adding the table alias to the column names in a SQL Select. It looks like well-formed SQL, but Postgres chokes on it.

update GREETING Greeting       set Greeting.ID=5,           Greeting.NAME='World',           Greeting.PHRASE='Hello World!'   where (Greeting.ID=5) 

gives an error:

Error: ERROR: column "greeting" of relation "greeting" does not exist SQLState:  42703 

Is there a way to get Postgres to accept that SQL? My other alternative is to hack the framework, which I don't want to do.

Answer 1

The problem is that you include the table alias in SET clause, in the columns. See the documentation of UPDATE in Postgres docs:

column

The name of a column in table. 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.

This is valid in Postgres:

update GREETING Greeting  set      NAME='World',      PHRASE='Hello World!'  where Greeting.ID=5 ;