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.

score 10 · Answer 1 · answered Apr 04 '13 at 18:29

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;

score 4 · Answer 2 · answered Apr 04 '13 at 18:36

4

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.

answered Apr 04 '13 at 18:36

Clodoaldo Neto

118,695
26
233
260

Thank you!!! This is the simple solution I was looking for and it works. Just the parentheses were missing. (I already told once that I don't want to create a table because database is not mine and this is a casual query, but somebody deleted that answer with my comment.) – Turtle Apr 05 '13 at 08:50

score 0 · Answer 3 · answered Apr 04 '13 at 18:00

What about creating another table that enumerate all cases, and do join ?

CREATE TABLE table_case
(
  case_id bigserial NOT NULL,
  case_name character varying,
  CONSTRAINT table_case_pkey PRIMARY KEY (case_id)
)
WITH (
  OIDS=FALSE
);

and when you select from your table:

SELECT id, case_name FROM my_table 
inner join table_case on case_id=my_table_id;

Select value from an enumerated list in PostgreSQL

3 Answers3