重写PostgreSQL的Select SQL查询

彼得·彭佐夫

我根据以下主题在PostgreSQL中创建了这些表：

-- TABLE SETTING

CREATE TABLE SETTING(
 ID INTEGER NOT NULL,
 DESCRIPTION TEXT,
 CONSTRAINED TEXT,
 DATA_TYPE TEXT,
 MIN_VALUE TEXT,
 MAX_VALUE TEXT
)
;

-- ADD KEYS FOR TABLE SETTING

ALTER TABLE SETTING ADD CONSTRAINT KEY34 PRIMARY KEY (ID)
;

-- TABLE ALLOWED_SETTING_VALUE

CREATE TABLE ALLOWED_SETTING_VALUE(
 ID INTEGER NOT NULL,
 SETTING_ID INTEGER,
 ITEM_VALUE TEXT,
 CAPTION TEXT
)
;

-- CREATE INDEXES FOR TABLE ALLOWED_SETTING_VALUE

CREATE INDEX IX_RELATIONSHIP16 ON ALLOWED_SETTING_VALUE (SETTING_ID)
;

-- ADD KEYS FOR TABLE ALLOWED_SETTING_VALUE

ALTER TABLE ALLOWED_SETTING_VALUE ADD CONSTRAINT KEY35 PRIMARY KEY (ID)
;

-- TABLE USER_SETTING

CREATE TABLE USER_SETTING(
 ID INTEGER NOT NULL,
 USER_ID INTEGER,
 SETTING_ID INTEGER,
 ALLOWED_SETTING_VALUE_ID INTEGER,
 UNCONSTRAINED_VALUE TEXT
)
;

-- CREATE INDEXES FOR TABLE USER_SETTING

CREATE INDEX IX_RELATIONSHIP15 ON USER_SETTING (SETTING_ID)
;

CREATE INDEX IX_RELATIONSHIP17 ON USER_SETTING (ALLOWED_SETTING_VALUE_ID)
;

-- ADD KEYS FOR TABLE USER_SETTING

ALTER TABLE USER_SETTING ADD CONSTRAINT KEY36 PRIMARY KEY (ID)
;

但是，当我运行Select SQL查询时，出现错误，因为它是针对MySQL的：

-- Show settings for a given user
select
  US.user_id 
, S1.description 
, S1.data_type 
, case when S1.constrained = 'true'
  then AV.item_value
  else US.unconstrained_value
  end value
, AV.caption
from USER_SETTING US
  inner join SETTING S1
    on US.setting_id = S1.id 
  left outer join ALLOWED_SETTING_VALUE AV
    on US.allowed_setting_value_id = AV.id
where US.user_id = 234

结果

ERROR:  syntax error at or near "value"
LINE 8:   end value

我如何为PostgreSQL重写此SQL查询？

a_horse_with_no_name

value 是保留关键字，您需要在SQL中将其引用：

case when S1.constrained = 'true'
    then AV.item_value
    else US.unconstrained_value
end "value"

添加as关键字是可行的，因为这样可以消除歧义value。但是最好还是用引号将其引号-即使在使用as关键字（或查找其他名称）时也是如此。

此行为已记录在案，并且手册中明确提到了关键字value

AS关键字是可选的，但仅当新列名与任何PostgreSQL关键字都不匹配时（请参阅附录C）。为避免与关键字意外匹配，您可以将列名双引号。例如，VALUE是一个关键字，因此它不起作用：

SELECT a value, b + c AS sum FROM ...

但这确实是：

选择一个“值”，b + c AS总和来自...

为了防止将来可能增加关键字，建议您始终写AS或在输出列名称中用双引号引起来。

无关，但是：

您不应将布尔值（true，false）存储在text列中。Postgresboolean为此具有本机数据类型。