PostgreSQL BOOLEAN

学习目标

理解 PostgreSQL 布尔类型的三种状态：TRUE、FALSE 和 NULL。
掌握布尔类型丰富的字面量输入格式及其隐式转换机制。
学会使用布尔类型设计状态标志位，并编写高效的查询语句。
了解布尔聚合函数 (bool_and, bool_or) 在数据分析中的应用。

引言

PostgreSQL 的布尔 (Boolean) 数据类型是处理逻辑判断的基础。它支持三种状态：TRUE (真)、FALSE (假) 和 NULL (未知)。在存储上，它仅占用 1 个字节，这使得它在处理大规模数据集时的状态标记（如是否激活、是否删除、审核状态等）极其高效。该类型在 SQL 中也可简写为 BOOL。

布尔类型

PostgreSQL 的 BOOLEAN 类型设计非常人性化，它不仅接受标准的 TRUE 和 FALSE，还允许使用多种字符串和数字字面量来表示真假值。这种灵活性极大地简化了数据录入和脚本编写。

有效的字面量值

下表列出了 PostgreSQL 中被识别为 TRUE 和 FALSE 的所有有效字面量：

TRUE (真)	FALSE (假)
`true`	`false`
`'t'`	`'f'`
`'true'`	`'false'`
`'y'`	`'n'`
`'yes'`	`'no'`
`1`	`0`

注意： 无论输入采用何种格式，PostgreSQL 在内部存储时都会将其标准化，并在查询输出时通常显示为 true 或 false (取决于客户端设置)。

实战演练

为了演示布尔类型的使用，我们创建一个名为 bookstore 的示例数据库，并设计一张表来追踪书籍的库存状态。

创建数据库与表

首先，我们定义一个 book_availability 表，其中 available 列使用 BOOLEAN 类型。

-- 创建数据库
CREATE DATABASE bookstore;

-- 切换到该数据库后，创建表
CREATE TABLE book_availability (
    book_id INT NOT NULL PRIMARY KEY,
    available BOOLEAN NOT NULL
);

插入多样化数据

我们将展示如何使用不同的字面量插入数据，验证 PostgreSQL 的隐式转换能力。

INSERT INTO book_availability (book_id, available)
VALUES
    (100, TRUE),          -- 标准布尔值
    (200, FALSE),         -- 标准布尔值
    (300, 't'),           -- 单字符 't'
    (400, '1'),           -- 数字字符串 '1'
    (500, 'y'),           -- 单字符 'y'
    (600, 'yes'),         -- 单词 'yes'
    (700, 'no'),          -- 单词 'no'
    (800, '0');           -- 数字字符串 '0'

查询结果验证

执行查询后，我们可以看到所有输入都被正确解析并标准化存储：

book_id	available
100	true
200	false
300	true
400	true
500	true
600	true
700	false
800	false

解析：
尽管插入时使用了 't', '1', 'yes' 等多种格式，数据库内部统一将其存储为布尔状态。输出时，客户端通常将其显示为小写的 true 或 false。

查询示例

查询所有在售书籍

我们可以使用任何代表 "真" 的字面量来过滤数据。虽然内部存储是布尔值，但 SQL 解析器足够智能，能识别字符串比较。

SELECT *
FROM book_availability
WHERE available = 'yes';

结果说明：
此查询将返回所有 available 状态为真的书籍（即 book_id 为 100, 300, 400, 500, 600 的记录）。PostgreSQL 会自动将 'yes' 转换为布尔真值进行匹配。

查询所有缺货书籍

同理，我们可以使用代表 "假" 的字面量来查找缺货书籍。

SELECT *
FROM book_availability
WHERE available = 'no';

结果说明：
此查询将返回所有 available 状态为假的书籍（即 book_id 为 200, 700, 800 的记录）。

使用 NOT 操作符

在布尔逻辑中，直接使用 NOT 操作符是最简洁且性能最优的方式，无需关心具体的字面量表示。

SELECT *
FROM book_availability
WHERE NOT available;

结果说明：
此查询逻辑等同于 WHERE available = FALSE。它将返回所有状态为假的记录。这种方式代码可读性最强，也是推荐的最佳实践。

技术要点

在使用 PostgreSQL 布尔类型时，请牢记以下核心特性：

极致节省空间：布尔类型仅占用 1 字节 存储空间。在处理亿级数据的状态标记时，相比使用整数或字符串，能显著节省磁盘空间和内存缓存。
输入高度灵活：支持 't', 'f', 'true', 'false', 'y', 'n', 'yes', 'no', 1, 0 等多种字面量。这减少了数据清洗的工作量。
隐式与显式转换：PostgreSQL 可以自动将兼容的值隐式转换为布尔类型。如果需要，也可以使用 ::boolean 或 CAST(value AS boolean) 进行显式转换。
```
-- 显式转换示例
SELECT 'y'::boolean;  -- 结果为 true
SELECT 0::boolean;    -- 结果为 false
```
专用聚合函数：PostgreSQL 提供了专门针对布尔类型的聚合函数，常用于数据分析：
- bool_and(): 当所有输入值都为 TRUE 时返回 TRUE，否则返回 FALSE。
- bool_or(): 只要有一个输入值为 TRUE 就返回 TRUE。
```
-- 检查某批次书籍是否全部有货
SELECT bool_and(available) FROM book_availability WHERE book_id BETWEEN 100 AND 200;
```