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