postgres simple number to string mapping

Question

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.

Answer 1

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

Answer 2

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'

Answer 3

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;