Replace column integer values with matching strings in PostgreSql

Question

Suppose I have the following events table:

 Column |   Type  |
--------+---------+
 id     | integer |
 type   | integer |

The event type can be any user provided number and there is no relation on any other table in the system associated with this type.

The request came in that I generate a report and I need to match the type to a user provided string.

The user may provide the following:

1 = "Created event" 
2 = "Deleted event" 
3 = "Updated event"

This is overly simplified, but you get the idea.

Now I need to generate the report that includes the event table but instead of the type the matching string should be used.

I read the manual on the string functions and found the replace function which only solves the problem partially - I can only replace on type:

select replace(type::text, '3', 'Updated event') from events;

This will return all the events rows and only the rows that have the type 3 will have the correct string, the others will have the integer.

How can I leverage that to be able to match the type to a user string in one select?

user330315 · Accepted Answer · 2017-09-07 19:31:38Z

10

You need a case expression:

select case type
          when 1 then 'Created event'
          when 2 then 'Deleted event'
          when 3 then 'Updated even'
          else 'Unknown Type'
       end as type_name
from events;

Another option (maybe a bit more flexible) is to create the lookup table "on-the-fly":

with type_names (id, type_name) as (
   values 
      (1, 'Created event'),
      (2, 'Deleted event'),
      (3, 'Updated event')
)
select e.id, coalesce(tn.type_name, 'Unknown type') as type_name
from events e 
  left join type_names tn on t.id = e.type;

The outer join is necessary in case there are events that have a different type number then expected.

answered Sep 7, 2017 at 19:31

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

unexplored Over a year ago

The "on-the-fly" table is exactly what I was looking for, I just didn't know it was possible and didn't know how to put my google query together to find such options. Thanks!

user330315 Over a year ago

You can also put that "on-the-fly" table into a view to simplify the queries using it

Marc Lambrichs · Accepted Answer · 2017-09-07 19:32:32Z

2

The case conditional will do what you want:

SELECT CASE 
       WHEN type=1 THEN 'Created event'
       WHEN type=2 THEN 'Deleted event'
       ELSE 'Updated event'
       END
FROM events;

answered Sep 7, 2017 at 19:32

Marc Lambrichs

2,8922 gold badges15 silver badges16 bronze badges

Collectives™ on Stack Overflow

Replace column integer values with matching strings in PostgreSql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related