The setup is a Debian 8 with Postgres 9.4 (64bit if that matters).

Given following table I want to "search" for a combination of columns first and column sub and replace their output with another string.

CREATE TABLE public.rawdata (
 first integer,
 sub character varying COLLATE pg_catalog."default",
 value integer

Example data:


For example, the two column '1' and 'A' should read in the output as 'cat'.

There should be no alteration of the source data.

I have read about using WITH for that but I am uncertain how to join the columns so that I can convert '(1, A)' to 'cat' and '(2, A)' to 'dog'.

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

The end-result should look like that:

Name Value
Cat 5994
Dog 30522

Sadly, I am limited to providing the output that way (if possible at all).
Normally, I would write something like:

select * from rawdata where first < 3

1   "A" 5994
1   "B" 28525
1   "C" 18577
2   "A" 30522
2   "B" 5238
2   "C" 18268

But the next step is something I can't figure out.

1 Answer 1


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 < 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:

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

  • looks good, works as intended, once again thank you for your service. ill accept tomorrow unless there will be a better answer for whatever reason Commented Nov 27, 2018 at 13:51
  • @DennisNolte: I added some more to address performance. Commented Nov 30, 2018 at 20:41

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.