create varchar column from boolean columns postgresql

Question

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.

how should the output look like? do you need a delimited result if multiple conditions are true? — Vamsi Prabhala
– Vamsi Prabhala, Commented Apr 19, 2017 at 23:31
maybe create a view that contains your varchar color column instead — supergrady
– supergrady, Commented Apr 19, 2017 at 23:32
yes, i want it to be delimited if multiple conditions are true. — geoinfo
– geoinfo, Commented Apr 19, 2017 at 23:33

Gordon Linoff · Accepted Answer · 2017-04-19 23:32:52Z

1

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.

answered Apr 19, 2017 at 23:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

create varchar column from boolean columns postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related