原 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 类型.
