case statement for boolean invalid input syntax for type boolean :"" in sql

Question

Trying to insert the columns from one table (table_temp) to another table(main_table), while doing the type casting the columns from table_temp table.

Below command running in redshift. I am getting output like this.

invalid input syntax for type boolean: ""

insert into main_table (
  select 
    case 
      when id = 'null' then null 
      when id ='' then null 
      else id:: varchar(max) 
    end as id, 
    case 
      when date = 'null' then null 
      when date ='' then null 
      else date ::timestamp 
    end as date, 
    case when payment = 'null' then null 
      when payment ='' then null 
      when payment ='None' then null 
      else payment::double precision 
    end as payment, 
    case 
      when isdeleted = 'null' then null 
      when isdeleted ='' then null 
      else replace(replace( isdeleted ,'false','0'),'true','1')::int::boolean 
    end as isdeleted 
  from table_temp
  );

Answer 1

You likely have other values in that column, such as 'abcstring'.

If you want to display true/false/null, then this should work:

case 
 when isdeleted = 'null' then null 
 when isdeleted ='' then null 
 when isdeleted in ('true','false') then replace(replace(isdeleted ,'false','0'),'true','1')::boolean 
end as isdeleted

If you want to display 1/0/null, then this should work:

case 
 when isdeleted = 'null' then null 
 when isdeleted ='' then null 
 when isdeleted in ('true','false') then isdeleted::boolean
end as isdeleted

I'm not a redshift user, so you may or may not need an "else null" for that. In Postgres it runs fine as-is. And since I have no Redshift playground, then perhaps this is wrong altogether. In that event, I can remove this answer.