CODEWITHSUNDEEP
sqlpostgresql
Richard
Richard

Reputation: 8935

SQL SELECT column value based on value

I am using Postgresql. I would like to write a SELECT statement with a column value based on the value in the database.

For example.

|  id  |  indicator  |
|   1  |       0     |
|   2  |       1     |

indicator can be 0 or 1 only where 0 = manual and 1 = auto.

Expected output from a SELECT *

1 manual
2 auto

Upvotes: 0

Views: 423

Answers (1)

user330315
user330315

Reputation:

You can use a case expression:

select id, case indicator
             when 0 then 'manual'
             when 1 then 'auto'
           end as indicator
from the_table;

If you need that frequently you could create a view for that.

In the long run, it might be better to create a proper lookup table and join to that:

create table indicator
(
  id integer primary key, 
  name text not null
);
insert into indicator (id, name)
values (0, 'manua', 1, 'auto');

alter table the_table 
   add constraint fk_indicator 
   foreign key (indicator) references indicator (id);

Then join to it:

select t.id, i.name as indicator
from the_table t
  join indicator i on i.id = t.indicator;

Upvotes: 4

Related Questions