- Newest
- Most votes
- Most comments
Hi,
From the notes, I understand that you are trying to cast a boolean field to varchar and encountering below error:
ERROR: cannot cast type character varying to boolean
In order to replicate and troubleshoot the issue at my end, please find below the steps I followed :
- Created a test table and inserted some sample data in it.
dev=# create table booltest(id int, flag boolean); CREATE TABLE
dev=# insert into booltest values(1, true); INSERT 0 1 dev=# insert into booltest values(2, true); INSERT 0 1 dev=# insert into booltest values(3, false); INSERT 0 1 dev=# insert into booltest values(4, false); INSERT 0 1
dev=# select * from booltest; id | flag ----+------ 1 | t 2 | t 3 | f 4 | f (4 rows)
- Then, I tried using cast/convert function to convert boolean datatype to text datatype.
dev=# select convert(varchar, flag) from booltest; ERROR: cannot cast type boolean to character varying
dev=# select cast(flag as varchar) from booltest; ERROR: cannot cast type boolean to character varying
dev=# select flag::varchar from booltest; ERROR: cannot cast type boolean to character varying
[+] CAST and CONVERT functions - https://docs.aws.amazon.com/redshift/latest/dg/r_CAST_function.html
- As a workaround, I tried the below commands and it worked fine.
dev=# select id,case flag when true then 'true'::varchar else 'false'::varchar end from booltest;
id | case
----+-------
1 | true
2 | true
3 | false
4 | false
(4 rows)
or
dev=# select id,decode(flag,true,'true',false,'false') from booltest;
id | case
----+-------
1 | true
2 | true
3 | false
4 | false
(4 rows)
[+] https://docs.aws.amazon.com/redshift/latest/dg/r_DECODE_expression.html
Unfortunately, I regret to inform you that above mentioned are the possible ways to cast a boolean field to varchar.
I hope the above information helps.
Thank you and have nice day !
Relevant content
- Accepted Answerasked 2 years ago
- Accepted Answerasked 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 3 years ago
- AWS OFFICIALUpdated a year ago
Thanks for your reply. My question is not about converting the boolean to a varchar, but rather how keep it as a boolean. I will try to update my original question to more clearly state what I am trying to do.