CODEWITHSUNDEEP
postgresql
unexplored
unexplored

Reputation: 1424

Replace column integer values with matching strings in PostgreSql

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?

Upvotes: 2

Views: 5090

Answers (2)

Marc Lambrichs
Marc Lambrichs

Reputation: 2892

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;

Upvotes: 2

user330315
user330315

Reputation:

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.

Upvotes: 10

Related Questions