Greenplum 数据库数据类型

0    45    2

Tags:

👉 本文共约26250个字,系统预计阅读时间或需99分钟。

简介

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 数据库内置数据类型

名称别名大小范围说明
bigintint88 字节-9223372036854775808 到 9223372036854775807大范围整数
bigserialserial88 字节1 到 9223372036854775807大范围自增型整数
bit [ (n) ]n位串常量定长位串
bit varying [ (n) ]varbit实际位数量位串常量变长位串
booleanbool1 字节true/false, t/f, yes/no, y/n, 1/0逻辑布尔值(true/false)
box32 字节((x1,y1),(x2,y2))平面内长方形 - 不能在分布键列使用中。
bytea1 字节 + 二进制字符串大小8比特字符 序列变长二进制字符串
character [ (n) ]char [ (n) ]1 字节 + n最长 n 个字符的字符串定长,(输入不足时自动用)空白填充
character varying [ (n)varchar [ (n) ]1 字节 + 字符串大小最长 n 个字符的字符串有限制的变长字符串
cidr12 或 24 字节IPv4 和 IPv6 网络地址
circle24 字节<(x,y),r> (中心坐标和半径)平面内圆 - 不能在分布键列使用中。
date4 字节4713 BC - 294,277 AD日历日期 (年, 月, 日)
decimal [ (p, s) ]numeric [ (p, s) ]可变无限制用户指定精度,精度确切
double precisionfloat8float8 字节15位十进制数字精度可变精度,精度不确切
inet12 或 24 字节IPv4 和 IPv6 主机和网络
integerint, int44 字节-2147483648 to +2147483647常用整数类型
interval [ fields ] [ (p) ]16 字节-178000000 年到 178000000 年时间跨度(间隔)
json1 字节 + json 大小任意长度 json不定长(无限制)
jsonb?????????
lseg32 字节((x1,y1),(x2,y2))平面内线段 - 不能在分布键列使用中。
macaddr6 字节MAC 地址
money8 字节-92233720368547758.08 到 +92233720368547758.07货币数量
path[1]16+16n 字节[(x1,y1),…]平面内几何路径 - 不能在分布键列使用中。
point16 字节(x,y)平面内几何点 - 不能在分布键列使用中。
polygon40+16n 字节((x1,y1),…)平面内封闭几何路径 - 不能在分布键列使用中。
realfloat44 字节6 十进制数字精度可变精度,精度不确切
serialserial44 字节1 到 2147483647自增型整数
smallintint22 字节-32768 到 +32767小范围整数
text[1]1 字节 + 字符串长度任意长度字符串不定长(无限制)
time [ (p) ] [ without time zone ]8 字节00:00:00[.000000] - 24:00:00[.000000]一天内的时间
time [ (p) ] with time zonetimetz12 字节00:00:00+1359 - 24:00:00-1359一天内的时间, 带时区
timestamp [ (p) ] [ without time zone ]8 字节4713 BC - 294,277 AD日期和时间
timestamp [ (p) ] with time zonetimestamptz8 字节4713 BC - 294,277 AD日期和时间, 带时区
uuid32 字节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 BC294276 AD1 微秒 / 14 位数字
timestamp [ ( p ) ] with time zone8 字节包括时间和日期 (有时区)4713 BC294276 AD1 微秒 / 14 位数字
date4 字节日期 (无一天内的时间)4713 BC5874897 AD1 天
time [ ( p ) ] [ without time zone ]8 字节一天内的时间 (无日期和时区)00:00:0024:00:001 微秒 / 14 位数字
time [ ( p ) ] with time zone12 字节一天内的时间, 无日期, 有时区00:00:00+145924:00:00-14591 微秒 / 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 )来来限制存储内容:

注意:如果同时指定 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 需要这样的语法

这里, p 是一个可选精度参数,指定秒的小数位数。它对以下类型起作用, time, timestamp, 以及 interval 类型。 允许设定的值范围已经在上面文档中说明; 如果没有指定精度,默认为字面值的精度。

日期

Table 2 一些可能的 date 类型输入格式.

举例说明
1999-01-08ISO 8601; 1 月 8 日 (推荐格式)
January 8, 1999对任何 日期风格 都很明确的输入模式
1/8/1999MDY 模式:1 月 8 日 ; DMY 模式:8 月 1 日
1/18/1999MDY 模式:1 月 18 日; 其他模式:无效
01/02/03MDY 模式: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-08YMD 模式:1999 年 1 月 8 日, 其他:错误
08-Jan-99YMD 模式:错误;其他模式:1999 年 1 月 8 日
Jan-08-99YMD 模式:错误;其他模式:1999 年 1 月 8 日
19990108ISO 8601; 任何模式:1999 年 1 月 8 日
990108ISO 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 3Table 4.) 如果在输入中给 time without time zone 类型指定了时区, 则时区会被忽略。你如果指定了一个日期,也会被忽略,除非你用了包含夏令时的时区,例如 America/New_York. 这种情况下,指定日期是必要的,因为需要决定当前是否是标准时间,还是夏令时时间. 使用 time with time zone 类型时,合适的时区偏移会被记录.

举例说明
04:05:06.789ISO 8601
04:05:06ISO 8601
04:05ISO 8601
040506ISO 8601
04:05 AM与 04:05 相同; AM 不影响值
04:05 PM与 16:05 相同; 小时值必须 <= 12
04:05:06.789-8ISO 8601
04:05:06-08:00ISO 8601
04:05-08:00ISO 8601
040506-08ISO 8601
04:05:06 PST时区用缩写指定
2003-04-12 04:05:06 America/New_York时区用全称指定
举例说明
PST缩写 (太平洋标准时间)
America/New_York时区全称
PST8PDTPOSIX-样式时区格式
-8:00ISO-8601 偏移(PST)
-800ISO-8601 偏移(PST)
-8ISO-8601 偏移(PST)
zulu军方缩写(UTC)
zzulu 的短格式

参考 时区 了解更多时区格式输入信息.

时间戳

时间戳的有效输入格式由以下几个部分组成:日期,时间,时区(可选), 接着可选的 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 命令中都需要用单引号引起来.

输入字符串有效类型说明
epochdate, timestamp1970-01-01 00:00:00+00 (Unix 系统时间零点)
infinitydate, timestamp比任何时间戳都晚的时间
-infinitydate, timestamp比任何时间戳都早的时间
nowdate, time, timestamp当前事务的开始时间
todaydate, timestamp今天午夜
tomorrowdate, timestamp明天午夜
yesterdaydate, timestamp昨天午夜
allballstime00: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 格式.

样式规范说明举例
ISOISO 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, DMYday / month / year17/12/1997 15:37:16.00 CET
SQL, MDYmonth / day / year12/17/1997 07:37:16.00 PST
Postgres, DMYday / month / yearWed 17 Dec 07:37:16 1997 PST

用户可以使用以下方式选择日期/时间样式: SET datestyle 命令, postgresql.conf 配置文件中的 DateStyle 参数, 或者服务器或客户端的 PGDATESTYLE 环境变量.

格式化函数 to_char (参见 数据类型格式化函数) 是一种格式化日期/时间输出的更加灵活的方式.

时区

时区和时区转换,不仅仅是地理位置问题,也受政治决定影响. 从 1900 年以来,世界上的时区稍微标准了一点, 但还是在持续的改变, 尤其是夏令时规则. Greenplum 使用广泛采纳的 IANA (Olson) 时区数据库来定义历史时区规则. 对于未来时间, 假定给定时区的现行已知规则会无限期持续.

Greenplum 保持与 SQL 标准兼容的典型用法定义. 然而, SQL 标准偶尔也有一点混淆时间和日期类型和能力. 两个明显的问题是:

  1. 虽然 date 类型不能带时区, 但是 time 类型是可以的. 不与日期和时间关联,时区在真实世界意义不大, 因为偏移变化与夏令时边界相关.
  2. 默认时区是与 UTC 之间差异的常量数值. 因而,当进行跨 DST 边界的日期/时间数值计算时,可能采用夏令时.

为了解决这些困难, 当使用时区的时候,我们推荐用同时包含日期和时间的日期/时间类型. 我们 推荐使用 time with time zone 类型(虽然 Greenplum 支持,但主要是为了一个老应用,同时保持与 SQL 标准兼容). 对于任何只包含日期或时间的类型, Greenplum 假定是你的本地时区(的时间或日期).

所有时区感知的日期和时间内部都存储为 UTC 值. 当被显示到客户端时,才通过 TimeZone 配置参数的值转换为指定时区的日期或时间.

Greenplum 允许你以三种不同的形式指定时区:

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Greenplum 数据库数据类型后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部