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?
-
1Do you want to insert, select update? Or what? – Jorge Campos Jun 07 '16 at 19:39
-
I would like to Update – Ramesh Jun 07 '16 at 19:49
5 Answers
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.

- 30,900
- 8
- 101
- 128

- 112,967
- 15
- 204
- 232
-
3This is not true, see `select 'true'::varchar::boolean, 'false'::varchar::boolean;` – KARASZI István Aug 21 '17 at 13:56
-
3What'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
-
1Confirm 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
-
-
[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
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.

- 1,272
- 13
- 27
-
1I 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
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.

- 560
- 5
- 18
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.

- 30,900
- 8
- 101
- 128
If you can assume anything besides true
is false
, then you could use:
select
column_name = 'true' column_name_as_bool
from
table_name;

- 189
- 1
- 8