[译] 自定义数据类型实践

原创多米爸比 2023-02-15

896

原文标题：Intro to Postgres Custom Data Types
原文地址：https://www.crunchydata.com/blog/intro-to-postgres-custom-data-types
原文作者：Elizabeth Christensen
翻译：多米爸比

在PostgreSQL中自定义数据类型有两种主要方法：

创建DOMAIN域对象，基于内置数据类型的值进行约束
创建用户自定义的数据类型

创建自定义数据类型之前，我们先检查数据库现有内置的多种数据类型是否确定不适用！

手把手实操教程

我的同事Jean-Paul Agudo大佬编写了两个实操教程，一个基于DOMAIN，另一个基于自定义数据类型。

使用CREATE DOMAIN

DOMAIN允许我们创建指定类型值的check约束。

例如，确保出生日期都超过1930年1月1日，确保电子邮件地址有效，可以创建下面两个DOMAIN对象：

CREATE DOMAIN date_of_birth AS date
	CHECK (value > '1930-01-01'::date)
;

CREATE DOMAIN valid_email AS text
	NOT NULL
	CHECK (value ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
;
复制

现在创建表时，可以分配date_of_birth和valid_email数据类型给相应的列：

CREATE TABLE person_using_domains
  (
     id         INTEGER GENERATED always AS IDENTITY PRIMARY KEY,
     firstname  TEXT NOT NULL,
     lastname   TEXT NOT NULL,
     birth_date DATE_OF_BIRTH,
     email      VALID_EMAIL
  );
复制

DOMAIN对象在psql里可以通过\dD进行查询。

DOMAIN对象与CHECK约束的区别

CHECK约束修改不方便，需要先被丢弃然后重新创建。

DOMAIN对象可以在schema级别创建，可以控制多个字段，并可以被其他多个表使用，便于集中管理。

假如业务发生变更，现在出生日期只想存储1980年之后出生的人，可以使用ALTER DOMAIN进行修改：

ALTER DOMAIN date_of_birth ADD CHECK (value > '1980-01-01'::date);
复制

使用CREATE TYPE

使用CREATE TYPE时，有几种形式：

复合类型：由少量字段构成的单一类型
枚举类型：不重复的常量集合
范围类型：一组范围系列值

复合类型

当我们将几个不同的值组合到单个字段中时，会使用复合类型。

下面，CREATE TYPE将创建一个新的数据类型，允许在单个字段中存储高度、宽度和重量：

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或者使用::双冒号操作符

INSERT INTO packages
            (properties)
VALUES      ( '(10.3,4.0,0.5)' :: physical_package ),
            ( '(5,3.0,0.2)' :: physical_package );
复制

查询复合类型时，使用.点号进行操作，复合类型名称需要使用括号嵌套引用

SELECT id, (properties).weight FROM packages;

 id | weight
----+--------
  1 |    0.5
  2 |    0.2
复制

复合类型与JSON

复合类型看起来与JSON很像，但复合类型结构更正式，更众所周知一些，而JSON的结构性会差一些。

PostgreSQL对复合类型的性能可能更高一些。因此，如果我们关心结构性和性能，复合类型可能是适合的解决方案。然而JSON更加流行，更加受欢迎，未来的发展更广阔。

枚举类型

当我们想在PostgreSQL中创建一组静态常量值时，可以使用CREATE TYPE AS ENUM语法。

如果我们想要新建一个打包分类的新字段，存储box、letter等类别，可以使用如下语句：

CREATE TYPE package_cat AS ENUM ('box','letter');
复制

稍后，我们可以继续添加一个新类别

ALTER TYPE package_cat ADD VALUE 'postcard';
复制

枚举类型注意事项

Craig Kerstiens写过一篇很好的枚举与CHECK约束入门文章，结论是CHECK约束要好一点。PostgreSQL允许向枚举类型添加值，但不能从枚举类型中删除值，只能重新创建类型。

范围类型

PostgreSQL中有几种范围数据类型，包括整数、大整数、数字、带时区或不带时区的日期和时间。创建新的范围类型可以处理其他范围子类型。

例如在送货场景中，客户接受一些延迟交货(范围可以为3天或过夜转天等)的价格折扣。让我们使用间隔类型interval来创建新的范围类型：

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
);
复制