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.

Have you tried setting different column name aliasing mode (http://jodd.org/doc/db/sqlgenerator.html#Column-name-aliasing)? By default TABLE_REFERENCE is set - that will add table aliases for columns, but you can try other two. This can be set by DbOomManager#setDefaultColumnAliasType() method, somewhere at the application startup. Also, don't hesitate to contact Jodders with the real code snippet, they are willing to help :) — igr, Jul 06 '12 at 21:55

score 62 · Accepted Answer · edited Jun 20 '20 at 09:12

62

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 ;

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 06 '12 at 21:51

ypercubeᵀᴹ

113,259
19
174
235

Maybe, you do not need to specify table name since SET works with target table only? – Evgeny Nozdrev May 10 '18 at 13:17
@ЕвгенийНоздрев are you asking why that limitation exists? Yeah, I suppose it has to do with that only one table can be updated in an UPDATE statement. But it is a limitation, I think the standard allows you to use `SET tablename.column = ` so it will be fixed at som epoint (or maybe it has already been fixed, I have to check) – ypercubeᵀᴹ May 10 '18 at 13:20
This solution was perfect to solve my problem. Thanks! – Genivan Feb 04 '22 at 14:35

score 7 · Answer 2 · answered Jul 06 '12 at 21:54

7

Check documentation on UPDATE statement, specifically for the column part: it is illegal to prefix columns with table alias in the SET clause.

UPDATE GREETING Greeting
   SET ID=5, NAME='World', PHRASE='Hello World!'
 WHERE (Greeting.ID=5);

answered Jul 06 '12 at 21:54

vyegorov

21,787
7
59
73

score 4 · Answer 3 · answered Jul 27 '12 at 23:07

Try using the latest Jodd, v3.3.7. where this issue is fixed.

The problem was in the Jodd library: entity update methods were generating update statement with table aliases. The new version simply does not put table aliases; that works for Postgres and for other databases too.

score 0 · Answer 4 · answered Aug 08 '20 at 09:52

As others have said, it is illegal with postgres to use an alias for the name of the column that is being updated but note that it is possible (and sometimes necessary) to use an alias in the rhs expression. For instance:

CREATE TABLE greetings (name VARCHAR(20), phrase VARCHAR(100));
INSERT INTO greetings (name, phrase);
VALUES ('palindrome_1', 'Rise to vote sir'),
       ('palindrome_2', 'Race fast, safe car');

-- You can do something like this:
UPDATE greetings g SET phrase = REVERSE (g.phrase);
                                      -- ^
                                      -- |
                                      -- +--- this works

This is a bit silly in this example because we don't need that alias. This becomes more useful when multiples tables are involved.

Postgres won't accept table alias before column name

4 Answers4

Linked