1

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:

INSERT INTO rawdata VALUES
  ('1','A','5994'),('1','B','28525'),('1','C','18577'),
  ('2','A','30522'),('2','B','5238'),('2','C','18268'),
  ('3','A','982'),('3','B','13401'),('3','C','24158'),
  ('4','A','8544'),('4','B','31575'),('4','C','16661'),
  ('5','A','600'),('5','B','5242'),('5','C','8740'),
  ('6','A','2557'),('6','B','69'),('6','C','31572'),
  ('7','A','4212'),('7','B','26269'),('7','C','27918'),
  ('8','A','29821'),('8','B','22603'),('8','C','32578'),
  ('9','A','8027'),('9','B','13668'),('9','C','32000'),
  ('10','A','17673'),('10','B','11723'),('10','C','8937');

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Dennis Nolte
  • 455
  • 5
  • 14

1 Answers1

2

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600