0

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.

2

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

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

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;
  • 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. – Patrick Mevzek Jan 7 at 21:20

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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