4.1　数据类型详解

PostgreSQL提供了丰富的数据类型，因为Greenplum是基于PostgreSQL实现的，所以继承了PostgreSQL全部的数据类型，支持绝大多数我们能想象到的数据类型。此外，用户还可以使用CREATE TYPE命令在数据库中创建新的数据类型。

4.1.1　基本数据类型

Greenplum数据库最基本的数据类型包括数值类型、货币类型、字符类型、日期/时间类型、布尔类型。

1. 数值类型

数值类型由2字节、4字节或8字节的整数以及4字节或8字节的浮点数和可选精度的十进制数组成。表4-1列出了数值类型的取值范围。

2. 货币类型

货币（money）类型存储带有固定小数精度的货币金额。numeric、int和bigint类型的值可以转换为货币类型，不建议使用浮点数来处理货币类型。货币类型的取值范围如表4-2所示。

3. 字符类型

字符类型用于存放字符串和文本内容，表4-3列出了Greenplum数据库支持的字符类型及其长度范围。

4. 日期/时间类型

日期/时间类型包括日期类型、时间类型和日期时间类型。日期类型用于存储日期，时间类型用于存储时间，日期时间类型用于存储时间戳。表4-4列出了Greenplum数据库支持的日期/时间类型及其精确度。

5. 布尔类型

Greenplum数据库支持标准的布尔（boolean）类型。布尔类型有true和false两种状态，未知用NULL表示，布尔类型的存储格式和描述如表4-5所示。

4.1.2　特殊数据类型

特殊数据类型是指有特定用途的数据类型，主要包括枚举类型、几何类型、网络地址类型、位串类型、文本搜索类型、UUID类型、范围类型、对象标识符类型、伪类型。这些特殊数据类型大大扩展了Greenplum数据库的应用范围，使其可以应用在GIS、图计算、机器学习等多个扩展领域。特殊类型延续了PostgreSQL的强大功能，虽然在OLAP中的应用场景不多，但是可以拓宽我们对数据库的认识。

1. 枚举类型

枚举类型是一个可以设定字段可选值集合的数据类型，用于在表定义时限制某个字段的有效值清单。Greenplum中的枚举类型类似C语言中的enum类型。

与其他类型不同的是，枚举类型需要使用CREATE TYPE命令创建。

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

例如创建一周中的几天，命令如下所示。

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

就像其他类型一样，一旦创建，枚举类型可以用于表和函数定义。

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood ------+--------------
 Moe  | happy(1 row)

2. 几何类型

几何数据类型表示二维的平面物体。表4-6列出了Greenplum支持的几何类型，最基本的几何类型是点，它是其他几何类型的基础。

3. 网络地址类型

网络地址类型用于存储IPv4、IPv6、MAC地址等数据类型。用这些数据类型存储网络地址比用纯文本类型好，因为这些类型提供输入错误检查和特殊的操作和功能。网络地址类型及其描述如表4-7所示。

4. 位串类型

位串就是一串由1和0组成的字符串，可用于存储和直观化位掩码。SQL位类型有两种：bit(n)和bit varying(n)，这里的n是一个正整数。

bit类型的数据必须准确匹配长度n，试图存储短一些或者长一些的数据都是错误的。bit varying类型数据是位长度最大为n的变长类型；超长的位串会被拒绝写入。写一个没有长度的bit等效于bit(1)，而没有长度的bit varying表示没有长度限制。

5. 文本搜索类型

文本搜索即通过自然语言文档的集合找到匹配某个查询的检索。Greenplum提供了两种数据类型，用于支持文本搜索，如表4-8所示。

6. UUID类型

UUID类型用来存储RFC 4122、ISO/IEF 9834-8:2005以及相关标准定义的通用唯一标识符UUID（我们可以认为这个数据类型为全球唯一标识符）。这是一个由算法产生的128位标识符，它不可能在已知使用相同算法的模块中和其他方式产生的标识符重复。对于分布式系统而言，这种标识符相比于序列能更好地提供唯一性保证，这是因为序列只能在单一数据库中保证唯一。

UUID被写成一个由小写字母和十六进制数字组成的序列，由分字符分成几组，特别是1组8位数字+3组4位数字+1组12位数字，共32个数字代表128位标识符，标准的 UUID示例如下。

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

7. 范围类型

范围数据类型代表着某一元素类型在一定范围内的值。例如，timestamp范围可能用于代表一间会议室被预订的时间范围。

Greenplum数据库内置的范围类型如下。

1）int4range：整数的取值范围。

2）int8range：bigint类型数据的取值范围。

3）numrange：numeric类型数据的取值范围。

4）tsrange：无指定时区的时间戳对应的范围。

5）tstzrange：有指定时区的时间戳对应的范围。

6）daterange：日期的取值范围。

此外，我们可以自定义范围类型，代码如下所示。

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
-- 包含
SELECT int4range(10, 20) @> 3;
-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- 提取上边界
SELECT upper(int8range(15, 25));
-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);
-- 范围是否为空
SELECT isempty(numrange(1, 5));

范围值必须遵循如下格式。

(下边界,上边界)(下边界,上边界][下边界,上边界)[下边界,上边界]空

方括号和圆括号表示是否包含下边界和上边界。注意最后的格式是空，代表一个空的范围（一个不含有值的范围）。

-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;
-- 不包括3和7，包括二者之间所有点
SELECT '(3,7)'::int4range;
-- 只包括单一值4
SELECT '[4,4]'::int4range;
-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;

8. 对象标识符类型

Greenplum在内部使用对象标识符OID作为各种系统表的主键。同时，系统不会给用户创建的表增加OID系统字段（除非在建表时声明了WITH OIDS或者配置参数default_with_oids设置为开启）。OID类型代表一个对象标识符。除此之外，OID还有几个别名—regproc、regprocedure、regoper、regoperator、regclass、regtype、regconfig和regdictionary。具体描述及示例如表4-9所示。

9. 伪类型

Greenplum类型系统包含一系列特殊用途的条目，属于伪类型。伪类型虽然不能作为字段的数据类型，但是可以用于声明一个函数的参数或者结果类型。伪类型适用于函数不只是简单地接受并返回某种SQL数据类型的情况。所有的伪类型如表4-10所示。

4.1.3　组合数据类型

组合数据类型是指由基本数据类型组合而成的复合数据，包括XML类型、JSON类型和复合类型。

1. XML类型

XML类型用于存储XML数据。向XML类型的字段插入数据时，数据库会对其进行类型安全性检查，确保字段符合格式要求。有时我们也会将XML类型的数据保存在TEXT类型中，这样做可以提高数据插入速度。

XML可以存储按XML标准定义的格式良好的文档，以及由XML标准中的content定义的内容片段。这意味着内容片段可以有多个顶级元素或字符节点。xmlvalue IS DOCUMENT表达式可以用来判断一个特定的XML值是完整的文件还是内容片段。

使用函数xmlparse()解析字符数据产生XML类型的值，代码如下。

demoDB=# SELECT xmlparse(DOCUMENT '<?xml version="1.0"?><book><title>Manual
    </title><chapter>...</chapter></book>');
            xmlparse
----------------------------------------------------------
 <book><title>Manual</title><chapter>...</chapter></book>
(1 row)

demoDB=# SELECT xmlparse(CONTENT 'abc<foo>bar</foo><bar>foo</bar>');
            xmlparse
---------------------------------
 abc<foo>bar</foo><bar>foo</bar>
(1 row)

2. JSON类型

JSON是当前最流行的数据类型。Greenplum可以存储JSON数据，JSON数据也可以存储为TEXT数据类型，使用JSON数据类型更有利于检查每个存储的数值是否是可用的JSON值。

Greenplum有两个函数用于处理JSON数据，具体使用方法如下。

demoDB=# SELECT array_to_json('{{1,5},{99,100}}'::int[]);
  array_to_json
------------------
 [[1,5],[99,100]]
(1 row)

demoDB=# SELECT row_to_json(row(1,'foo'));
    row_to_json
---------------------
 {"f1":1,"f2":"foo"}
(1 row)

3. 数组类型

Greenplum允许将字段定义成不定长度的多维数组。数组类型可以是任何基本类型、用户定义类型、枚举类型或复合类型。

我们可以在创建表的时候声明数组，方式如下。

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]);

pay_by_quarter为整型数组、schedule为二维文本类型数组。

我们也可以使用ARRAY关键字来显式定义数组类型，如下所示。

CREATE TABLE sal_emp (
    name text,
    pay_by_quarter integer ARRAY[4],
    schedule text[][]);

4. 复合类型

复合类型表示一行或者一条记录的结构，实际上它只是一个字段名和它们的数据类型的列表。Greenplum允许像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。下面是定义复合类型的简单示例。

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric);

定义复合类型的语法类似CREATE TABLE，区别在于这里只可以声明字段名字和类型。定义类型之后，我们就可以用它来创建表了。

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);