如何在PostgreSQL中创建4VL "boolean"类型？

Question

我正在寻找一种类型，根据正常的布尔类型，NULL（也根据现有实现，Codd的"A值"或适用的未知）和NA（Codd的"I-Value"或不适用）同时具有TRUE和FALSE。

示例：假设您有一组针对患者的医学测试作为 relvar 中的属性，患者作为元组。尚未采取的测试结果具有NULL（"我们还不知道"）的值，但女性患者永远不会测试睾丸癌，因此该值应为NA（"不适用于该患者"）。

我目前正在通过 ENUM 自定义类型（3VL，因为我假设 NULL 将继续工作而不会被声明）：

CREATE TYPE triplebool AS ENUM ('true', 'false', 'na');

但是，它需要将现有布尔值转换为文本，然后转换为三布尔值：

SELECT has_taken_test::text::triplebool FROM test_record

这既不优雅，又意味着我必须使用文本值（'true'、'false'、'na'），而不是 Postgres 真正的布尔类型（TRUE、t、1 等 + 所有标准操作）提供的灵活性。

我想要的是看起来像（伪代码）的东西：

CREATE TYPE triplebool AS (Boolean || 'na')

以及定义包含"na"值的真值表的能力。

Answer 1

您不能以这种方式扩展（或创建新的扩展类型）boolean或任何其他标量类型。但是您有 2 种选择：

1. 创建一个enum并增强它。

就像你尝试过的那样，你可以创建一个enum（排序很重要：它将决定它应该如何按order by、索引等排序）。

create type triplebool as enum ('false', 'na', 'true');

您可以通过定义一些自定义强制转换来简化转换：

-- sample casts: from and to boolean
create function bool2triplebool(boolean)
  returns triplebool
  immutable
  strict
  language sql
as $func$
  select case $1
    when false then 'false'::triplebool
    when true  then 'true'::triplebool
  end
$func$;
create function triplebool2bool(triplebool)
  returns boolean
  immutable
  strict
  language sql
as $func$
  select case $1
    when 'false' then false
    when 'true'  then true
  end
$func$;
-- use implicit instead of assignment, if you want to
-- use this cast automatically in any expression,
-- not just in column assigments (in INSERT & UPDATE statement)
create cast (boolean as triplebool)
  with function bool2triplebool(boolean)
  as assignment;
-- this should be explicit (default)
create cast (triplebool as boolean)
  with function triplebool2bool(triplebool);

如果需要，您可以模拟boolean的一些标准操作。您可以创建自定义函数和运算符来实现这一点：

-- sample operator: and
create function triplebool_and(triplebool, triplebool)
  returns triplebool
  immutable
  called on null input -- important, if you want to use null as the 4th "value"
  language sql
as $func$
  select res
  from (values (null::triplebool, null::triplebool, null::triplebool),
               (null,    'false', null   ),
               (null,    'na',    'na'   ),
               (null,    'true',  null   ),
               ('false', null,    null   ),
               ('false', 'false', 'false'),
               ('false', 'na',    'na'   ),
               ('false', 'true',  'false'),
               ('na',    null,    'na'   ),
               ('na',    'false', 'na'   ),
               ('na',    'na',    'na'   ),
               ('na',    'true',  'na'   ),
               ('true',  null,    null   ),
               ('true',  'false', 'false'),
               ('true',  'na',    'na'   ),
               ('true',  'true',  'true' )) t(lop, rop, res)
  where lop is not distinct from $1
    and rop is not distinct from $2
        -- "is [not] distinct from" can handle null values too
$func$;
create operator && (
  leftarg    = triplebool,
  rightarg   = triplebool,
  procedure  = triplebool_and,
  commutator = && -- for joins
);

但是，如您所见，实现boolean类型的几乎所有功能将需要大量工作。它有一个严重的限制：你不能改变enum类型的输入和/或输出函数（至少，以可靠的方式;你可以开始搞砸pg_type，但我不确定会发生什么，你的更改可能无法导出/转储，等等）。这意味着，您只能使用定义的值（false，na&true）进行输入，不能使用别名（如f，1带有boolean）（从和到text转换是一个完全不同的故事，甚至可能与类型的IO函数不一致）。

SQLFiddle

2. 创建真正的用户定义类型。

使用此选项，您可以创建真正的 4VL 布尔类型，但它需要付出代价，它将比enum选项做更多的工作。这些类型通常用C编写（或用一种语言编写，该语言与PostgreSQL的源代码有适当的绑定）。你不能用sql和plpgsql来写这些低级的IO函数。

+1.重新设计表架构（稍微一点）

虽然上述这些解决方案可以工作，但它们会使您的数据无法移植。此外，如果您需要另一列，哪个值取决于这些行是否适用（如测试的日期），这些新列是否也需要 n/a 状态？在我看来，不是。该基准面应位于不同的列中：

create table med_test (
   -- ...
   is_applicable boolean not null,
   result        boolean,
   taken_at      timestamp with time zone
);

如果您查看上表中的一行，则可以清楚地区分您的案例：

• is_applicable is false表示它不适用，
• is_applicable is true and result is null意味着它还没有采取

您可以选择添加完整性检查，以避免不适用但采取的状态：

alter table med_test
  add check (is_applicable is true or result is null);
alter table med_test
  add check (is_applicable is true or taken_at is null);