PostgreSQL – Simple Number to String Mapping Techniques

casepostgresqlquery

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.

Best Answer

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

Related Solutions

Alternatives to concatenating strings or going procedural to prevent SQL query code repetition

You are too modest - your SQL is well and concisely written given the task you are undertaking. A few pointers:

t1.name <> t2.name is always true if t1.name = REPLACE(t2.name, 'DUP_', '') - you can drop the former
usually you want union all. union means union all then drop duplicates. It might make no difference in this case but always using union all is a good habit unless you explicitly want to drop any duplicates.

if you are willing for the numeric comparisons to happen after casting to varchar, the following might be worth considering:

create view test_attribs_cast as 
select id, name, attr1, attr2, cast(attr3 as varchar(2000)) as attr3, 
       cast(attr4 as varchar(2000)) as attr4, attr5
from test_attribs;

create view test_attribs_unpivot as 
select id, name, 1 as attr#, attr1 as attr from test_attribs_cast union all
select id, name, 2, attr2 from test_attribs_cast union all
select id, name, 3, attr3 from test_attribs_cast union all
select id, name, 4, attr4 from test_attribs_cast union all
select id, name, 5, attr5 from test_attribs_cast;

select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
       t2.name as name_dup, t1.attr as val1, t2.attr as val2
from test_attribs_unpivot t1 join test_attribs_unpivot t2 on(
       t1.id<>t2.id and 
       t1.name = replace(t2.name, 'DUP_', '') and 
       t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
      or (t1.attr is not null and t2.attr is null);

the second view is a kind of unpivot operation - if you are on at least 11g you can do this more concisely with the unpivotclause - see here for an example

I'm say don't go down the procedural route if you can do it in SQL, but...
Dynamic SQL is probably worth considering despite the problems you mention with testing and maintenance

--EDIT--

To answer the more general side of the question, there are techniques to reduce repetition in SQL, including:

Views - you know about that one :)
Common Table Expressions (see here for example)
individual features of the database such as decode (see Leigh's answer for how this can reduce repetition), window functions and hierarchical/recursive queries to name but a few

But you can't bring OO ideas into the SQL world directly - in many cases repetition is fine if the query is readable and well-written, and it would be unwise to resort to dynamic SQL (for example) just to avoid repetition.

The final query including Leigh's suggested change and a CTE instead of a view could look something like this:

with t as ( select id, name, attr#, 
                   decode(attr#,1,attr1,2,attr2,3,attr3,4,attr4,attr5) attr
            from test_attribs
                 cross join (select rownum attr# from dual connect by rownum<=5))
select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
       t2.name as name_dup, t1.attr as val1, t2.attr as val2
from t t1 join test_attribs_unpivot t2 
               on( t1.id<>t2.id and 
                   t1.name = replace(t2.name, 'DUP_', '') and 
                   t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
      or (t1.attr is not null and t2.attr is null);

Postgresql – Should I use the PostgreSQL bit string

If you only have a few variables I would consider keeping separate boolean columns.

Indexing is easy. In particular, indexes on expressions are easy.
Conditions for queries and partial indexing are easy to write and read and meaningful.
A boolean column occupies 1 byte. For only a few variables this occupies the least space.
Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

Occupies 4 bytes on disk.
Very fast indexing for exact matches (= operator).
Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Occupies at least 5 bytes (or 8 for very long strings) plus 1 byte for each group of 8 bits (rounded up).
You can use bit string functions and operators directly.

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.