Greenplum 数据库数据类型
简介
Greenplum 数据库有一套丰富的本地数据类型供用户使用。用户也可以使用 CREATE TYPE 命令定义新的数据类型。本参考手册对所有内置数据类型进行说明。除了这里列出的类型外，还有一些内部使用的数据类型，例如 oid (对象标识符)，但这些不在本手册中介绍。
在 contrib 目录中的可选模块也可能安装新的数据类型。例如 hstore 模块引入了一种新数据类型和 连带函数来与 key-value 键值对一起工作。参见 hstore。 citext 模块添加了一种大小写不敏感的文本数据类型。参见 citext。
下面的数据类型在SQL中使用: bit, bit varying, boolean, character varying, varchar, character, char, date, double precision, integer, interval, numeric, decimal, real, smallint, time (带时区或不带时区), 以及 timestamp (带时区或不带时区)。
每一种数据类型都有一种外部表示格式，这由数据类型的输入函数和输出函数决定。许多内置数据类型有明显的外部格式。然而，几种特别的数据类型要么只存在于 PostgreSQL（以及 Greenplum 数据库），例如 geometric paths，要么有几种可能的表示格式，例如时间和日期类型。一些输入和输出函数是不可逆转的，也就是说，相比较原始输入，输出函数的结果可能存在精度损失。
表 1. Greenplum 数据库内置数据类型
| 名称 | 别名 | 大小 | 范围 | 说明 |
|---|---|---|---|---|
| bigint | int8 | 8 字节 | -9223372036854775808 到 9223372036854775807 | 大范围整数 |
| bigserial | serial8 | 8 字节 | 1 到 9223372036854775807 | 大范围自增型整数 |
| bit [ (n) ] | n 位 | 位串常量 | 定长位串 | |
| bit varying [ (n) ] | varbit | 实际位数量 | 位串常量 | 变长位串 |
| boolean | bool | 1 字节 | true/false, t/f, yes/no, y/n, 1/0 | 逻辑布尔值(true/false) |
| box | 32 字节 | ((x1,y1),(x2,y2)) | 平面内长方形 - 不能在分布键列使用中。 | |
| bytea | 1 字节 + 二进制字符串大小 | 8比特字符 序列 | 变长二进制字符串 | |
| character [ (n) ] | char [ (n) ] | 1 字节 + n | 最长 n 个字符的字符串 | 定长,（输入不足时自动用）空白填充 |
| character varying [ (n) | varchar [ (n) ] | 1 字节 + 字符串大小 | 最长 n 个字符的字符串 | 有限制的变长字符串 |
| cidr | 12 或 24 字节 | IPv4 和 IPv6 网络地址 | ||
| circle | 24 字节 | <(x,y),r> (中心坐标和半径) | 平面内圆 - 不能在分布键列使用中。 | |
| date | 4 字节 | 4713 BC - 294,277 AD | 日历日期 (年, 月, 日) | |
| decimal [ (p, s) ] | numeric [ (p, s) ] | 可变 | 无限制 | 用户指定精度，精度确切 |
| double precision | float8float | 8 字节 | 15位十进制数字精度 | 可变精度，精度不确切 |
| inet | 12 或 24 字节 | IPv4 和 IPv6 主机和网络 | ||
| integer | int, int4 | 4 字节 | -2147483648 to +2147483647 | 常用整数类型 |
| interval [ fields ] [ (p) ] | 16 字节 | -178000000 年到 178000000 年 | 时间跨度(间隔) | |
| json | 1 字节 + json 大小 | 任意长度 json | 不定长（无限制） | |
| jsonb | ??? | ??? | ??? | |
| lseg | 32 字节 | ((x1,y1),(x2,y2)) | 平面内线段 - 不能在分布键列使用中。 | |
| macaddr | 6 字节 | MAC 地址 | ||
| money | 8 字节 | -92233720368547758.08 到 +92233720368547758.07 | 货币数量 | |
| path[1] | 16+16n 字节 | [(x1,y1),…] | 平面内几何路径 - 不能在分布键列使用中。 | |
| point | 16 字节 | (x,y) | 平面内几何点 - 不能在分布键列使用中。 | |
| polygon | 40+16n 字节 | ((x1,y1),…) | 平面内封闭几何路径 - 不能在分布键列使用中。 | |
| real | float4 | 4 字节 | 6 十进制数字精度 | 可变精度，精度不确切 |
| serial | serial4 | 4 字节 | 1 到 2147483647 | 自增型整数 |
| smallint | int2 | 2 字节 | -32768 到 +32767 | 小范围整数 |
| text[1] | 1 字节 + 字符串长度 | 任意长度字符串 | 不定长（无限制） | |
| time [ (p) ] [ without time zone ] | 8 字节 | 00:00:00[.000000] - 24:00:00[.000000] | 一天内的时间 | |
| time [ (p) ] with time zone | timetz | 12 字节 | 00:00:00+1359 - 24:00:00-1359 | 一天内的时间, 带时区 |
| timestamp [ (p) ] [ without time zone ] | 8 字节 | 4713 BC - 294,277 AD | 日期和时间 | |
| timestamp [ (p) ] with time zone | timestamptz | 8 字节 | 4713 BC - 294,277 AD | 日期和时间, 带时区 |
| uuid | 32 字节 | RFC 4122, ISO/IEC 9834-8:2005 标准定义的通用唯一标识(UUID) | ||
| xml[1] | 1 字节 + xml 大小 | 任意长度 xml | 变长，无限制 | |
| txid_snapshot | 用户级事务 ID 快照 |
日期/时间类型
Greenplum 完整支持全部 SQL 日期和时间类型, 参见 Table 1。对于这些数据类型可用的操作说明, 参见 PostgreSQL 文档 日期/时间函数和运算符。 日期根据罗马日历计算，在日历引入之前的年也这样计算 (参见 PostgreSQL 文档 日期单位的历史了解更多信息)。
| 名称 | 存储大小 | 描述 | 最小值 | 最大值 | 精度 |
|---|---|---|---|---|---|
| timestamp [ ( p ) ] [ without time zone ] | 8 字节 | 包括时间和日期 (无时区) | 4713 BC | 294276 AD | 1 微秒 / 14 位数字 |
| timestamp [ ( p ) ] with time zone | 8 字节 | 包括时间和日期 (有时区) | 4713 BC | 294276 AD | 1 微秒 / 14 位数字 |
| date | 4 字节 | 日期 (无一天内的时间) | 4713 BC | 5874897 AD | 1 天 |
| time [ ( p ) ] [ without time zone ] | 8 字节 | 一天内的时间 (无日期和时区) | 00:00:00 | 24:00:00 | 1 微秒 / 14 位数字 |
| time [ ( p ) ] with time zone | 12 字节 | 一天内的时间, 无日期, 有时区 | 00:00:00+1459 | 24:00:00-1459 | 1 微秒 / 14 位数字 |
| interval [ fields ] [ ( p ) ] | 16 字节 | 时间间隔 | -178000000 年 | 178000000 年 | 1 微秒 / 14 位数字 |
Note: SQL 标准要求 timestamp 与 timestamp without time zone 写法一致, Greenplum 实现了这一点。 timestamptz 是 timestamp with time zone 的缩写; 这是 PostgreSQL 的一点扩展。
time, timestamp, 和 interval 接受一个可选的精度值 p ，来指定秒的小数部分数字。默认情况下, 没有明确精度指定。对于 timestamp 和 interval 类型, 允许的 p 值范围为 0 到 6。
Note: 当 timestamp 值按八字节整数存储(当前默认), 精度可全范围(0 到 6 位)任意设置。当 timestamp 值按双精度浮点数存储(一个过时的编译选项), 精度有效值可能小于 6 位。 timestamp 值存储的是从 2000-01-01 午夜开始的秒的数值。 当 timestamp 值按浮点数实现时，2000-01-01 附近的一些年可以达到微秒精度，但是距离这个日期稍远的一些时间精度就会有所降低。注意：浮点存储的 timestamp 值允许更大的日期范围，比上表中给出的要大，即可以表示从 4713 BC 到 5874897 AD 的日期。
同样的编译选项也决定 time 和 interval 类型值存储为浮点数还是八字节整数。 当存储位浮点数的时候, 当时间间隔增大时， interval 的精度有所降低。
对于 time 类型, 使用八字节整数存储时，允许的 p 值范围从 0 到 6；使用浮点数存储时，允许的 p 值范围从 0 到 10。
另外， interval 数据类型还有一个附加选项，可以通过书写以下时间单位后缀( fields )来来限制存储内容:
1 2 3 4 5 6 7 8 9 10 11 12 13 | YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO SECOND |
注意：如果同时指定 fields 与 p 参数, fields 必须包含 SECOND, 因为精度参数 p 只对秒起作用。
time with time zone 类型由 SQL 标准定义， 但是定义给出的属性导致人们怀疑它的有用性。 大部分情况下，复合使用 date, time, timestamp without time zone, 以及 timestamp with time zone 已经提供了任何应用程序所需的完整日期/时间功能。
abstime 和 reltime 是内部使用的低精度数据类型。 不建议你在应用程序中使用; 这些内部数据类型可能在未来的某一个版本中消失。
日期/时间输入
几乎任何合理的日期/时间输入格式都可以被接受。包括 ISO 8601, SQL-兼任格式, 传统 POSTGRES 格式, 等等。对于一些格式, 数据输入中的年、月、日的顺序非常含糊，所以支持指定这些字段的期望顺序。参见 DateStyle 通过参数 MDY 指定 月-日-年 格式, DMY 指定 日-月-年 格式，或者 YMD 指定 年-月-日 格式。
Greenplum 在处理日期/时间输入上比 SQL 标准要求的更加弹性。参见 PostgreSQL 文档 附录 B. 时间/日期支持，了解时间/日期输入的确切解析规则，以及如何识别文本字段，包括月份, 星期和时区。
记住，任何日期或时间字面值需要用单引号引起来, 就像字符串一样. SQL 需要这样的语法
1 | type [ ( p ) ] ' value ' |
这里， p 是一个可选精度参数，指定秒的小数位数。它对以下类型起作用， time, timestamp, 以及 interval 类型。 允许设定的值范围已经在上面文档中说明； 如果没有指定精度，默认为字面值的精度。
日期
Table 2 一些可能的 date 类型输入格式.
| 举例 | 说明 |
|---|---|
| 1999-01-08 | ISO 8601; 1 月 8 日 (推荐格式) |
| January 8, 1999 | 对任何 日期风格 都很明确的输入模式 |
| 1/8/1999 | MDY 模式：1 月 8 日 ; DMY 模式：8 月 1 日 |
| 1/18/1999 | MDY 模式：1 月 18 日; 其他模式：无效 |
| 01/02/03 | MDY 模式：2003 年 1 月 2 日; DMY 模式：2003 年 2 月 1 日; YMD 模式：2001 年 2 月 3 日 |
| 1999-Jan-08 | 任何模式：1999 年 1 月 8 日 |
| Jan-08-1999 | 任何模式：1999 年 1 月 8 日 |
| 08-Jan-1999 | 任何模式：1999 年 1 月 8 日 |
| 99-Jan-08 | YMD 模式：1999 年 1 月 8 日, 其他：错误 |
| 08-Jan-99 | YMD 模式：错误；其他模式：1999 年 1 月 8 日 |
| Jan-08-99 | YMD 模式：错误；其他模式：1999 年 1 月 8 日 |
| 19990108 | ISO 8601; 任何模式：1999 年 1 月 8 日 |
| 990108 | ISO 8601; 任何模式：1999 年 1 月 8 日 |
| 1999.008 | 年和一年中的天 |
| J2451187 | 儒略日期（天文学常用） |
| January 8, 99 BC | 公元前 99 年 1 月 8 日 |
时间
时间类型包括 time [ ( p ) ] without time zone 和 time [ ( p ) ] with time zone。 time 与 time without time zone 等价.
这些类型的有效的输入格式由时间加上一个可选的时区. (参见 Table 3 和 Table 4.) 如果在输入中给 time without time zone 类型指定了时区, 则时区会被忽略。你如果指定了一个日期，也会被忽略，除非你用了包含夏令时的时区，例如 America/New_York. 这种情况下，指定日期是必要的，因为需要决定当前是否是标准时间，还是夏令时时间. 使用 time with time zone 类型时，合适的时区偏移会被记录.
| 举例 | 说明 |
|---|---|
| 04:05:06.789 | ISO 8601 |
| 04:05:06 | ISO 8601 |
| 04:05 | ISO 8601 |
| 040506 | ISO 8601 |
| 04:05 AM | 与 04:05 相同; AM 不影响值 |
| 04:05 PM | 与 16:05 相同; 小时值必须 <= 12 |
| 04:05:06.789-8 | ISO 8601 |
| 04:05:06-08:00 | ISO 8601 |
| 04:05-08:00 | ISO 8601 |
| 040506-08 | ISO 8601 |
| 04:05:06 PST | 时区用缩写指定 |
| 2003-04-12 04:05:06 America/New_York | 时区用全称指定 |
| 举例 | 说明 |
|---|---|
| PST | 缩写 (太平洋标准时间) |
| America/New_York | 时区全称 |
| PST8PDT | POSIX-样式时区格式 |
| -8:00 | ISO-8601 偏移(PST) |
| -800 | ISO-8601 偏移(PST) |
| -8 | ISO-8601 偏移(PST) |
| zulu | 军方缩写(UTC) |
| z | zulu 的短格式 |
参考 时区 了解更多时区格式输入信息.
时间戳
时间戳的有效输入格式由以下几个部分组成：日期，时间，时区(可选), 接着可选的 AD 或 BC. (另外, AD / BC 也可以出现在时区之前, 但不是推荐的顺序.) 因而: 1999-01-08 04:05:06 和: 1999-01-08 04:05:06 -8:00 都是有效的时间戳值, 它们满足 ISO 8601 标准要求. 另外, 常用格式: January 8 04:05:06 1999 PST 也被支持.
SQL 标准中 timestamp without time zone 与 timestamp with time zone 字面值的差异主要体现在时间后面由一个 + 或 - 号标识的时区. 因此, 根据标准, TIMESTAMP ‘2004-10-19 10:23:54’ 是一个 timestamp without time zone 类型字面值, 而 TIMESTAMP ‘2004-10-19 10:23:54+02’ 是一个 timestamp with time zone 类型的字面值. Greenplum 在确定类型之前从不检查字面值内容, 所以上面两个字面值都会被认为是 timestamp without time zone 类型. 为确保当作 timestamp with time zone 类型对待, 请像这样给出明确类型: TIMESTAMP WITH TIME ZONE ‘2004-10-19 10:23:54+02’ 对于一个已经确定为 timestamp without time zone 类型的字面值, Greenplum 会抛弃时区相关信息. 也就是说, 结果时间戳仅仅根据日期/时间确定，不会根据时区进行调整.
对于 timestamp with time zone 类型, 内部值总是按照 UTC (统一协调时间, 通常叫做格林威治时间, GMT) 存储. 带有明确时区的输入值会使用合适的时区偏移转为 UTC 时间。 如果输入字符串中没有指定时区，会假定为当前系统的 TimeZone 参数, 并使用这个参数的偏移转换为 UTC 时间。
当输出一个 timestamp with time zone 类型值, 总是根据当前 timezone 时区值进行转换, 并显示为本地时间. 要想以另一个时区查看时间，要么改变 timezone 设置，要么使用 AT TIME ZONE 构造 (参见 PostgreSQL 文档的AT TIME ZONE).
在 timestamp without time zone 与 timestamp with time zone 间转换通常假定 timestamp without time zone 中的值应该是以 timezone 为时区的本地时间. 当然，也可以用 AT TIME ZONE 指定一个不同的时区.
特殊值
为了方便, Greenplum 支持几种特殊的日期/时间输入值格式, 参见 Table 5. infinity 和 -infinity 值是用于系统内部的特殊表示，会被直接显示; 但其他都只是些快捷记号，会在读取的时候被转换为常规的日期/时间值.(特别地, now 和相关字符串会被转换为读取时的当前特定时间值.) 所有这些常量值在 SQL 命令中都需要用单引号引起来.
| 输入字符串 | 有效类型 | 说明 |
|---|---|---|
| epoch | date, timestamp | 1970-01-01 00:00:00+00 (Unix 系统时间零点) |
| infinity | date, timestamp | 比任何时间戳都晚的时间 |
| -infinity | date, timestamp | 比任何时间戳都早的时间 |
| now | date, time, timestamp | 当前事务的开始时间 |
| today | date, timestamp | 今天午夜 |
| tomorrow | date, timestamp | 明天午夜 |
| yesterday | date, timestamp | 昨天午夜 |
| allballs | time | 00:00:00.00 UTC |
下面这些 SQL-兼容函数也可以用来获取相应类型的当前时间: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. 后面四个可接受一个可选的亚秒级精度参数. (参见 PostgreSQL 文档的当前日期/时间.) 注意 有一些 SQL 函数在输入字符串中 不 被接受.
日期/时间输出
日期/时间类型的输出格式可以被设置为以下四种样式: ISO 8601, SQL (Ingres), 传统 POSTGRES (Unix date 格式), 或者 German. 默认是 ISO 格式. (SQL 标准需要使用 ISO 8601 格式. SQL 输出格式这个名字是由于偶然的历史原因.) Table 6 给出了各种输出格式的例子. date 和 time 类型的输出格式通常为对应例子中的日期或时间部分. 然而, POSTGRES 格式的纯日期输出用的是 ISO 格式.
| 样式规范 | 说明 | 举例 |
|---|---|---|
| ISO | ISO 8601, SQL 标准 | 1997-12-17 07:37:16-08 |
| SQL | 传统样式 | 12/17/1997 07:37:16.00 PST |
| Postgres | 原始样式 | Wed Dec 17 07:37:16 1997 PST |
| German | 区域样式 | 17.12.1997 07:37:16.00 PST |
Note: ISO 8601 规定使用大写字母 T 隔开日期和时间. Greenplum 支持这个输入格式, 但是输出时使用空格而不是 T, 上面您已经看到了. 这是为了可读性，同时与 RFC 3339 以及一些其他数据库系统保持一致.
在 SQL 和 POSTGRES 样式中, 如果指定 DMY 模式，日会出现在月之前; 其他情况下月在日之前. (参见 Table 2 关于这些设置如何影响输入值解释.) Table 7 中的例子.
| datestyle 设置 | 输入顺序 | 输出举例 |
|---|---|---|
| SQL, DMY | day / month / year | 17/12/1997 15:37:16.00 CET |
| SQL, MDY | month / day / year | 12/17/1997 07:37:16.00 PST |
| Postgres, DMY | day / month / year | Wed 17 Dec 07:37:16 1997 PST |
用户可以使用以下方式选择日期/时间样式: SET datestyle 命令, postgresql.conf 配置文件中的 DateStyle 参数, 或者服务器或客户端的 PGDATESTYLE 环境变量.
格式化函数 to_char (参见 数据类型格式化函数) 是一种格式化日期/时间输出的更加灵活的方式.
时区
时区和时区转换，不仅仅是地理位置问题，也受政治决定影响. 从 1900 年以来，世界上的时区稍微标准了一点, 但还是在持续的改变, 尤其是夏令时规则. Greenplum 使用广泛采纳的 IANA (Olson) 时区数据库来定义历史时区规则. 对于未来时间, 假定给定时区的现行已知规则会无限期持续.
Greenplum 保持与 SQL 标准兼容的典型用法定义. 然而, SQL 标准偶尔也有一点混淆时间和日期类型和能力. 两个明显的问题是:
- 虽然 date 类型不能带时区, 但是 time 类型是可以的. 不与日期和时间关联，时区在真实世界意义不大, 因为偏移变化与夏令时边界相关.
- 默认时区是与 UTC 之间差异的常量数值. 因而，当进行跨 DST 边界的日期/时间数值计算时，可能采用夏令时.
为了解决这些困难, 当使用时区的时候，我们推荐用同时包含日期和时间的日期/时间类型. 我们 不 推荐使用 time with time zone 类型(虽然 Greenplum 支持，但主要是为了一个老应用，同时保持与 SQL 标准兼容). 对于任何只包含日期或时间的类型, Greenplum 假定是你的本地时区(的时间或日期).
所有时区感知的日期和时间内部都存储为 UTC 值. 当被显示到客户端时，才通过 TimeZone 配置参数的值转换为指定时区的日期或时间.
Greenplum 允许你以三种不同的形式指定时区:
- 时区全称, 例如 America/New_York. 在 pg_timezone_names 视图里列出了所有可被识别的时区名称. 为了这个目的，Greenplum 使用广泛采纳的 IANA 的时区数据, 因此同样的时区名称也可以被很多其他软件识别.
- 另一个形式时时区缩写, 例如 PST. 对于 时区全称，能够暗示夏令时规则，而时区缩写这个规范只能表示与 UTC 直接的时区偏移. 可识别的缩写列在 pg_timezone_abbrevs 视图中. 你不能给时区缩写设置配置参数 TimeZone 或 log_timezone, 但你可以在时间/日期输入值中通过 AT TIME ZONE 操作符使用缩写.
- 除了时区全称和时区缩写外, Greenplum 也接受 POSIX-风格的时区规范，形如 STD offset 或者 STD offset DST, 这里 STD 是一个时区缩写, offset 是 UTC 往西偏移的小时数值, DST 是一个可选的夏令时时区缩写, 假定为比给定偏移提前一个小时. 举个例子, 如果 EST5EDT 还没有被识别为时区名称, 它能被接受，相当于美国东岸时间(United States East Coast time). 在这种语法中, 时区缩写可以是一串字母, 或者包围在尖括号中的任意字符串( <> ). 当存在夏令时时区缩写时, 假定使用与 IANA 时区数据库中相同的夏令时转换规则. 在标准 Greenplum 安装时, 与 US/Eastern 相同, 因此 POSIX-风格时区规范遵守 USA 夏令时规则. 如果需要，你也可以通过替换文件来调整这个行为.
总之, 这就是时区全称与缩写的不同: 缩写表示与 UTC 之间的偏移, 而全称还暗示了夏令时规则, 因此有两种可能的 UTC 偏移. 例如, 2014-06-04 12:00 America/New_York 表示纽约本地时间中午, 指定为东部夏令时间 Eastern Daylight Time (UTC-4). 因此 2014-06-04 12:00 EDT 指定了同样的时间常数. 但是 2014-06-04 12:00 EST 指定为东部标准时间 (UTC-5) 中午, 不管那天夏令时是否正常生效与否.
另外复杂情况是, 有些管辖范围使用同样的时区缩写表示不同的 UTC 偏移和不同的时间; 例如, 在 MSK 有些年头表示 UTC+3, 有些年头又表示 UTC+4. 对于这些缩写, Greenplum 根据日期来进行解释(对于未来时间，根据最近的情况解释); 但是, 就像上面 EST 这个例子, 这不一定与那个日期的本地时间相同.
应该小心 POSIX-风格时区特征很容易导致错误输入被默默接受, 因为很难检查时区输入的合理性. 例如, SET TIMEZONE TO FOOBAR0 能够正常工作, 让系统有效使用相当特别的 UTC 缩写. 另一个要记住的问题是在 POSIX 时区名称中, 正数被用在(相对于)格林威治时间. 其他任何地方, Greenplum 遵守 ISO-8601 约定, 正时区偏移都是(相对于)格林威治时间.
所有情况中, 失去名称和缩小都是大小写无关的.
不论时区名称还是缩写, 都没有硬编码进服务程序; 我们是从配置文件 .
TimeZone 配置参数可以在文件中设定, 或者其他任何标准设置配置参数的方式. 另外也有一个特殊方式来设置:
- SQL 命令 SET TIME ZONE 可以设置当前会话的时区. 这是比 SET TIMEZONE TO 更符合 SQL 兼容规范的一种形式.
- PGTZ 环境变量被 libpq 客户端用来, 在初始化与服务器的连接时, 发送 SET TIME ZONE 命令.
时间段输入
时间段 值可以被写成下面的详细语法:
1 | @ quantity unit quantity unit... direction |
这里 quantity 是一个有符号数值; unit 是 microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, 或者这些单位的缩写或复数形式; direction 可以是 ago 或者 为空. at 符号 ( @ ) 是一个可选的无用符号. 不同单位的值会被隐式的加在一起(考虑符号和单位). ago 导致所有单位的值都变号. 如果 IntervalStyle 设置为了 postgres_verbose, 这个语法也用于时间段输出.
天, 小时, 分, 和秒的数量都可以不用单位指定. 例如, ‘1 12:59:10’ 与这个 ‘1 day 12 hours 59 min 10 sec’ 是一样的. 另外, 年和月可以通过一个划线(减号)来指定; 例如 ‘200-10’ 读出来与 ‘200 years 10 months’ 一样. (这些短格式事实上是 SQL 标准仅允许的格式. 当 IntervalStyle 设置给 sql_standard 时, 也按这样的格式输出.)
时间段值也可以写成 ISO 8601 时间段的格式, 用标准中的 4.4.3.2 节内容 format with designators, 或者用 4.4.3.3 节说明的 alternative format. format with designators 看起来像这个样子:
1 | P quantity unit quantity unit... T quantity unit... |
这个时间段字符串必须以 P 开头, 可以包含一个 T 来隔开时间部分. 可以使用的缩写列在 Table 8 中. 单位可以忽略, 也可以按任意顺序指定, 但是时间单位必须出现在 T 后面. 特别地, M 的含义依赖于它出现在 T 之前还是之后.
| 缩写 | 含义 |
|---|---|
| Y | 年 |
| M | 月 (在日期部分) |
| W | 周 |
| D | 天 |
| H | 小时 |
| M | 分 (在时间部分) |
| S | 秒 |
另一种格式:
1 | P years - months - days T hours : minutes : seconds |
字符串必须以 P 开头, 用字母 T 隔开时间段的日期和时间部分. 数值按类似于 ISO 8601 日期格式书写.
当书写一个带有 fields 格式的时间段常量, 或者当把一个字符串赋给带有 fields 格式的时间段列时, 没有标志单位的数值根据 fields 解释. 例如 INTERVAL ‘1’ YEAR 读成 1 年, 而 INTERVAL ‘1’ 意味着 1 秒. 另外, 带有 to the right 字段值中 fields 允许的右边最不重要的字段会被简单的忽略. 例如, 写 INTERVAL ‘1 day 2:03:04’ HOUR TO MINUTE 结果或丢弃秒部分, 而不会丢掉天的部分.
根据 SQL 标准, 时间段中所有部分必须有同样的符号, 开头的负号会对所有部分起作用; 例如, 在时间段字面值 ‘-1 2:03:04’ 中, 负号应用于天, 以及小时/分/秒 部分. Greenplum 允许各部分有不同的符号, 并且传统认为文本中每个部分有独立的符号, 因此上面例子中小时/分/秒会被认为是正值. 如果 IntervalStyle 设置给 sql_standard 那么开头的负号就应用到所有部分 (只有当别的地方没有符号时). 要不然, 就用传统 Greenplum 解释. 为了避免模糊, 如果时间段值是负的, 建议每个部分都带上负号.
在详细日期格式中, 对于更紧凑的输入格式的几个部分值可以带小数; 例如 ‘1.5 week’ 或者 ‘01:02:03.45’. 这些输入被转换为月, 天和秒来存储. 当这些导致结果的月或者天有小数时, 小数部分被加到低一级的部分上. 转换规则是 1 月 = 30 天, 1 天 = 24 小时. 例如, ‘1.5 month’ 变成 1 月 15 天. 输出中秒从来不会显示小数部分.
Table 9 这里给出 时间段 输入的有效示例.
| 举例 | 说明 |
|---|---|
| 1-2 | SQL 标准格式: 1 年 2 个月 |
| 3 4:05:06 | SQL 标准格式: 3 天 4 小时 5 分 6 秒 |
| 1 year 2 months 3 days 4 hours 5 minutes 6 seconds | 传统 Postgres 格式: 1 年 2 月 3 天 4 小时 5 分 6 秒 |
| P1Y2M3DT4H5M6S | ISO 8601 format with designators : 同上 |
| P0001-02-03T04:05:06 | ISO 8601 alternative format : 同上 |
内部地 时间段 值被存为月, 天, 和秒. 这是因为一个月中的天数是变化的, 另外因为夏令时的原因, 一天可能有23, 24 或 25 小时. 月和天部分是整数, 秒可以包含小数. 因为时间段通常从常量字符串或 时间戳 相减得来, 大部分情况下这种存储挺好, 但有时也有一些意外: SELECT EXTRACT(hours from ‘80 minutes’::interval); date_part —————- 1 SELECT EXTRACT(days from ‘80 hours’::interval); date_part —————- 0 当超过值的正常范围时, 可以用 justify_days 函数和 justify_hours 函数用来调整天和小时.
时间段输出
时间段类型的输出格式可以设置为以下四种格式之一: sql_standard, postgres, postgres_verbose, 或者 iso_8601, 通过命令 SET intervalstyle 来设置. 默认是 postgres 格式. Table 10 里有每种输出格式的一些例子.
如果时间段值符合标准要求 (年-月 或者 天-时间, 没有混合正负数), sql_standard 样式产生的输出遵从 SQL 标准中对于时间段字面值的规范. 要不然输出看起来像一个标准的 年-月 字面值, 后面跟着一个 天-时间 字面值字符串, 并且标记上明确的符号来消除混合符号带来的歧义.
当 DateStyle 参数设置为 ISO 时, postgres 样式输出与 PostgreSQL 8.4 发布之前的输出一致.
当 DateStyle 参数设置为 非- ISO 时, postgres_verbose 样式的输出与 PostgreSQL 8.4 之前版本的输出一致.
iso_8601 样式与 ISO 8601 标准中 4.4.3.2 节关于 format with designators 的描述一致.
| 样式规范 | 年-月 时间段 | 天-时间 时间段 | 混合时间段 |
|---|---|---|---|
| sql_standard | 1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
| postgres | 1 year 2 mons | 3 days 04:05:06 | -1 year -2 mons +3 days -04:05:06 |
| postgres_verbose | @ 1 year 2 mons | @ 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago |
| iso_8601 | P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |
伪类型
Greenplum 数据库支持一个特殊目的数据类型集合，叫做 伪类型. 一个伪类型不能被用作列数据类型, 但是可以用来声明一个函数的参数或返回值. 当一个函数的行为并不是简单获取或者返回一个特定 SQL 数据类型值的时候，这些伪类型非常有用.
过程语言实现的函数只能使用实现语言中支持的伪类型. 过程与语言全都禁止使用伪类型作为参数类型，仅仅允许 void 和 record 作为返回类型.
一个使用 record 伪类型作为返回数据类型的函数返回一个待定的行数据类型. record 表示可能的匿名复合类型数组. 由于复合数据带有自己的类型标识, 因此不需要额外的数组级信息.
伪类型 void 表示一个函数不返回值.
Note: Greenplum 数据库不支持触发器和 trigger 伪类型.
另外, anyelement, anyarray, anynonarray, 和 anyenum 几个伪类型被称为多态类型. 一些过程语言也支持多态函数使用 anyarray, anyelement, anyenum, 和 anynonarray 函数.
anytable 伪类型是一个 Greenplum 数据库类型, 表示一个二维表表达式 — 评估为一张二维表的表达式. Greenplum 数据库只允许这个类型作为用户定义函数的参数. 参见 二维表表达式 了解更多关于 anytable 伪类型信息.
更多关于伪类型的介绍, 查看 PostgreSQL 文档中 关于 伪数据类型.
Parent topic: Greenplum 数据库数据类型
多态类型
四种特殊目的的数据类型: anyelement, anyarray, anynonarray, 和 anyenum, 被称作 多态 类型. 任何使用这些类型的函数被称做多态函数. 通过在运行时检查实际传递的数据，一个多态函数可以操作许多不同的数据类型.
多态参数和返回值互相关联，当查询调用一个多态函数的时候，真实类型才被决定。每个位置 (或者参数或者返回值) 声明为 anyelement 类型时，允许有任何特定的实际类型, 但是，任何调用中，必须是同一种真实数据类型. 每个位置声明为 anyarray 允许有任何类型的数组, 但同样的，(在一个特定查询中,)必须是同一种数据类型. 如果有位置声明为 anyarray 同时别的位置声明为 anyelement, 在 anyarray 位置中的实际数组类型的元素必须与 anyelement 位置 中的类型相同. anynonarray 与 anyelement 类型同样对待, 但是包含附加的约束就是，实际类型不能是数组类型. anyenum 被当作 anyelement 同样对待, 但是实际类型必须是 enum 类型.
当超过一个参数位置声明为多态类型，最终效果是只允许仅有特定的实际参数组合. 举例说, 函数声明为 equal( anyelement, anyelement ) 则这个函数接受两个输入值，并且他们是同样的数据类型就可以.
当一个函数的返回值声明为多态类型时, 必须有一个参数位置也声明为多态类型, 并且实际参数的数据类型决定调用的实际返回值类型. 举例说, 如果还没有数组订阅机制，你可以像这样定义一个函数实现订阅: subscript( anyarray, integer) returns anyelement . 这里声明约束第一个参数必须是数组类型, 并且允许解析器根据第一个实际参数类型, 推断新的正确的返回值类型. 另一个例子是一个函数声明为 myfunc( anyarray ) returns anyenum 则只能接受一个 enum 类型数组(作为参数).
注意: anynonarray 和 anyenum 不表示单个类型变量; 可以用 anyelement, 只是带一点附加约束. 例如, 声明函数为 myfunc( anyelement, anyenum ) 与声明为 myfunc( anyenum, anyenum ) 是一样的: 两者的实际参数都必须是同样的 enum 类型.
一个可变参数函数 (接收可变数目参数) 中，如果最后的参数声明为 VARIADIC anyarray , 就是多态的. 为了匹配和决定实际参数类型, 这样的函数与你生命合适数量的 anynonarray 参数的函数一样.
了解更多多态类型信息, 参见 PostgreSQL 文档关于 多态参数和返回值.
二维表表达式
当用 anytable 伪类型声明了一个函数参数, 这就是一个二维表表达式. 一个二维表表达式的写法是把 SELECT 语句放在一个 TABLE() 函数中. 你可以通过添加这样的语句来指定表的分布规则: SCATTER RANDOMLY, 或者 SCATTER BY 子句以及列列表指定分布键.
SELECT 语句在函数被调用时执行, 结果集被分布到 segments 节点中, 因此每一个 segment 节点在一部分结果表上执行函数.
举例说, 下面这个表表达式从一个叫做 customer 的表中选了三列，并将分布键设置为第一列:
1 | TABLE(SELECT cust_key, name, address FROM customer SCATTER BY 1) |
SELECT 语句可以包括连接多个基础表, WHERE 语句, 聚集, 以及任何有效的查询语法.
对于 C 或 C++ 语言实现的函数， anytable 类型 是唯一允许的(多态)类型. 函数体可以用 Greenplum 数据库服务器编程接口 (SPI) 或者 Greenplum 伙伴连接器接口 (GPPC) API 访问.
全文搜索类型
Greenplum 数据库提供两种数据类型，这两种数据类型被设计来支持全文搜索，即搜索自然语言 文档 集合并定位与一个 查询 最匹配内容。 tsvector 类型以一种专为全文搜索优化的形式表示一个文档; 类似地， tsquery 类型表示一个文本查询。 使用全文搜索 有这方面支持的详细解释, 文本搜索函数和操作符 总结了相关函数和操作。
tsvector 和 tsquery 类型不能是 Greenplum 数据库表的分布键的一部分。
tsvector
一个 tsvector 值是不同 词素(lexemes) 的排序列表, 也就是由对不同形式的词进行合并，已经被 标准化 的单词组成的列表 (更多细节，请参考 使用全文搜索)。 排序和去重在输入过程中已经自动完成。请看以下例子:
1 2 3 4 | SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector; tsvector ---------------------------------------------------- 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat' |
要表示包含空格和标点符号的词素，可以用单引号把它们包围起来:
1 2 3 4 | SELECT $$the lexeme ' ' contains spaces$$::tsvector; tsvector ------------------------------------------- ' ' 'contains' 'lexeme' 'spaces' 'the' |
(在这个例子中，我们使用了 $$ 引起来的字符串字面值。下一个例子中，我们使用常规的单引号引起来的字符串，所以字面值中嵌入的单引号必须双写进行转义。) 单引号引起来的词素中嵌入的单引号(‘)和反斜杠()必须双写:
1 2 3 4 | SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector; tsvector ------------------------------------------------ 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the' |
可选的, 整数 定位数值 可以被附加到词素:
1 2 3 4 | SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector; tsvector ------------------------------------------------------------------------------- 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4 |
一个定位数值通常表示源单词在文档中的位置。定位信息能够在 邻近排名 时使用。 定位数值的范围可以从 1 到 16383; 更大的数值自动归整到 16383。 相同定位数值的同一词素会被丢弃。
包含定位数值的词素可以进一步使用一个 权重 进行标记, 权重可以是 A, B, C, 或 D 。 D 是默认权重因此在输出中不会显示:
1 2 3 4 | SELECT 'a:1A fat:2B,4C cat:5D'::tsvector; tsvector ---------------------------- 'a':1A 'cat':5 'fat':2B,4C |
典型地，权重被用来反映文档结构, 例如, 可以使用不同权重标记标题与正文中的单词。全文搜索排名函数能够对不同权重标记指派不同优先级。
理解 tsvector 类型本身并不进行任何标准化非常重要; 它假定给它的单词已经为应用进行了适当地标准化。例如,
1 2 3 4 | select 'The Fat Rats'::tsvector; tsvector -------------------- 'Fat' 'Rats' 'The' |
对大多数英文全文搜索应用，上面的单词被认为是非标准化的，但是 tsvector 并不在意。原始文档文本应该总是通过 to_tsvector 函数来为搜索进行适当地标准化:
1 2 3 4 | SELECT to_tsvector('english', 'The Fat Rats'); to_tsvector ----------------- 'fat':2 'rat':3 |
tsquery
一个 tsquery 值为搜索存储词素，同时结合它们实现布尔操作 & (与), | (或), 以及 ! (非)。 括号可以用来表示分组操作:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT 'fat & rat'::tsquery; tsquery --------------- 'fat' & 'rat' SELECT 'fat & (rat | cat)'::tsquery; tsquery --------------------------- 'fat' & ( 'rat' | 'cat' ) SELECT 'fat & rat & ! cat'::tsquery; tsquery ------------------------ 'fat' & 'rat' & !'cat' |
不使用括号时, ! (非) 具有最高优先级, 另外 & (与) 优先级高于 | (或)。
可选地, 一个 tsquery 中的词素可以带一个或多个权重标记字母，用来限制他们只与合适权重的 tsvector 相匹配:
1 2 3 4 | SELECT 'fat:ab & cat'::tsquery; tsquery ------------------ 'fat':AB & 'cat' |
Also, 一个 tsquery 中的词素也可以用星号(*)指定前缀匹配:
1 2 3 4 | SELECT 'super:*'::tsquery; tsquery ----------- 'super':* |
这个查询会匹配一个以 “super” 开头的 tsvector 中的任何单词。 注意前缀会被全文搜索配置首先处理，也就是说这个比较会返回 true (真):
1 2 3 4 5 | SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' ); ?column? ---------- t (1 row) |
因为 postgres 中提取的词干是 postgr :
1 2 3 4 5 | SELECT to_tsquery('postgres:*'); to_tsquery ------------ 'postgr':* (1 row) |
所以就匹配到了 postgraduate 。
词素引用规则与前面 tsvector 中的规则一样; 同时，也和 tsvector 一样, 任何需要标准化的单词必须在转换为 tsquery 类型之前完成。 to_tsquery 函数可以方便地用来进行这类标准化操作:
1 2 3 4 | SELECT to_tsquery('Fat:ab & Cats'); to_tsquery ------------------ 'fat':AB & 'cat' |
范围类型
范围类型是一种数据类型，它代表一种元素类型 (即范围类型的 子类型 ) 的一个范围. 举例说, timestamp 的范围类型可以用来表示一个会议室预定的时间范围. 在这个例子中，数据类型是 tsrange ( “timestamp range” 的缩写), 这里 timestamp 是子数据类型. 子数据类型必须有一个整体顺序，因此很好确定一个元素值是否在范围值域内，范围值域前，还是范围值域后面.
范围类型表示单个范围内的许多元素，所以非常有用, 因为一些概念，像重叠范围，能够用范围类型清晰表达. 为调度目的使用时间和日期是一个更清楚的例子; 对于价格范围, 仪器的测量范围, 等等, 也很有用.
内置范围类型
Greenplum 数据库带有以下内置范围类型:
- int4range — integer 范围
- int8range — bigint 范围
- numrange — numeric 范围
- tsrange — timestamp without time zone 范围
- tstzrange — timestamp with time zone 范围
- daterange — date 范围
除此之外, 你也可以定义你自己的范围类型; 参见 CREATE TYPE 了解更多信息.
举例
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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)); |
参见 范围函数和运算符 查看范围类型的操作符和函数完整列表.
包含边界与非包含边界
每一个非空范围有两个边界, 下边界和上边界. 所有在这两个边界之间的点都包含在范围内. 包含边界意味着边界点本身也包括在范围内, 而非包含边界意思是边界点不包括在范围内.
对于一个范围的文本格式, 包含下边界用 [ 表示, 而非包含下边界用 ( 表示. 同样, 包含上边界用 ] 表示, 而非包含上边界用 ) 表示. (参见 范围函数和运算符 了解更多信息.)
函数 upper_inc 和 lower_inc 分别用来测试一个范围值的上下边界包含关系.
无限 (无边界) 范围
范围的下边界可以忽略, 意味着所有小于上边界的点都包含在范围内. 同样地, 如果范围的上边界被忽略, 那么所有大于下边界的点都包含在范围内. 如果上下边界都忽略了, 那么这个元素类型的所有值都被认为包含在范围内.
可以等价地分别认为, 这个时候下边界是 “负无穷”, 或者上边界是 “正无穷”. 但是请注意, 这些无穷值不是一个范围的元素类型的值, 也不是范围的一部分. (所有无穷边界不存在包含问题 — 如果你这样写的话, 会自动转换为非包含边界.)
有些元素类型也有 “infinity” 的表示, 但那仅仅是一个值, 与范围边界无关. 举例说, 在时间戳范围中, [today,] 与 [today,) 含义相同. 但是 [today,infinity] 与 [today,infinity) 含义就不同了 — 后者排除了特殊的 时间戳 值 infinity.
函数 lower_inf 和 upper_inf 可以分别检测一个范围的无限上边界和下边界.
范围类型输入/输出
范围类型的输入必须遵循下面的模式:
1 2 3 4 5 | ( lower-bound, upper-bound ) ( lower-bound, upper-bound ] [ lower-bound, upper-bound ) [ lower-bound, upper-bound ] empty |
和前面说的一样, 方括号或圆括号表示是否上下边界是包含, 非包含关系. 注意最后一种模式是 empty, 表示一个空范围 (一个不包含任何点的范围).
lower-bound 可以是一个子类型的有效字符串, 或者用 empty 表示没有下边界. 同样地, upper-bound 可以是一个子类型的有效字符串, 或者用 empty 表示没有上边界.
每一个边界值都可以是用 “ (双引号) 引起来的字符串. 如果边界值包含圆括号, 方括号, 逗号, 双引号, 反斜杠的时候, 则必须引起来, 因为这些字符可能是范围语法的一部分. 如果引起来的边界值有双引号或者反斜杠, 需要在前面插入额外的反斜杠进行转义. (在双引号引起来的边界值中两个双引号也用来表示一个双引号字符, 类似于 SQL 字面值中的单引号规则.) 当然, 你也可以避免引号, 只用反斜杠把所有会被认为是范围语法一部分的字符进行转义. 另外, 边界值也可以写成空字符串 “”, 因为啥也不写意味着无限边界.
在范围值的前后, 允许出现空格, 但是任何在圆括号或方括号之间的空格会被认为是上边界或下边界的一部分. (与元素类型有关, 或许有或许没意义.)
例如:
1 2 3 4 5 6 7 8 | -- 包含 3, 不包含 7, 并且包含期间的所有点 SELECT '[3,7)'::int4range; -- 不包含 3 和 7, 单包含之间的所有点 SELECT '(3,7)'::int4range; -- 只包含单个点 4 SELECT '[4,4]'::int4range; -- 不包含任何点 (会被规范化为 'empty') SELECT '[4,4)'::int4range; |
构造范围
每个范围类型有一个与范围类型同名的构造函数. 由于避免了边界值的额外引号(和转义), 通常使用构造函数比写一个范围字面常量更方便. 构造函数接受二或三个参数. 两个参数形式构造一个标准格式的范围 (包含下边界, 不包含上边界), 而三个参数格式构造一个通过第三个参数指定的边界(包含关系). 第三个参数必须是下面的字符串之一: (), (], [), 或者 []. 举例如下:
1 2 3 4 5 6 7 8 9 10 | -- 全格式是: 下边界, 上边界, 以及文本参数指示 -- 包含/非包含边界. SELECT numrange(1.0, 14.0, '(]'); -- 如果第三个参数忽略, 默认是 '[)'. SELECT numrange(1.0, 14.0); -- 虽然这里指定 '(]', 显示值会被转换成经典格式 -- 因为 int8range 是一个离散范围类型 (见下). SELECT int8range(1, 14, '(]'); -- 对某一边界使用 NULL 导致这一边无边界. SELECT numrange(NULL, 2.2); |
离散范围类型
离散范围类型的元素类型有一个明确定义的 “步长”, 例如 integer 或 date. 在这些类型中, 当在两个元素间没有合法的值时, 我们就可以说这两个元素是相邻的. 这个与连续范围相对, 在连续范围中, 两个元素之间总是可以找到其它元素值. 例如, numeric 类型的范围就是连续的, timestamp 类型的范围也是连续的. (即使 timestamp 有有限的精度, 理论上可以认为它是离散的, 但由于步长值通常不重要, 所以最好认为它是连续的.)
另一个理解离散范围类型的方法是, 对于每一个元素值, 它有清晰的 “后一个” 和 “前一个” 值. 了解了这些, 就可以通过选择后一个或前一个元素值, 取代原始元素值, 来对范围边界的包含和非包含关系进行转换. 举例说, 一个整数范围类型 [4,8] 和 (3,9) 表示的是同样值的集合; 但对于一个 numeric 类型的范围就没有这样的关系.
一个离散范围类型应该有一个 canonicalization 函数来感知元素类型的步长. canonicalization 函数用于把等价范围类型值转换为同一表示, 特别是一致的包含或非包含边界. 如果没有指定 canonicalization 函数, 那么不同格式总是当成不相等对待, 即使他们实际上表示同样的值域.
内置范围类型 int4range, int8range, 和 daterange 全部使用一个包含下边界, 不包含上边界的一个经典格式; 即 [). 然而, 用户定义的范围类型可以使用其他转换形式.
定义新的范围类型
用户可以定义他们自己的范围类型. 这样做的通常原因是要使用的子类型的范围没有内置提供. 例如, 定义子类型为 float8 的新的范围类型:
1 2 3 4 5 | CREATE TYPE floatrange AS RANGE ( subtype = float8, subtype_diff = float8mi ); SELECT '[1.234, 5.678]'::floatrange; |
因为 float8 没有 “步长”, 这个例子中我们不能定义一个 canonicalization 函数给它.
定义你自己的范围类型也允许你指定一个不同子类型 B-树 操作类或校对方式(collation)来使用, 可以改变排序规则, 以决定哪些值会落在给定范围之内.
如果子类型被认为是离散值而不是连续值, CREATE TYPE 命令应该指定一个 canonicalization 函数. canonicalization 函数接受一个范围值, 返回一个具有不同边界和格式的等价范围值. 对于表示同一值集的两个范围, canonicalization 函数输出一致., 例如整数范围 [1, 7] 和 [1, 8), 一定是相同的. 不论你选择哪一个作为标准输出, 只要不同格式的等价值总是映射到同样格式的相同值(就可以). 除了调整包含/非包含边界格式, canonicalization 函数还可以圆整(四舍五入)边界值, 例如设定的步长比子类型存储分辨能力宽(就需要这个功能). 例如, 一个 timestamp 范围类型, 可以定义为步长为 1 小时, 这种情况下, canonicalization 函数需要对不是整小时的边界进行圆整, 当然也可以抛出一个异常.
另外, 与 GiST 或 SP-GiST 索引一起使用的范围类型应该定义一个子类型差异函数, 即 subtype_diff, 函数. (没有 subtype_diff 函数索引也能工作, 但可能被认为没有提供这个函数时高效.) 子类型差异函数接受两个子类型输入值, 同时返回他们之间的差 (即, X 减去 Y ) 用 float8 值表示. 在我们上面的例子中, 函数 float8mi 隐含有常规 float8 相减操作来用; 但对于任何其他子类型, 某种类型的转换是必要的. 关于如何表示数值之间差异的一些创造性想法也很需要. 为了最大限度扩展这种可能, subtype_diff 函数应该与选定操作类和校对函数一致; 也就是说, 根据排序顺序, 当第一个参数比第二个大时, 这个函数应该范围正数.
一个关于 subtype_diff 函数不那么简单的例子是:
1 2 3 4 5 6 7 | CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS 'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE; CREATE TYPE timerange AS RANGE ( subtype = time, subtype_diff = time_subtype_diff ); SELECT '[11:10, 23:00]'::timerange; |
参见 CREATE TYPE 了解更多关于创建范围类型的详细信息.
索引
GiST 和 SP-GiST 索引可以在范围类型列上创建. 例如, 创建一个 GiST 索引:
1 | CREATE INDEX reservation_idx ON reservation USING GIST (during); |
一个 GiST 或者 SP-GiST 索引能够加速包含以下操作的范围运算: \=, &&, <@, @>, <<, >>, -|-, &<, 以及 &> (参见 范围函数和运算符 了解更多信息).
另外, B-树 和 hash 索引也可以创建在范围类型的表列上. 对于这些索引类型, 基本上只对相等范围操作有用. 也有一个范围值的 B-树 排序定义, 具有相应的 < 和 > 操作, 但是排序方法相当简单粗暴, 现实生活中并不常用. 范围类型的 B-树 和 hash 支持主要允许排序和查询内部建立 hash, 而不是为了创建实际的索引.
参考
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/3a2bada7c9033f8e.md
