Merge 2 columns and replace with specific output
Create a translation table, let's call it sub1_dict:

CREATE TABLE sub1_dict (
   first int NOT NULL
 , sub   varchar NOT NULL
 , name  text NOT NULL -- find a better name than "name"
 , CONSTRAINT t_pkey PRIMARY KEY (first, sub) -- INCLUDE (name) -- see below
   );


Enter all defined translations in this table.

Since you specified:


  I only need the output where I have a defined "replacement name"


... use an [INNER] JOIN in the query, excluding rows without translation:

SELECT s.name, r.value
FROM   rawdata r
JOIN   sub1_dict d USING (first, sub)
WHERE  r.first &lt; 3;


The added PK should serve your purpose. The INCLUDE clause requires Postgres 11. The intention is to allow fast index-only scans - not important with small tables.

Same for your table rawdata: if it's big, add an index on (first, sub). Or a PK or UNIQUE constraint. Column first first to also serve your WHERE clause optimally. See:


Is a composite index also good for queries on the first field?


If you get index-only scans out of it, consider another covering index like above, with INCLUDE (name) in Postgres 11 or later.


Can Postgres use an index-only scan for this query with joined tables?