CODEWITHSUNDEEP
sqlpostgresql
user615993
user615993

Reputation: 185

Update composite column

I just want to fill column7 of my_table with values from other columns so the result for column7-value looks like that: 86|WWB|2014 or 86|WWB|- in case that column3 has value 0. Here is my SQL:

UPDATE public.my_table
SET column7 =   
case when column1 IS NULL then '-' else column1 end   
|| '|' || 
case when column2 IS NULL then '-' else column2 end    
|| '|' || 
case when column3 = '0' then '-'  else column3 end 


error: invalid input syntax for integer: "_"

The problem ist the last case-row, because column3 is integer. Column1 and column2 are bpchar, column3 ist int2. Is there a way to solve this problem?

Upvotes: 0

Views: 54

Answers (2)

user330315
user330315

Reputation:

Using concat will make this a lot easier to read and it automatically converts everything to text. However the case statement needs to yield the same data type for all branches, so a cast to text is still needed for column3

UPDATE public.my_table
  SET column7 = concat( 
                   coalesce(column1, '-'), '|'
                   coalesce(column2, '-'), '|'
                   case when coalesce(column3,0) = 0 then '-' else column3::text end
                );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You are having type collisions. It is easy to convert in Postgres:

UPDATE public.my_table
    SET column7 = (coalesce(column1::text, '-') || '|' || 
                   coalesce(column2::text, '-') || '|' || 
                   (case when column3 = 0 then '-' else column3::text end)
                  );

Upvotes: 1

Related Questions