8

I have a an Enum at the programming language level which is stored as a simple integer on the table. Think:

APPLE = 1
GOOGLE = 2
MSFT = 3
AMAZON = 4
 ... (100s more)

I just wanna query the table and instead of the number return the corresponding string value. Is there an easier way of doing this without using case statement or a temp table:

SELECT
  CASE WHEN type = 1 THEN "APPLE"
  CASE WHEN type = 2 THEN "GOOGLE"
  CASE WHEN type = 3 THEN "MSFT"
  CASE WHEN type = 4 THEN "AMAZON"
  ...
  ELSE "UNKNOWN"
FROM t

Basically it's just a key look-up in a dictionary.

3 Answers 3

8

IMHO the easiest way is by using a lookup table.

create table lk (id int, name text);

insert into lk values
(1, 'apple'),(2, 'google'),(3, 'msft'),(4, 'amazon');

create table t (id serial, lk int);
insert into t (lk) values (2),(1),(3),(4);

select
    t.id,
    lk.name
from   t
join   lk
       on lk.id = t.lk;
id | name  
-: | :-----
 2 | apple 
 1 | google
 3 | msft  
 4 | amazon

db<>fiddle here

2
  • Thanks. I specifically wanted to avoid temp table but you gave me enough clue.
    – Sam R.
    Jan 3, 2019 at 23:47
  • You can deal with ENUM postgres type, but to me is quite more difficult than a lookup table
    – McNets
    Jan 3, 2019 at 23:51
6

JSON FTW. As a JS developer I'm partial to Object mapping. Here's an example of what that would look like in PG:

SELECT COALESCE(
  (
    (json_build_object(
      1, 'APPLE',
      2, 'GOOGLE',
      3, 'MSFT',
      4, 'AMAZON'
    )::jsonb
  )->>(2::text))::text,
  'UNKNOWN'
); 
-- RETURNS 'GOOGLE'
3
  • 1
    thanks this helped me to map enums integer value back to string
    – Jose Kj
    Apr 3, 2020 at 6:36
  • Should be the accepted answer. Sep 30, 2020 at 13:38
  • nice, clean way of mapping enums, thanks!
    – santuxus
    Jun 29, 2022 at 16:10
5

Alright, using @McNets' answer I used with clause without having a temp table:

create table t (id serial, lk int);
insert into t (lk) values (2),(1),(3),(4);

with m (k, v) as (values (1, 'apple'),(2, 'google'),(3, 'msft'),(4, 'amazon'))
select t.id, m.v
from t
join m
  on m.k = t.lk;
2
  • 2
    The difference being that the day you need to add a new entry in the mapping you will either need to change your code or some stored procedure/view where the above is contained (same problem by using CASE), whereas by using a table you will just need to insert a row, which is low impact. And if your mapping is with hundreds of values as you say, such a with statement will not be pretty. You do not need a "temporary" table just create once for all a table with the mapping and you are done forever regarding the design. Jan 7, 2019 at 21:20
  • 1
    the advantage here is say matching '01' -> 'January'...
    – pmc
    Oct 2, 2019 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.