I need to create a varchar column containing color value from boolean columns, here's the table structure :
CREATE TABLE public.prosp(
id serial PRIMARY KEY NOT NULL,
isblack bool,
isyellow bool,
isgreen bool
);
I want to add a column called color containg for example : green
if isgreen = true, I tried this and it worked
SELECT
"id",
CASE WHEN "isblack" THEN 'black; ' ELSE '' END ||
CASE WHEN "isyellow" THEN 'yellow; ' ELSE '' END ||
CASE WHEN "isgreen" THEN 'green; ' ELSE '' END
AS couleurs
FROM public.prosp
now I need to put the result of the above expression in color column.
Thanks.
Is this what you want?
alter table public.prosp add column color varchar(255);
update public.prosp
set color = (CASE WHEN isblack THEN 'black; ' ELSE '' END) ||
(CASE WHEN isyellow THEN 'yellow; ' ELSE '' END) ||
(CASE WHEN isgreen THEN 'green; ' ELSE '' END);
I should note that often this would be done using concat_ws():
update public.prosp
set color = concat_ws(',',
(CASE WHEN isblack THEN 'black' END),
(CASE WHEN isyellow THEN 'yellow' END),
(CASE WHEN isgreen THEN 'green' END)
);
This puts a comma between the values, rather than a semicolon at the end.