CODEWITHSUNDEEP
sqlamazon-redshift
Saba Malik
Saba Malik

Reputation: 281

Need to fetch Boolean type column as varchar from source table and store as varchar in target table in Amazon redshift

I have been working with redshift for a month. I need your help regarding this;

I need to create a target table using source table (create table target as select * from source), there is a column in source table type of boolean, I need to store that column as varchar in target table. Tried lots of methods like cast, convert..etc nothing worked for me. After done with lots of search, I got to know that boolean cannot be converted to another data type in redshift.

It shows me below error;

Amazon Invalid operation: column "indicator" is of type boolean but expression is of type character varying; [SQL State=42804, DB Errorcode=500310] 1 statement failed.

I shall be grateful for your help.

Upvotes: 0

Views: 3626

Answers (4)

Ajeet Verma
Ajeet Verma

Reputation: 1123

The same issue I was also facing ,please check below how I fixed it.

In my original code one column having Boolean data type and when I was doing union of two table it was failing due to below error.

[Amazon](500310) Invalid operation: UNION types character varying and boolean cannot be matched;

I have attached the screenshot for your reference:

enter image description here

Now I have just used the case statement inside the select for the Boolean data type column and it's working fine.

enter image description here

Upvotes: 0

Saba Malik
Saba Malik

Reputation: 281

I am able to resolve the issue using nested case statement at the time of table creation - I am posting the solution here so that It can be helpful for others as well.

case 
  when name in ('a', 'b') then 
  case offline_crt when true then 'true' else 'false' end
  else 'N/A'
  end as indicator

Upvotes: 0

PBD
PBD

Reputation: 61

If you can't convert directly from Boolean to varchar, you could use

CASE 
    WHEN boolCol= TRUE THEN 'True'
    WHEN boolCol= FALSE THEN 'False'
    ELSE 'Unknown'
END AS boolCol

to sidestep the need for conversion.

Upvotes: 0

Sivakumar
Sivakumar

Reputation: 1751

The following approach might work for your case.

When loading the data from source table, instead of selecting all the columns using SELECT *, you explicitly call out the individual columns and for the boolean column use a case expression and evaluate the actual data and return the result as string 'true' or 'false'.

create table target as select colA, colB, case boolColC when true then 'true' else 'false' end from source

Upvotes: 1

Related Questions