PostgreSQL 自定义数据类型：用户定义类型

为什么要创建自定义数据类型？

自定义数据类型主要帮助你保证数据完整性，使数据库中的数据符合预期；它还常常能带来维护上的便利。

本教程通过一个简单的业务场景说明这些优势，分为两个部分：

第 1 部分介绍 DOMAIN 的使用。
本篇是第 2 部分，介绍自定义 TYPE 的使用。

CREATE TYPE 命令

如果读过第 1 部分，就会发现 CREATE TYPE 更复杂。若要在一篇教程中涵盖其全部细节，篇幅会非常大。先看它的语法定义：

Command:     CREATE TYPE
Description: define a new data type
Syntax:
CREATE TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )

CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] )

CREATE TYPE name AS RANGE (
    SUBTYPE = subtype
    [ , SUBTYPE_OPCLASS = subtype_operator_class ]
    [ , COLLATION = collation ]
    [ , CANONICAL = canonical_function ]
    [ , SUBTYPE_DIFF = subtype_diff_function ]
    [ , MULTIRANGE_TYPE_NAME = multirange_type_name ]
)

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , TYPMOD_IN = type_modifier_input_function ]
    [ , TYPMOD_OUT = type_modifier_output_function ]
    [ , ANALYZE = analyze_function ]
    [ , SUBSCRIPT = subscript_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
)

相关文档：PostgreSQL CREATE TYPE。

四种形式

用户定义类型有四种形式：

复合类型：由属性名及其数据类型列表定义。
枚举类型：由一组带引号的标签构成。
范围类型：用于定义灵活的取值范围。
全新的标量基础类型：自行实现数据库正确处理该类型所需的所有功能。

第四种形式内容庞杂，本教程不展开。创建基础类型还需要超级用户权限；PostgreSQL 文档指出，错误的类型定义可能使服务器混乱，甚至崩溃。

复合类型

假设应用管理一个递送系统，负责投递信件或包裹。应用绝大多数地方只需要包裹 ID，尺寸和重量等物理属性则主要用于传给判定包裹类别的函数。

设定尺寸阈值为高 10、宽 13（英寸或其他单位），重量阈值为 18（盎司或其他单位）。下面的实现中，任一维度超过阈值就归为箱件 box，否则归为信件 letter。

创建一个包含两个尺寸和重量的复合类型，再创建使用该类型的 packages 表：

create type physical_package as (
    height    numeric
  , width     numeric
  , weight    numeric
);

create table packages (
	id            bigint  generated always as identity primary key
, properties    physical_package
);

现在可以用 :: 运算符（或 cast()）将格式正确的数据转换为 physical_package。例如，可以用易读的形式插入数据：

insert into
packages
  (
     properties
  )
values
  (
     '(10.3,4.0,0.5)'::physical_package
  ),
  (
     '(5,3.0,0.2)'::physical_package
  ),
  (
     '(100,200,400)'::physical_package
  ),
  (
     '(4,10,50)'::physical_package
  ),
  (
     '(12,10,100)'::physical_package
  ),
  (
     '(3.5,5,3.5)'::physical_package
  );

访问复合类型的字段时，不能直接写成 my_tape_name.my_sub_type 这样的表达式，否则 PostgreSQL 可能将点号前的部分当成表名。要像下面这样，用括号包住复合值表达式：

select id,(properties).weight from packages;

复合类型的一大优势是既可以作为函数参数，也可以作为返回值。下面创建一个函数，判断包裹是信件还是箱件：

create function categorize_package (
   p  physical_package
) returns text
as
$$
  select
    case when (
         case when (p).height>10.0 then true else false end
      or case when (p).width >13.0 then true else false end
      or case when (p).weight>18.0 then true else false end)
    then
			'box'
		else
			'letter'
		end
  ;
$$ language sql;

可以看到，自定义类型简化了函数的参数列表。

这个 SQL 函数对三个 case when 的结果进行逻辑或运算。只要其中一个为真，最外层条件就为真，分类为 box；否则为 letter。

借助该函数，一条查询就能为每个包裹分类：

select id, properties, categorize_package(properties) from packages;

也可以结合聚合函数做统计：

select categorize_package(properties), count(*) from packages group by 1;

还可以在 WHERE 子句中使用它，找出所有信件：

select id, properties from packages where categorize_package(properties)='letter';

枚举类型

要在 PostgreSQL 中使用许多编程语言称为 enum 的枚举类型，需要通过 CREATE TYPE 创建。例如，仅为一个 domain 设置一些默认值并不会创建枚举类型。

先定义包裹类别枚举，每个值只能是箱件或信件：

create type package_cat as enum ('box','letter');

然后修改 categorize_package()。如果前一步已创建该函数，需要先删除它，再用新的返回类型重新创建：

drop function categorize_package;
create function categorize_package (
   p  physical_package
) returns package_cat
as
$$
  select
    case when (
      case when (p).height>10.0 then true else false end or
      case when (p).width>13.0 then true  else false end or
      case when (p).weight>18.0 then true else false end
    ) then 'box'::package_cat else 'letter'::package_cat end
  ;
$$ language sql;

这里需要注意两点：

函数可以返回我们创建的类型，不限于 PostgreSQL 提供的基础类型。
返回的 box 和 letter 都显式转换为 package_cat；否则与函数声明的 returns package_cat 不匹配。

这些操作看起来可能有些繁琐，但它们确保数据始终符合约定。假设应用新增一个具有特殊物理属性的明信片类别 postcard，只需向 package_cat 枚举添加这个值：

alter type package_cat add value 'postcard';

PostgreSQL 允许为枚举添加值，却不能直接删除已有枚举值。如果需要删除，通常要：

将现有枚举类型重命名，例如改成 my_enum_old。
使用要保留的值创建新类型，例如 my_enum。
通过 ALTER TABLE … ALTER COLUMN … TYPE my_enum … 将相关列迁移到新类型。
删除旧枚举类型。

查看枚举所有成员的一种简单方法是执行 dT+，检查结果中的 Elements 列：

\dT+ package_cat

接下来可以按明信片的物理属性调整 categorize_package()，使它能识别明信片；本教程不再展开这个步骤。

现在创建另一张表，添加类型为 package_cat 的列，并禁止相关列出现空值：

create table packages_with_category (
		id            bigint  generated always as identity primary key
	, properties    physical_package  not null
	, category      package_cat       not null
);

插入数据：

insert into
packages_with_category
  (
     properties
    ,category
  )
values
  (
     '(10.3,4.0,0.5)'::physical_package
    ,'box'::package_cat
  ),
  (
     '(5,3.0,0.2)'::physical_package
    ,'letter'::package_cat
  ),
  (
     '(100,200,400)'::physical_package
    ,'box'::package_cat
  ),
  (
     '(4,10,50)'::physical_package
    ,'box'::package_cat
  ),
  (
     '(12,10,100)'::physical_package
    ,'box'::package_cat
  ),
  (
     '(3.5,5,3.5)'::physical_package
    ,'postcard'::package_cat
  );

按预期，尝试插入不属于枚举的值会失败：

insert into
packages_with_category
  (
     properties
    ,category
  )
values
  (
     '(6,6,6)'::physical_package
    ,'stuff'::package_cat
  );

与 domain 类似，这类自定义类型能约束数据符合预期，从而保证数据质量。

范围类型

范围类型在 PostgreSQL 中已存在多年，但内置范围类型的子类型主要限于整数、大整数、数值、带或不带时区的时间戳以及日期。

因此，自定义范围类型的常见用途是支持其他子类型。

假设客户愿意通过放宽包裹的送达时限换取折扣。例如，运输公司尽力配送可在 3 天内送达，但客户也能接受最多 10 天。可以定义以时间间隔为子类型的范围：

create type delay as range (
	subtype = interval
);

创建一张新表，同时使用前面创建的自定义类型与这个新范围类型：

create table packages_with_delay (
		id                bigint generated always as identity primary key
	, properties        physical_package          not null
	, category          package_cat               not null
  , acceptable_delay  delay                     not null
);

插入数据：

insert into
packages_with_delay
  (
     properties
    ,category
    ,acceptable_delay
  )
values
  (
     '(10.3,4.0,0.5)'::physical_package
    ,'box'::package_cat
    ,'[3 hours,3 days]'::delay
  ),
  (
     '(5,3.0,0.2)'::physical_package
    ,'letter'::package_cat
    ,'[3 days, 10 days]'
  ),
  (
     '(100,200,400)'::physical_package
    ,'box'::package_cat
    ,'[5 days, 30 days]'
  ),
  (
     '(4,10,50)'::physical_package
    ,'box'::package_cat
    ,'[1 day, 10 days]'
  ),
  (
     '(12,10,100)'::physical_package
    ,'box'::package_cat
    ,'[3 hours, 2 days]'
  ),
  (
     '(3.5,5,3.5)'::physical_package
    ,'postcard'::package_cat
    ,'[3 days, 1 month]'
  );

例如，今天有一批货物发出，预计 1～2 天内送达。哪些包裹可以加入这批货物？

select
    id
   ,acceptable_delay
from
   packages_with_delay
where
   acceptable_delay @> '[1 day,2 day]'::delay;

另有一艘正在装货的船，受海况影响，预计 20 天至 1 个月后到达。哪些包裹适合上船？

select
    id
   ,acceptable_delay
from
   packages_with_delay
where
   acceptable_delay @> '[20 days,1 month]'::delay;

使用范围类型时，要分清重叠与包含运算符。重叠使用 &&：

select
    id
   ,acceptable_delay
from
   packages_with_delay
where
   acceptable_delay && '[6 days,1 month]'::delay;

包含使用 @>；如果左右操作数交换，则使用 <@：

select
    id
   ,acceptable_delay
from
   packages_with_delay
where
   acceptable_delay @> '[6 days,1 month]'::delay;

仔细比较这两条查询的结果。更多信息请参阅 PostgreSQL 文档中的范围运算符表。

结语

Craig Kerstiens 的相关博客中有一个有趣的安排：真正的结语之前，就先提出了总结性问题：

何时使用这些类型，用来做什么？

我们已经了解了它们的价值，那么实际什么时候应该使用？

在作者看来，枚举有助于提高数据质量，但不宜滥用，尤其不适合很长的取值列表。例如，当枚举超过 10 个值时，作者更倾向于用一张表保存这些值，再通过引用完整性约束将源表与该表关联起来。

如果大量使用函数，或需要某种形式的反规范化，复合类型会很方便。有时也可以将其作为表中元组的“先前版本”，类似于用 tablename_hist 等历史表保存变更记录的做法。

自定义范围类型的使用可能相对较少，因为内置范围子类型通常已能满足需求。

PostgreSQL 中 JSON(B) 的兴起，改变了人们对早已受支持的复合类型和数组的使用方式，也带来了新的应用场景。

文章版权归作者所有，未经允许请勿转载。

THE END

技术译文

PostgreSQL 自定义数据类型：用户定义类型

PostgreSQL 自定义数据类型：用户定义类型

为什么要创建自定义数据类型？

CREATE TYPE 命令

四种形式

复合类型

枚举类型

范围类型

结语

请登录后发表评论