Select value from an enumerated list in PostgreSQL

Question

I want to select from an enumaration that is not in database.

E.g. SELECT id FROM my_table returns values like 1, 2, 3 I want to display 1 -> 'chocolate', 2 -> 'coconut', 3 -> 'pizza' etc. SELECT CASE works but is too complicated and hard to overview for many values. I think of something like

SELECT id, array['chocolate','coconut','pizza'][id] FROM my_table

But I couldn't succeed with arrays. Is there an easy solution? So this is a simple query, not a plpgsql script or something like that.

Answer 1

This is the correct syntax:

SELECT id, (array['chocolate','coconut','pizza'])[id] FROM my_table

But you should create a referenced table with those values.

Answer 2

with food (fid, name) as (
  values 
     (1, 'chocolate'),
     (2, 'coconut'),
     (3, 'pizza')
)
select t.id, f.name
from my_table t
  join food f on f.fid = t.id;

or without a CTE (but using the same idea):

select t.id, f.name
from my_table t
  join (
     values 
       (1, 'chocolate'),
       (2, 'coconut'),
       (3, 'pizza')
  ) f (fid, name) on f.fid = t.id;