Postgres在列名之前不接受表别名 [英] Postgres won't accept table alias before column name

问题描述

我正在使用一个框架（Jodd），该框架将表别名添加到SQL Select的列名称中。它看起来像格式正确的SQL，但Postgres对此感到窒息。

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)

给出错误：

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

是否存在一种使Postgres接受该SQL的方法？我的另一种选择是破解我不想做的框架。

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.

推荐答案

问题是您包括了列中 SET 子句中的表别名。请参阅Postgres文档中 UPDATE 的文档：

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

列

表 中列的名称。如果需要，可以使用子字段名称或数组下标来限定列名称。不要在目标列的规范中包括表的名称-例如， UPDATE选项卡SET选项卡。col= 1 无效。

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.

在Postgres中有效：

This is valid in Postgres:

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

这篇关于Postgres在列名之前不接受表别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！