在Select(PostgreSQL/pgAdmin)中将布尔值返回为TRUE或FALSE [英] Return Boolean Value as TRUE or FALSE in Select (PostgreSQL/pgAdmin)

问题描述

在PostgreSQL(版本9.4，pgAdmin3)中，当对具有布尔列的表进行选择时，数据输出显示为"t"或"f".我想在不编写CASE语句或不执行JOINS等的情况下将布尔值强制转换/转换为TRUE或FALSE.

顺便说一句，根据PostgreSQL自己的文档，此行为是不是SQL标准.

首选和正确的用法是关键字TRUE和FALSE.

PS:仅当在pgAdmin中使用SQL编辑器时才会发生这种情况.使用pgAdmin对象浏览器，向下钻取到相同的表，右键单击，查看数据，查看前100行，按预期/标准，同一布尔列显示为TRUE或FALSE.

解决方案

如果要显示的只是文字TRUE或FALSE，则可以使用您提出的case语句.由于PostgreSQL将TRUE，true，yes，on，y，t和1视为真实，因此我可以控制输出的外观.

Where子句可以这样写:

select * from tablename where active
--or--
select * from tablename where active = true

(我的建议与PostgreSQL相同-请使用true)

选择时，尽管可能会有些犹豫地使用case语句，但我仍然建议您这样做以控制您的输出字符串文字.

您的查询将如下所示:

select 
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status,
  ...other columns...
from tablename
where active = TRUE;

SQLFiddle示例: http://sqlfiddle.com/#!15/4764d/1

create table test (id int, fullname varchar(100), active boolean);
insert into test values (1, 'test1', FALSE), (2, 'test2', TRUE), (3, 'test3', TRUE);

select
  id,
  fullname,
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status
from test;

| id | fullname | active_status |
|----|----------|---------------|
|  1 |    test1 |         FALSE |
|  2 |    test2 |          TRUE |
|  3 |    test3 |          TRUE |

In PostgreSQL (version 9.4, pgAdmin3), when doing select on a table with boolean column the data output shows 't' or 'f'. I would like to cast/convert booleans as TRUE or FALSE without writing CASE statements or doing JOINS etc.

BTW, according to PostgreSQL own documentation this behavior is not the SQL standard.

The key words TRUE and FALSE are the preferred (SQL-compliant) usage.

PS: This happens only when using the SQL Editor in pgAdmin. Use pgAdmin object browser, drill down to same table, right-click, view data, View Top 100 rows, the same boolean column shows up as TRUE or FALSE, as expected/standard.

解决方案

If all you want to show is the literal TRUE or FALSE, you can use the case statements like you had proposed. Since PostgreSQL treats TRUE, true, yes, on, y, t and 1 as true, I'd control how I'd want the output to look like.

Where clause can be written like:

select * from tablename where active
--or--
select * from tablename where active = true

(My recommendation is the same as PostgreSQL - use true)

When selecting, although there may be hesitation to use the case statements, I'd still recommend doing that to have control over your output string literal.

Your query would look like this:

select 
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status,
  ...other columns...
from tablename
where active = TRUE;

SQLFiddle example: http://sqlfiddle.com/#!15/4764d/1

create table test (id int, fullname varchar(100), active boolean);
insert into test values (1, 'test1', FALSE), (2, 'test2', TRUE), (3, 'test3', TRUE);

select
  id,
  fullname,
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status
from test;

| id | fullname | active_status |
|----|----------|---------------|
|  1 |    test1 |         FALSE |
|  2 |    test2 |          TRUE |
|  3 |    test3 |          TRUE |

这篇关于在Select(PostgreSQL/pgAdmin)中将布尔值返回为TRUE或FALSE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！