postgresql 如何将 varchar 转换为布尔值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37687830/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to cast varchar to boolean
提问by Ramesh
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?
我有一个变量“x”，它在临时表中是 varchar，但它在目标表中设置为布尔值，它具有“true”和“false”值。如何在 postgresql 中将 varchar 转换为布尔值？
回答by klin
If the varchar column contains one of the strings(case-insensitive):
如果 varchar 列包含字符串之一（不区分大小写）：
t,true,y,yes,on,1f,false,n,no,off,0
t,true,y,yes,on,1f,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.
请参阅SQLFiddle。
回答by FoxMulder900
For Redshift, I had the best luck with the following:
对于 Redshift，我最幸运的是：
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.
这只是将 varchar 字符串映射到'0'or '1'Redshift 然后可以首先将其转换为整数，然后最后转换为布尔值。
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:
这种方法的一大优点是可以扩展它以包含您想要映射的任何其他字符串。IE：
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.
您可以在此处阅读有关 DECODE 方法的更多信息。
回答by Jonathan Perkins
If you can assume anything besides trueis false, then you could use:
如果您可以假设除trueis之外的任何内容false，那么您可以使用：
select
column_name = 'true' column_name_as_bool
from
table_name;
回答by KARASZI István
For old PostgreSQL versions and in Redshift casting won't work but the following does:
对于旧的 PostgreSQL 版本和 Redshift 转换将不起作用，但以下内容有效：
SELECT boolin(textout('true'::varchar)), boolin(textout('false'::varchar));
See SQLFiddlealso see the discussion on the PostgreSQL list.
请参阅SQLFiddle也可参阅有关PostgreSQL 列表的讨论。
