Return constant literal from select query in postgresql

Question

SELECT (
 CASE  
   WHEN t.status_id = 13 THEN 1
   WHEN t.status_id = 14 THEN 2 
 END)
FROM tea t WHERE t.id =13

Above query is working. But what I need is to return a string instead of number.

SELECT (
 CASE  
   WHEN t.status_id = 13 THEN CLOSE
   WHEN t.status_id = 14 THEN OPEN
 END)
FROM tea t WHERE t.id =13

[As documented in the manual](http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS), string literals/constants need to be enclose in single quotes: `then 'CLOSE'` - but a lookup table might be a better solution. Also: putting the case between parentheses ( `(case ... end) `) is totally useless. — , Feb 15 '16 at 09:06

score 0 · Answer 1 · edited May 23 '17 at 11:59

Like @a_horse told you, but I advise to add an explicit type cast to be clear and avoid any possible complication:

SELECT CASE t.status_id              -- "switched" case
          WHEN 13 THEN text 'CLOSE'  -- explicit cast (text would be default)
          WHEN 14 THEN      'OPEN'   -- later CASE terms are coerced to same type
                                     -- so cast is optional
       END AS status                 -- add a column alias.
FROM   tea t
WHERE  t.id = 13;

Return constant literal from select query in postgresql

1 Answers1