14

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?

Ramesh
  • 251
  • 2
  • 4
  • 12

5 Answers5

22

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.

KARASZI István
  • 30,900
  • 8
  • 101
  • 128
klin
  • 112,967
  • 15
  • 204
  • 232
  • 3
    This is not true, see `select 'true'::varchar::boolean, 'false'::varchar::boolean;` – KARASZI István Aug 21 '17 at 13:56
  • 3
    What's wrong with it?? [SQLFiddle](http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/17922) – klin Aug 21 '17 at 14:13
  • Hmm, interesting. I would have sworn that I've seen an error in new PostgreSQL as well, but it seems that this is only an issue in Redshift and old PostgreSQL versions. – KARASZI István Aug 21 '17 at 14:28
  • 1
    Confirm that redshift will throw an error with `select 'true'::varchar::boolean;`: Invalid operation: cannot cast type character varying to boolean; – Alan Jun 09 '20 at 20:33
  • for some reason it doesn't work in redshift – Batato Jan 10 '22 at 10:39
  • [Redshift is not Postgres.](https://www.google.com/search?newwindow=1&client=firefox-b-d&sxsrf=AOaemvI4t3ELsDXj7GP8QlNfHx2d8QJmMg:1641982990147&q=redshift+vs+postgres&spell=1&sa=X&ved=2ahUKEwjd--qm_6v1AhWVyosKHaWbA_AQkeECKAB6BAgBEDc) – klin Jan 12 '22 at 10:22
12

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.

FoxMulder900
  • 1,272
  • 13
  • 27
  • 1
    I am not seeming to need the intermediate `::INTEGER::` cast. `SELECT DECODE(col_name, 'N', '0', 'Y', '1' )::BOOLEAN FROM table_name;` is working fine for me in Redshift. – leerssej Dec 11 '19 at 22:20
4

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.

Batato
  • 560
  • 5
  • 18
3

For old PostgreSQL versions and in Redshift casting won't work but the following does:

SELECT boolin(textout('true'::varchar)), boolin(textout('false'::varchar));

See SQLFiddle also see the discussion on the PostgreSQL list.

KARASZI István
  • 30,900
  • 8
  • 101
  • 128
1

If you can assume anything besides true is false, then you could use:

select
  column_name = 'true' column_name_as_bool
from
  table_name;
Jonathan Perkins
  • 189
  • 1
  • 8