Rewrite Select SQL query for PostgreSQL

Question

I created these tables in PostgreSQL based on this topic :

-- 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)
;

But when I run Select SQL query I get error because it's for 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

result

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

Howe I can rewrite this SQL query for PostgreSQL?

This is valid Standard SQL, try adding `AS`: `AS value` (I just tried it on Fiddle and this worked). Seems to be a weird parsing error. — dnoeth, May 14 '16 at 09:56

score 1 · Accepted Answer · 2016-05-14T11:41:46.807

value is a reserved keyword, you need to quote it in SQL:

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

Adding the as keyword works, because this remove the ambiguity what value might be. But it's still better to quote it - even when using the as keyword (or find a different name).

This behaviour is documented and the manual explicitly mentions the keyword value

The AS keyword is optional, but only if the new column name does not match any PostgreSQL keyword (see Appendix C). To avoid an accidental match to a keyword, you can double-quote the column name. For example, VALUE is a keyword, so this does not work:

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

but this does:

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

For protection against possible future keyword additions, it is recommended that you always either write AS or double-quote the output column name.

Unrelated, but:

You shouldn't store boolean values (true, false) in a text column. Postgres has a native boolean data type for this.

Rewrite Select SQL query for PostgreSQL

1 Answers1