How to cast varchar to boolean

Question

I have a variable 'x' which is varchar in staging table, but it is set to boolean in target table which has 'true' and 'false' values. How can I convert varchar to boolean in postgresql?

Answer 1

In aws redshift unfortunately @klin answer doesn't work as mentioned by others. Inspired in the answer of @FoxMulder900, DECODE seems the way to go but there is no need to cast it to an integer first:

SELECT DECODE(original, 
  'true', true,   -- decode true
  'false', false, -- decode false
  false           -- an optional default value
) as_boolean FROM bar;

The following works:

WITH bar (original) AS
  (SELECT 'false' UNION SELECT 'true' UNION SELECT 'null') -- dumb data
SELECT DECODE(original, 
  'true', true,   -- decode true
  'false', false, -- decode false
  false           -- an optional default value
) as_boolean FROM bar;

which gives:

original	as_boolean
false	false
null	false
true	true

I hope this helps redshift users.

Answer 2

If the varchar column contains one of the strings (case-insensitive):

• t, true, y, yes, on, 1
• f, false, n, no, off, 0

you can simply cast it to boolean, e.g:

select 'true'::boolean, 'false'::boolean;

 bool | bool 
------+------
 t    | f
(1 row) 

See SQLFiddle.

Answer 3

For Redshift, I had the best luck with the following:

SELECT DECODE(column_name, 
             'false', '0', 
             'true', '1'
             )::integer::boolean from table_name;

This simply maps the varchar strings to '0' or '1' which Redshift can then cast first to integers, then finally to boolean.

---

A big advantage to this approach is that it can be expanded to include any additional strings which you would like to be mapped. i.e:

    SELECT DECODE(column_name, 
             'false', '0', 
             'no', '0', 
             'true', '1',
             'yes', '1'
             )::integer::boolean from table_name;

---

You can read more about the DECODE method here.