[GreenPlum] 1.基础数据类型

摘自PostgreSQL 8.1中文文档

 

 

PostgreSQL 有着丰富的数据类型可用。 用户可以使用 CREATE TYPE 命令为 PostgreSQL 增加新的数据类型。

Table 8-1 显示了所有内置的普通数据类型。 在”别名”列里列出的大多数可选名字都是因历史原因 PostgreSQL 在内部使用的名字。 另外,还有一些内部使用的或者废弃的类型也可以用,但没有在这里列出。

Table 8-1. 数据类型

名字 别名 描述
bigint int8 有符号 8 字节整数
bigserial serial8 自增八字节整数
bit [ (n) ]   定长位串
bit varying [ (n) ] varbit 变长位串
boolean bool 逻辑布尔量 (真/假)
box   平面中的长方形
bytea   二进制数据(”字节数组”)
character varying [ (n) ] varchar [ (n) ] 变长字符串
character [ (n) ] char [ (n) ] 定长字符串
cidr   IPv4 或者 IPv6 网络地址
circle   平面中的圆
date   日历日期(年,月,日)
double precision float8 双精度浮点数字
inet   IPv4 或者 IPv6 网络地址
integer int,int4 四字节长有符号整数
interval [ (p) ]   时间间隔
line   平面中的无限长直线
lseg   平面中的线段
macaddr   MAC 地址
money   货币金额
numeric [ (ps) ] decimal [ (ps) ] 可选精度的准确数字
path   平面中的几何路径
point   平面中的点
polygon   平面中的封闭几何路径
real float4 单精度浮点数
smallint int2 有符号两字节整数
serial serial4 自增四字节整数
text   变长字符串
time [ (p) ] [ without time zone ]   一天里的时间
time [ (p) ] with time zone timetz 一天里的时间,包括时区
timestamp [ (p) ] [ without time zone ]   日期和时间
timestamp [ (p) ] with time zone timestamptz 日期和时间

兼容性下列类型(或者那样拼写的)是SQL声明的: bit,bit varying,boolean, char,character,character varying,varchar,date, double precision,integer, interval,numeric,decimal, real,smallint,time (包括有时区和无时区的), timestamp(包括有时区和无时区的)。

每种数据类型都有一个由其输入和输出函数决定的外部表现形式。 许多内建的类型有明显的格式。不过,许多类型要么是 PostgreSQL 所特有的,比如几何路径,要么可能是有几种不同的格式,比如日期和时间类型。 有些输入和输出函数是不可逆的。也就是说,输出函数的输出结果和原始的输入比较的时候可能丢失精度。

8.1. 数值类型

数值类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。 Table 8-2 列出了所有可用类型。

Table 8-2. 数值类型

名字 存储空间 描述 范围
smallint 2 字节 小范围整数 -32768 到 +32767
integer 4 字节 常用的整数 -2147483648 到 +2147483647
bigint 8 字节 大范围的整数 -9223372036854775808 到 9223372036854775807
decimal 变长 用户声明精度,精确 无限制
numeric 变长 用户声明精度,精确 无限制
real 4 字节 变精度,不精确 6 位十进制数字精度
double precision 8 字节 变精度,不精确 15 位十进制数字精度
serial 4 字节 自增整数 1 到 +2147483647
bigserial 8 字节 大范围的自增整数 1 到 9223372036854775807

数值类型常量的语法在 Section 4.1.2 里描述。 数值类型对应有一套完整的数学操作符和函数。相关信息请参考 Chapter 9。 下面的几节详细描述这些类型。

8.1.1. 整数类型

类型 smallint,integer,和 bigint 存储各种范围的全部是数字的数,也就是没有小数部分的数字。 试图存储超出范围以外的数值将导致一个错误。

常用的类型是 integer,因为它提供了在范围,存储空间, 和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint。而只有在 integer 的范围不够的时候才使用 bigint,因为前者绝对快得多。

bigint 类型可能不是在所有平台上都运转正确, 因为它依赖编译器对八字节整数的支持。在那些没有这样支持的机器上, bigint 的作用和 integer 一样(但是仍然占据八字节存储)。不过,我们还不知道任何有这样的情况的平台。

SQL只声明了整数类型 integer(或int)和 smallint。类型 bigint,和类型名 int2,int4,和 int8 都是扩展, 也在许多其它 SQL 数据库系统中使用。

8.1.2. 任意精度数值

类型 numeric 可以存储最多1000位精度的数字并且准确地进行计算。 我们特别建议将它用于货币金额和其它要求计算准确的数量。不过,numeric 类型上的算术运算比整数类型或者我们下一节描述的浮点数类型要慢很多。

在随后的内容里,我们使用了下述术语: 一个 numeric 的比例是到小数点右边为止小数部分的位数, numeric 的精度是整个数字里全部数据位的数目,也就是小数点两边的数据数目。 因此数字 23.5141 的精度为6而比例为4。你可以认为整数的比例为零。

numeric 字段的最大精度和最大比例都是可以配置的。要声明一个类型为 numeric 的字段,你可以用下面的语法

NUMERIC(precision, scale)

精度必须为正数,比例可以为零或者正数。 另外,

NUMERIC(precision)

选择了 0 为比例。不带任何精度或者比例声明

NUMERIC

则创建一个可以存储一个直到实现精度上限的任意精度和比例的数值, 一个这样类型的字段将不会把输入数值转化成任何特定的比例, 而带有比例声明的 numeric 字段将把输入值转化为该比例。 (SQL标准要求缺省的比例是 0。也就是转化成整数精度。 我们觉得这样做有点没用。如果你关心移植性,那你最好总是明确声明精度和比例。)

如果一个要存储的数值的比例比字段声明的比例高, 那么系统将尝试圆整(四舍五入)该数值到指定的小数位。 然后,如果小数点左边的数据位数超过了声明的精度减去声明的比例, 那么抛出一个错误。

数值数据值物理上是不带任何前导或者后缀零的形式存储的。 因此,字段上声明的精度和比例都是最大值,而不是固定分配的。 (在这个方面,numeric 类型更类似于 varchar(n), 而不像 char(n)。) 实际存储是每四个十进制位两个字节,然后在整个数据上加上八个字节的额外开销。

除了普通的数字值之外,numeric 类型允许特殊值 NaN, 表示”不是一个数字”。任何在 NaN 上面的操作都生成另外一个 NaN。 如果在 SQL 命令里把这些值当作一个常量写,你必须在其周围放上单引号,比如 UPDATE table SET x = ‘NaN’。在输入时,字串 NaN 当作大小写无关看待。

类型 decimal 和 numeric 是等效的。 两种类型都是SQL标准。

8.1.3. 浮点数类型

数据类型 real 和 double precision 是不准确的,变精度的数字类型。 实际上,这些类型是 IEEE 标准 754 二进制浮点数算术(分别对应单和双精度)的一般实现, 外加下层处理器,操作系统和编译器对它的支持。

不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的,因此存储然后把数据再打印出来可能显示一些缺失。 处理这些错误以及这些错误是如何在计算中传播的属于数学和计算机科学的一个完整的分支, 我们不会在这里进一步讨论它,这里的讨论仅限于如下几点:

  • 如果你要求准确的计算(比如计算货币金额),应使用 numeric 类型。
  • 如果你想用这些类型做任何重要的复杂计算,尤其是那些你对范围情况(无穷,下溢)严重依赖的事情,那你应该仔细评诂你的实现。
  • 拿两个浮点数值进行相等性比较可能象,也可能不象想像那样运转。

通常,real 类型的范围是至少 -1E+37 到 +1E+37, 精度至少是 6 位小数。double precision 类型通常有 -1E+308 到 +1E+308 的范围,精度是至少 15 位数字。太大或者太小的数值都会导致错误。 如果输入数据太高,那么可能发生园整。太接近零的数字,如果无法与零值的表现形式相区分就会产生下溢错。

除了普通的数字值之外,浮点类型还有几个特殊值:

Infinity

-Infinity

NaN

这些值分别表示 IEEE 754 特殊值”正无穷大”,”负无穷大”, 以及”不是一个数字”。(在不遵循 IEEE 754 浮点算术的机器上,这些值的含义可能不是预期的。) 如果在 SQL 命令里把这些数值当作常量写,你必须在它们周围放上单引号, 像这样 UPDATE table SET x = ‘Infinity’。 输入时,这些值是以大小写无关的方式识别的。

PostgreSQL 还支持 SQL 标准表示法 float 和 float(p) 用于声明非精确的数值类型。 在这里,p 声明以二进制位表示的最低可接受精度。 在选取 real 类型的时候,PostgreSQL 接受 float(1) 到 float(24),在选取 double precision 的时候,接受 float(25) 到 float(53)。在允许范围之外的 p 值将导致一个错误。 没有声明精度的 float 将被当作是 double precision。

注意在 PostgreSQL 7.4 以前,在 float(p) 里面的精度会被当作是这么多位数的十进制位。到 7.4 已经被修改成与 SQL 标准匹配,标准声明这个精度是以二进制位度量的。假设 real 和 double precision 分别有 24 和 53 个二进制位的位数对 IEEE 标准的浮点实现来说是正确的。 在非 IEEE 平台上,这个数值可能略有偏差,但是为了简化,我们在所有平台上都用了同样的 p 值范围。

8.1.4. Serial(序号)类型

serial 和 bigserial 类型不是真正的类型, 只是为在表中设置唯一标识做的概念上的便利。(类似其它一些数据库中的 AUTO_INCREMENT 属性)。 在目前的实现中,下面一句话:

CREATE TABLE tablename (
colname SERIAL
);

等价于声明下面几句话:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
colname integer DEFAULT nextval(‘tablename_colname_seq’) NOT NULL
);

因此,我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。 应用了一个 NOT NULL 约束以确保空值不会被明确地插入。 在大多数情况下你可能还希望附加一个 UNIQUE 或者 PRIMARY KEY 约束避免意外地插入重复的数值,但这个不是自动发生的。

注意在 PostgreSQL 7.3 以前,serial 隐含 UNIQUE。但现在不再如此。 如果你希望一个序列字段有一个唯一约束或者一个主键,那么你现在必须声明,就像其它数据类型一样。

要使用 serial 字段插入序列的下一个数值到表中, 主要是要注意 serial 应该赋予缺省值。 我们可以通过在 INSERT 语句中把该字段排除在字段列表之外来实现, 也可以通过使用 DEFAULT 关键字来实现。

类型名 serial 和 serial4 是等效的: 两个都创建 integer 字段。类型名 bigserial 和 serial8 也一样,只不过它创建一个 bigint 字段。 如果你预计在表的生存期中使用的标识数目超过 231 个,那么你应该使用 bigserial。

一个 serial 类型创建的序列在所属的字段被删除的时候自动删除,其它情况下是不会被删除的。 (这一点在 PostgreSQL 版本 7.3 之前可不是真的。请注意,这种自动删除的关联在通过重载 7.3 以前的数据库转储的时候可不会自动发生; 那样的转储文件不包含需要建立这种关联关系的信息。) 另外,这样的序列和字段之间的依赖性只在 serial 字段本身上有; 如果任何其它字段引用了序列(可能是手工调用 nextval 函数), 那么,如果这个序列被删除了,它们就会被破坏。我们认为这样使用 serial 字段是一种不好的形式;如果你想用同一个序列发生器给几个字段喂数据,那么还是把序列发生器作为独立对象创建。

 

8.2. 货币类型

注意money (货币)现在已经废弃,用 numeric 或 decimal 以及和 to_char 函数一起使用就可以取代它。

money 类型存储固定小数点位置的货币数字。 参阅 Table 8-3。 可接受的输入格式很多,包括整数和浮点数文本,以及””货币格式,象 ‘$1,000.00’ 这样的。 输出是最后一种形式,但和区域相关。

Table 8-3. 货币类型

名字 存储空间 描述 范围
money 4 字节 货币金额 -21474836.48 到 +21474836.47

 

8.3. 字符类型

Table 8-4. 字符类型

名字 描述
character varying(n), varchar(n) 变长,有长度限制
character(n), char(n) 定长,不足补空白
text 变长,无长度限制

Table 8-4 显示了在 PostgreSQL 里可用的一般用途的字符类型。

SQL 定义了两种基本的字符类型: character varying(n) 和 character(n), 这里的 n 是一个正整数。两种类型都可以存储最多 n 个字符长的字串。 试图存储更长的字串到这些类型的字段里会产生一个错误, 除非超出长度的字符都是空白,这种情况下该字串将被截断为最大长度。 (这个看上去有点怪异的例外是SQL标准要求的。) 如果要存储的字串比声明的长度短, 类型为 character 的数值将会用空白填满; 而类型为 character varying 的数值将只是存储短些的字串。

如果我们明确地把一个数值转换成 character(n) 或者 character varying(n), 那么超长的数值将被截断成 n 个字符,而不会抛出错误。(这也是SQL标准的要求。)

注意在PostgreSQL7.2 以前,太长的字串总会被不声不响地咔喳掉, 而且不会生成错误。不管是明确的还是隐含的转换情况下。

char(n) 和 varchar(n) 的概念分别是 character(n) 和 character varying(n) 的别名, 没有长度声明词的character 等于 character(1);如果不带长度说明词使用 character varying,那么该类型接受任何长度的字串。 后者是PostgreSQL的扩展。

另外,PostgreSQL 提供 text 类型,它可以存储任何长度的字串。 尽管类型 text 不是SQL标准,但是许多其它 SQL 数据库系统也有它。

类型 character 的数值物理上都用空白填充到指定的长度 n, 并且以这种方式存储和显示。不过,填充的空白在语意上是无所谓的。 在比较两个 character 的值的时候,填充的空白都不会被关注, 在转换成其它字串类型的时候,character 值里面的空白会被删除。 请注意,在 character varying 和 text 数值里, 结尾的空白语意上是有含义的。

这些类型的存储需求是 4 字节加上实际的字串,如果是 character 的话再加上填充的字节。长的字串将会自动被系统压缩, 因此在磁盘上的物理需求可能会更少些。长的数值也会存储在后台表里面,这样它们就不会干扰对短字段值的快速访问。 不管怎样,允许存储的最长字串大概是 1 GB。 (允许在数据类型声明中出现的的 n 的最大值比这还小。 修改这个行为没有甚么意义,因为在多字节编码下字符和字节的数目可能差别很大。 如果你想存储没有特定上限的长字串,那么使用 text 或者没有长度声明词的 character varying, 而不要选择一个任意长度限制。)

提示这三种类型之间没有性能差别,只不过是在使用填充空白的类型的时候增加了存储尺寸。 虽然在某些其它的数据库系统里,character(n) 有一定的性能优势, 但在 PostgreSQL 里没有。在大多数情况下,应该使用 text 或者 character varying。

请参考 Section 4.1.2.1 获取关于字串文本的语法的信息, 以及参阅 Chapter 9 获取关于可用操作符和函数的信息。 数据库的字符集决定用于存储文本值的字符集;有关字符集支持的更多信息, 请参考 Section 21.2

Example 8-1. 使用字符类型

CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES (‘ok’);
SELECT a, char_length(a) FROM test1; — (1)
a   | char_length
——+————-
ok   |           2

CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES (‘ok’);
INSERT INTO test2 VALUES (‘good      ‘);
INSERT INTO test2 VALUES (‘too long’);
ERROR:  value too long for type character varying(5)
INSERT INTO test2 VALUES (‘too long’::varchar(5)); — 明确截断
SELECT b, char_length(b) FROM test2;
b   | char_length
——-+————-
ok    |           2
good  |           5
too l |           5

(1)

函数 char_length 在 Section 9.4中讨论。

在 PostgreSQL 里另外还有两种定长字符类型。 在 Table 8-5 里显示。 name类型用于在内部系统表中存储标识符并且不是给一般用户使用的。 该类型长度当前定为 64 字节 (63 可用字符加结束符)但应该使用常量 NAMEDATALEN 引用。这个长度是在编译的时候设置的,(因而可以为特殊用途调整); 缺省的最大长度在以后的版本可能会改变。 类型 “char” (注意引号)和 char(1) 是不一样的,它只用了一个字节的存储空间。它在系统内部用于系统表当做穷人的枚举类型用。

Table 8-5. 特殊字符类型

名字 存储空间 描述
“char” 1 字节 单字节内部类型
name 64 字节 用于对象名的内部类型

 

8.4. 二进制数据类型

bytea 数据类型允许存储二进制字串。 参阅Table 8-6

Table 8-6. 二进制数据类型

名字 存储空间 描述
bytea 4 字节加上实际的二进制字串 变长的二进制字串

二进制字串是一个字节数值的序列。 二进制字串和字符字串的区别有两个: 首先,二进制字串完全可以允许存储字节零值以及其它”不可打印的”字节 (定义为范围在 32 到 126 之外的字节)。 字符串不允许字节零,并且也不允许那些从数据库选定的字符集编码里面认为是非法的其它字节值或者字节序列。 第二,对二进制串的处理就是实际上的字节,而字符串的处理和取决于区域设置。 简单说,二进制字串适用于存储那些程序员认为是”裸字节”的数据, 而字符串适合存储文本。

在输入 bytea 数值的时候, 在一个 SQL 语句的文本串里面, 你必须逃逸某些字节值(但可以逃逸所有字节值) 通常,要逃逸一个字节值,需要把它的数值转换成与其十进制字节值对应的三位八进制数字, 并且前导两个反斜杠。有些八进制数值有可选的逃逸序列, 在 Table 8-7 中显示, 同时给出了可选的逃逸序列。

Table 8-7. bytea 文本逃逸八进制

十进制数值 描述 输入逃逸表现形式 例子 输出形式
0 零的八进制 ‘\\000’ select ‘\\000’::bytea; \000
39 单引号 ‘\” 或者 ‘\\047’ select ‘\”::bytea;
92 反斜杠 ‘\\\\’ 或者 ‘\\134’ select ‘\\\\’::bytea; \\
0 到 31 和 127 到 255 “不可打印”字节 ‘\\xxx (八进制值) SELECT ‘\\001’::bytea; \001

逃逸”不可打印”字节的要求因区域设置而异。在某些场合下,你可以不逃逸它们。 请注意Table 8-7里的每个例子都是刚好一个字节长,虽然字节零和反斜杠输出形式比一个字符要长。

你必须写这么多反斜杠的原因,如 Table 8-7 所示, 是因为一个写成字串文本的输入字串必须通过 PostgreSQL 服务器里的两个分析阶段。 每个反斜杠对的第一个反斜杠会被字串文本分析其理解成一个逃逸字符而消耗掉, 剩下反斜杠对中的第二个。剩下的反斜杠被 bytea 输入函数当作一个三位八进制值或者是逃逸另外一个反斜杠的开始。 比如,一个传递给服务器的字串文本 ‘\\001’ 在通过字串分析器之后会成为 \001。而 \001 则发送给 bytea 输入函数,在这里它被转换成一个十进制值为 1 的单个字节。请注意,省略字符不会被 bytea 特殊对待, 因此它遵循字串文本的普通规则。(又见 Section 4.1.2.1。)

Bytea 字节也在输出中逃逸的。通常, 每个”不可打印”的字节值都转化成对应的前导反斜杠的三位八进制数值。 大多数”可打印的”字节值是以客户端字符集的标准表现形式出现的。 十进制值为 92 (反斜杠)的字节有一个特殊的可选输出形式。细节在 Table 8-8 里描述。

Table 8-8. bytea 输出逃逸序列

字节的十进制值 描述 逃逸的输出形式 例子 输出结果
92 反斜杠 \\ select ‘\\134’::bytea; \\
0 到 31 和 127 到 255 “不可打印”八进制字符 \xxx(octal value) select ‘\\001’::bytea; \001
32 到 126 “可打印”八进制字符 客户端字符集表现形式 select ‘\\176’::bytea; ~

根据你使用的前端的不同,在逃不逃逸 bytea 字串的问题上你可能有一些额外的工作要做。 比如,如果你的接口自动转换换行和回车,那你可能还要逃逸它们。

SQL 标准定义了一种不同的二进制字串类型, 叫做 BLOB 或者 BINARY LARGE OBJECT。 其输入格式和 bytea 不同,但是提供的函数和操作符大多一样。

 

8.5. 日期/时间类型

PostgreSQL 支持 SQL 中所有的日期和时间类型。 在 Table 8-9 中显示。 这些数据类型上可以进行的操作在 Section 9.9 描述。

Table 8-9. 日期/时间类型

名字 存储空间 描述 最低值 最高值 分辨率
timestamp [ (p) ] [without time zone] 8 字节 包括日期和时间 4713 BC 5874897 AD 1 毫秒 / 14 位
timestamp [ (p) ] with time zone 8 字节 日期和时间,带时区 4713 BC 5874897 AD 1 毫秒 / 14 位
interval [ (p) ] 12 字节 时间间隔 -178000000 年 178000000 年 1 毫秒 / 14 位
date 4 字节 只用于日期 4713 BC 32767 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+1359 24:00:00-1359 1 毫秒 / 14 位

注意在 PostgreSQL 7.3 以前,只写 timestamp 等效于 timestamp with time zone。 这样是为了和 SQL 兼容。

time ,timestamp 和interval 接受一个可选的精度值 p,这个精度值声明在秒域后面小数点之后保留的位数。 缺省的时候在精度上是没有明确的绑定的, p 有用的范围对 timestamp和 interval 是从 0 到大约 6。

注意如果 timestamp 数值是以双精度浮点数(目前的缺省)的方式存储的, 那么精度的有效限制会小于 6。 timestamp 值是以 2000-01-01 午夜之前或之后以来的秒数存储的,而微秒的精度是为那些在 2000-01-01 前后几年的日期实现的, 对于那些远一些的日子,精度会下降。如果 timestamp 以八字节整数存储(一个编译时的选项),那么微秒的精度就可以在数值的全部范围内都可以获得。 不过,八位整数的时间戳的日期范围缩小到 4713 BC 到 294276 AD。 同一个编译时选项也决定 time 和 interval 值是保存成浮点数还是八字节整数。 在以浮点数存储的时候,随着时间间隔的大小增加,大的 interval 数值的精度会降低。

对于 time 类型,如果使用了八字节的整数存储,那么 p 允许的范围是从 0 到 6,如果使用的是浮点数存储,那么这个范围是 0 到 10。

类型time with time zone是 SQL 标准定义的, 但是整个定义有些方面会导致有问题的用法。在大多数情况下, date,time,timestamp without time zone 和 timestamp with time zone 的组合就应该能提供提供任何应用需要的日期/时间的完整功能。

类型 abstime 和 reltime 是低分辨率类型,它们被用于系统内部。 我们不鼓励你在新的应用里面使用这些类型,同时我们支持合适的时候把旧应用中对应的类型转换成目前上面指明的。 因为这些旧类型的部分或全部可能会在未来的版本里消失。

8.5.1. 日期/时间输入

日期和时间的输入几乎可以是任何合理的格式,包括 ISO 8601,SQL-兼容的, 传统 POSTGRES 的和其他的形式。 对于一些格式,日期输入里的月份和日子输入可能会让人模糊, 因此系统支持声明自己预期的这些字段的顺序。 把 DateStyle 参数设置为 MDY, 就是“月-日-年”的解析,设置为 DMY 就是 “日-月-年”,而 YMD 是 “年-月-日”。

PostgreSQL 在处理日期/时间输入上比 SQL 标准要求的更灵活。 参阅 Appendix B 获取关于日期/时间输入的准确的分析规则和可识别文本字段,包括月份,星期几,和时区。

请记住任何日期或者时间的文本输入需要由单引号包围, 就象一个文本字符串一样。 参考 Section 4.1.2.5 获取更多信息。SQL 要求下面的语法

type [ (p) ] ‘value

在这里可选的精度声明中的 p 是一个整数, 对应在秒域中小数部分的位数, 我们可以对 time, timestamp,和 interval 类型声明精度。 允许的精度在上面已经说明。如果在常量声明中没有声明精度,缺省是文本值的精度。

8.5.1.1. 日期

Table 8-10 显示了 date 类型可能的输入方式。

Table 8-10. 日期输入

例子 描述
January 8, 1999 在任何datestyle输入模式下都无歧义
1999-01-08 ISO-8601 格式,任何方式下都是1999年1月8号,(建议格式)
1/8/1999 歧义,在MDY下是一月八号;在 DMY 模式下读做八月一日
1/18/1999 在MDY模式下读做一月十八日,其它模式下被拒绝
01/02/03 MDY 模式下的2003年一月2日; DMY 模式下的 2003 年 2月 1日; YMD 模式下的2001年二月三日;
1999-Jan-08 任何模式下都是一月8日
Jan-08-1999 任何模式下都是一月8日
08-Jan-1999 任何模式下都是一月8日
99-Jan-08 在 YMD 模式下是一月8日,否则错误
08-Jan-99 一月八日,除了在 YMD 模式下是错误的之外
Jan-08-99 一月八日,除了在 YMD 模式下是错误的之外
19990108 ISO-8601; 任何模式下都是1999年1月8日
990108 ISO-8601; 任何模式下都是1999年1月8日
1999.008 年和年里的第几天
J2451187 儒略日
January 8, 99 BC 公元前99年

8.5.1.2. 时间

当日时间类型是 time [ (p) ] without time zone 和 time [ (p) ] with time zone。 只写 time 等效于 time without time zone。

这些类型的有效输入由当日时间后面跟着可选的时区组成。 (参阅 Table 8-11。) 如果在 time without time zone 类型的输入 中声明了时区,那么它会被无声地忽略。

Table 8-11. 时间输入

例子 描述
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 用名字声明的时区

Table 8-12. 时区输入

例子 描述
PST 太平洋标准时间(Pacific Standard Time)
-8:00 ISO-8601 与 PST 的偏移
-800 ISO-8601 与 PST 的偏移
-8 ISO-8601 与 PST 的偏移
zulu 军方对 UTC 的缩写(译注:可能是美军)
z zulu 的缩写

参考Appendix B 获取可以识别的时区输入。

8.5.1.3. 时间戳

时间戳类型的有效输入由一个日期和时间的联接组成,后面跟着一个可选的时区,一个可选的 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。 PostgreSQL 从来不会在确定文本的类型之前检查文本内容,因此会把上面两个都看做是 timestamp without time zone。因此要保证把上面的当作 timestamp without time zone 看待, 就要给它明确的类型:

TIMESTAMP WITH TIME ZONE ‘2004-10-19 10:23:54+02’

。 如果一个文本已被确定是 timestamp without time zone , PostgreSQL 将不声不响忽略任何文本中指出的时区。 因此,生成的日期/时间值是从输入值的日期/时间字段衍生出来的,并且没有就时区进行调整。

对于 timestamp [without time zone],任何在输入中声明的时区都被悄悄吞掉。 也就是说,生成的日期/时间数值是从输入中明确的日期/时间字段中得出的,并且没有根据时区调整。

对于 timestamp with time zone,内部存储的数值总是 UTC (全球统一时间,以前也叫格林威治时间GMT)。如果一个输入值有明确的时区声明, 那么它将用该时区合适的偏移量转换成 UTC。如果在输入字串里没有时区声明, 那么它就假设是在系统的 timezone 参数里的那个时区,然后使用这个 timezone 时区转换成 UTC。

如果输出一个 timestamp with time zone,那么它总是从 UTC 转换成当前的 timezone 时区,并且显示为该时区的本地时间。 要看其它时区的该时间,要么修改 timezone,要么使用 AT TIME ZONE 构造(参阅 Section 9.9.3)。

在 timestamp without time zone 和 timestamp with time zone 之间的转换通常假设 timestamp without time zone 数值应该以 timezone 本地时间的形式接受或者写出。 其它的时区引用可以用 AT TIME ZONE 的方式为转换声明。

8.5.1.4. 间隔

interval数值可以用下面语法声明:

[@] quantity unit [quantity unit…] [direction]

这里:quantity 是一个数字(可能有符号); unit 是 second, minute, hour, day, week, month, year, decade, century, millennium, 或者这些单位的缩写或复数; direction 可以是 ago 或者为空。符号 @ 是一个可选的东西。不同的单位以及相应正确的符号都是隐含地增加的。

日期,小时,分钟,以及秒钟的数量可以在无明确单位标记的情况下声明。 比如,’1 12:59:10′ 和 ‘1 day 12 hours 59 min 10 sec’ 读数一样。

可选的精度 p 应该介于 0 和 6 之间, 并且缺省是输入文本的精度。

8.5.1.5. 特殊值

PostgreSQL 为方便起见支持几个特殊输入值, 如在 Table 8-13 里面显示的那样。 值infinity 和 -infinity 是特别在系统内部表示的,并且将按照同样的方式显示; 但是其它的都只是符号缩写,在读取的时候将被转换成普通的日期/时间值。 (特别是,now 和相关的字串在读取的时候就被转换成对应的数值。) 所有这些值在 SQL 命令里当作普通常量对待时,都需要写在单引号里面。

Table 8-13. 特殊日期/时间输入

输入字串 有效类型 描述
epoch date, timestamp 1970-01-01 00:00:00+00 (Unix 系统零时)
infinity timestamp 比任何其它时间戳都晚
-infinity 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。最后四个接受一个可选的精度声明。 (见 Section 9.9.4 。) 不过,请注意这些 SQL 函数不是被当作数据输入串识别的。

8.5.2. 日期/时间输出

使用 SET DateStyle,时间/日期类型的输出格式可以设成四种风格之一: ISO 8601,SQL (Ingres),传统的 POSTGRES,和 German 。缺省是 ISO 格式。 (SQL 标准要求使用 ISO 8601 格式。”SQL” 输出格式的名字是历史偶然。)Table 8-14 显示了每种输出风格的例子。date 和 time类型的 输出当然只是给出的例子里面的日期和时间部分。

Table 8-14. 日期/时间输出风格

风格描述 描述 例子
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

如果声明了 DMY 字段,那么在 SQL 和 POSTGRES 风格里,日期在月份之前出现,否则月份出现在日期之前。 (参阅 Section 8.5.1 部分,看看这个设置是如何影响对输入值的解释。) Table 8-15 显示了一个例子。

Table 8-15. 日期顺序习惯

风格描述 描述 例子
SQL, DMY // 17/12/1997 15:37:16.00 CET
SQL, MDY // 12/17/1997 07:37:16.00 PST
Postgres, DMY day/month/year Wed 17 Dec 07:37:16 1997 PST

interval 的输出看起来象输入格式,只是象 century 和 week 这样的单位被转换成年和日,而 ago 被转换成合适的符号。在 ISO 模式下输出看起来象

[ quantity unit [ … ] ] [ days ] [ hours:minutes:secondes ]

日期/时间风格可以由用户用 SET datestyle 命令 选取,或者在 postgresql.conf 配置文件里的参数 DateStyle 设置,或者服务器或客户端的 PGDATESTYLE 环境变量里设置。我们也可以用格式化函数 to_char(参阅 Section 9.8) 来更灵活地控制时间/日期地输出。

8.5.3. 时区

时区和时区习惯不仅仅受地球几何形状的影响,还受到政治决定的影响。 到了19世纪,全球的时区变得稍微标准化了些,但是还是易于遭受随意的修改, 部分是因为夏时制规则。 PostgreSQL 目前支持 1902 年到 2038 年之间的夏时制信息(对应于传统 Unix 系统时间的完整跨度)。 如果时间超过这个范围,那么假设时间是选取的时区的”标准时间”,不管它们落在哪个年份里面。

PostgreSQL 在典型应用中尽可能与 SQL 的定义相兼容。但 SQL 标准在日期和时间类型和功能上有一些奇怪的混淆。两个显而易见的问题是:

  • date (日期)类型与时区没有联系,而 time (时间)类型却有或可以有。 然而,现实世界的时区只有在与时间和日期都关联时才有意义, 因为时间偏移量(时差)可能因为实行类似夏时制这样的制度而在一年里有所变化。
  • 缺省的时区用一个数字常量表示与UTC的偏移(时差)。 因此,当跨 DST 界限做日期/时间算术时, 我们根本不可能把夏时制这样的因素计算进去。

为了克服这些困难,我们建议在使用时区的时候,使用那些同时包含日期和时间的日期/时间类型。 我们建议不要使用类型 time with time zone (尽管 PostgreSQL 出于合理应用以及为了与其他RDBMS实现兼容的考虑支持这个类型)。 PostgreSQL 假设你用于任何类型的本地时区都只包含日期或时间。

在系统内部,所有日期和时间都是用全球统一时间UTC格式存储, 时间在发给客户前端前由数据库服务器转换成本地时间,使用的是配置参数 timezone 声明的时区。

我们可以在 postgresql.conf 文件里设置配置参数 timezone, 或者用任何其它在 Chapter 17 描述的标准方法。 还有好几种特殊方法可以设置它:

  • 如果没有在 postgresql.conf 里声明 timezone,也没有在命令行开关上声明, 服务器试图使用服务器主机上的TZ环境变量作为服务器的缺省时区。 如果没有定义 TZ,或者是 PostgreSQL 不认识的时区名, 那么服务器将试图通过检查 C 库函数 localtime() 的行为来判断操作系统的缺省时区。 缺省时区是按照最接近 PostgreSQL 的已知时区的原则来选择的。
  • SQL 命令 SET TIME ZONE 为会话设置时区,这是 SET TIMEZONE TO 的一个可选的拼写方式, 更加兼容标准。
  • 如果在客户端设置了PGTZ环境变量, 那么libpq在联接时将使用这个环境变量给后端发送一个 SET TIME ZONE 命令。

请参考 Appendix B 获取一个可用时区的列表。

8.5.4. 内部

PostgreSQL 使用儒略历法用于所有日期/时间计算。 如果假设一年的长度是365.2425天时,这个方法可以 很精确地预计/计算从4713 BC(公元前4713年)到很久的未来的任意一天的日期。

19世纪以前的日期传统(历法)只是对一些趣味读物有意义, 而在我们这里好象没有充分的理由把它们编码入日期/时间控制器里面去。

 

8.6. 布尔类型

PostgreSQL 支持标准的 SQL boolean 数据类型。 boolean 只能有两个状态之一: ‘真'(‘True’) 或 ‘假'(‘False’)。 第三种状态,’未知'(‘Unknow’),用 SQL空状态表示。

“真”值的有效文本值是:

 

TRUE
‘t’
‘true’
‘y’
‘yes’
‘1’

而对于”假”而言,你可以使用下面这些:

 

FALSE
‘f’
‘false’
‘n’
‘no’
‘0’

使用TRUE和FALSE 这样的字眼比较好(也是SQL兼容的用法)。

Example 8-2. 使用 boolean 类型

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, ‘sic est’);
INSERT INTO test1 VALUES (FALSE, ‘non est’);
SELECT * FROM test1;
a |    b
—+———
t | sic est
f | non est

SELECT * FROM test1 WHERE a;
a |    b
—+———
t | sic est

Example 8-2 显示了使用字母 t 和 f 输出 boolean 数值的例子。

技巧boolean 类型的数值不能直接转换成其它 类型(也就是说, CAST (boolval AS integer) 是不会起作用的)。你可以用 CASE 表达式实现这个目的: CASE WHEN boolval THEN ‘value if true’ ELSE’value if false’ END。见 Section 9.13

bool 使用1字节存储空间。

 

8.7. 几何类型

几何数据类型表示二维的平面物体。 Table 8-16 显示了PostgreSQL 里面可以用的几何类型。 最基本的类型:点,是其他类型的基础。

Table 8-16. 几何类型

名字 存储空间 描述 表现形式
point 16 字节 空间中一点 (x,y)
line 32 字节 (无穷)直线(未完全实现) ((x1,y1),(x2,y2))
lseg 32 字节 (有限)线段 ((x1,y1),(x2,y2))
box 32 字节 长方形 ((x1,y1),(x2,y2))
path 16+16n 字节 闭合路径(与多边形类似) ((x1,y1),…)
path 16+16n 字节 开放路径 [(x1,y1),…]
polygon 40+16n 字节 多边形(与闭合路径相似) ((x1,y1),…)
circle 24 字节 圆(圆心和半径) <(x,y),r>(圆心与半径)

我们有一系列丰富的函数和操作符可用来进行各种几何计算, 如拉伸,转换,旋转和计算相交等。 它们在 Section 9.10 里有解释。

8.7.1. Point(点)

点是几何类型的基本二维构造单位。 用下面语法描述 point 的数值:

( x , y )
x , y

这里的参数是 是用浮点数表示的点的 x 坐标和 y 坐标。

8.7.2. 线段

线段 (lseg)是用一对点来代表的。 lseg 的值用下面语法声明:

( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1   ,   x2 , y2

这里的 (x1,y1), (x2,y2) 是线段的端点。

8.7.3. Box(方)

方是用一对对角点来表示的。 box 的值用下面语法声明:

( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1   ,   x2 , y2

这里的 (x1,y1) 和 (x2,y2) 是方形的一对对角点。

方的输出使用第一种语法。在输入时将按先右上角后左下角的顺序重新排列。 你也可以输入其他的一对对角点。 但输入时将先从输入中和存储的角中计算出左下角和右上角然后再存储。

8.7.4. Path(路径)

路径由一系列连接的点组成。路径可能是开放的, 也就是认为列表中第一个点和最后一个点没有连接, 也可能是闭合的,这时认为第一个和最后一个点连接起来。

path 的数值用下面语法声明:

( ( x1 , y1 ) , … , ( xn , yn ) )
[ ( x1 , y1 ) , … , ( xn , yn ) ] ( x1 , y1 ) , … , ( xn , yn )
( x1 , y1   , … ,   xn , yn )
x1 , y1   , … ,   xn , yn

这里的点是组成路径的线段的端点。 方括弧([])表明一个开放的路径,圆括弧(())表明一个闭合的路径。

路径的输出使用第一种语法输出。

8.7.5. Polygon(多边形)

多边形由一系列点代表(多边形的顶点)。多边形可以认为与闭合路径一样, 但是存储方式不一样而且有自己的一套支持过程/函数。

polygon 的数值用下列语法声明:

( ( x1 , y1 ) , … , ( xn , yn ) )
( x1 , y1 ) , … , ( xn , yn )
( x1 , y1   , … ,   xn , yn )
x1 , y1   , … ,   xn , yn

这里的点是组成多边形边界的线段的端点。

多边形输出使用第一种语法。

8.7.6. Circle(圆)

圆由一个圆心和一个半径代表。 circle 的数值用下面语法表示:

< ( x , y ) , r >
( ( x , y ) , r )
( x , y ) , r
x , y   , r

这里的 (x,y) 是圆心,而r圆的半径

圆的输出用第一种格式。

 

8.8. 网络地址数据类型

PostgreSQL 提供用于存储 IPv4,IPv6 和 MAC 地址的数据类型, 在 Table 8-17 里显示。 用这些数据类型存储网络地址比用纯文本类型好,因为这些类型提供输入错误检查和好些种特殊的操作和功能。 (见 Section 9.11)。

Table 8-17. 网络地址类型

名字 存储空间 描述
cidr 12 或 24 字节 IPv4 和 IPv6 网络
inet 12 或 24 字节 IPv4 或 IPv6 网络和主机
macaddr 6 字节 MAC 地址

在对 inet 或者 cidr 数据类型进行排序的时候, IPv4 地址将总是排在 IPv6 地址前面,包括那些封装或者是映射在 IPv6 地址里 的 IPv4 地址,比如 ::10.2.3.4 或者 ::ffff::10.4.3.2。

8.8.1. inet

inet 在一个数据域里保存一个主机 IPv4 或 IPv6 地址, 以及一个可选的它所处的等效的子网。 子网的等效是通过计算主机地址中有多少位表示网络地址的方法来 表示的 (”网络掩码”)。 如果网络掩码是 32 并且地址是 IPv4 ,那么不表示任何子网,只是一台主机。 在 IPv6 里,地址长度是 128 位,因此 128 位表明一个唯一的主机地址。 请注意如果你想只接受网络地址,你应该使用 cidr 类型而不是 inet。

该类型的输入格式是 地址/y 这里 地址 是 IPv4 或者 IPv6 主机,y 是网络掩码的位数。 如果 /y 部分未填, 则网络掩码对 IPv 而言是 32,对 IPv6 而言是 128, 所以该值表示只有一台主机。 显示时,如果 /y 部分是 /32,将不会显示出来。

8.8.2. cidr

cidr 保存一个 IPv4 或 IPv6 网络地址声明。 其输入和输出遵循无类的互联网域路由(Classless Internet Domain Routing)习惯。 声明一个网络的格式是 地址/y 这里 地址 是 IPv4 或 IPv6 网络地址而 /y 是 网络掩码的二进制位数。 如果省略 /y, 那么掩码部分用旧的有类的网络编号系统进行计算,但要求输入的数据已经包括了确定掩码的所需的所有字节。 如果声明了一个网络地址,它的指定掩码的右边置了位,那么算错误。

Table 8-18是些例子:

Table 8-18. cidr 类型输入举例

cidr 输入 cidr 显示 abbrev(cidr)
192.168.100.128/25 192.168.100.128/25 192.168.100.128/25
192.168/24 192.168.0.0/24 192.168.0/24
192.168/25 192.168.0.0/25 192.168.0.0/25
192.168.1 192.168.1.0/24 192.168.1/24
192.168 192.168.0.0/24 192.168.0/24
128.1 128.1.0.0/16 128.1/16
128 128.0.0.0/16 128.0/16
128.1.2 128.1.2.0/24 128.1.2/24
10.1.2 10.1.2.0/24 10.1.2/24
10.1 10.1.0.0/16 10.1/16
10 10.0.0.0/8 10/8
10.1.2.3/32 10.1.2.3/32 10.1.2.3/32
2001:4f8:3:ba::/64 2001:4f8:3:ba::/64 2001:4f8:3:ba::/64
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 2001:4f8:3:ba:2e0:81ff:fe22:d1f1
::ffff:1.2.3.0/120 ::ffff:1.2.3.0/120 ::ffff:1.2.3/120
::ffff:1.2.3.0/128 ::ffff:1.2.3.0/128 ::ffff:1.2.3.0/128

8.8.3. inet  cidr 对比

inet 和 cidr 类型之间的基本区别是 inet 接受右边有非零位的网络掩码, 而 cidr 不接受。

提示如果你不喜欢 inet 或 cidr 值的输出 格式,请试一下 host ,text 和 abbrev 函数。

8.8.4. macaddr

macaddr 类型存储 MAC 地址,也就是以太网卡硬件地址 (尽管 MAC 地址还用于其它用途)。可以接受多种客户化的格式, 包括

 

‘08002b:010203’
‘08002b-010203’
‘0800.2b01.0203’
’08-00-2b-01-02-03′
’08:00:2b:01:02:03′

它们声明的都是同一个地址。 对于数据位 a 到 f,大小写都行。 输出总是我们上面给出的最后一种形式。

在 PostgreSQL 源代码发布的 contrib/mac 目录里有一些可以将 MAC 地址映射为硬件制造商的名字的工具。

 

8.9. 位串类型

位串就是一串 1 和 0 的字串。它们可以用于存储和视觉化位掩码。 我们有两种类型的 SQL 位类型: bit(n) 和 bit varying(n); 这里的 n是一个正整数。

bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的数据都是错误的。类型 bit varying 数据是最长 n 的变长类型; 更长的串会被拒绝。写一个没有长度的 bit 等效于 bit(1),没有长度的bit varying 意思是没有长度限制。

注意如果我们明确地把一个位串值转换成 bit(n), 那么它的右边将被截断或者在右边补齐零,直到刚好 n 位, 而不会抛出任何错误。类似地,如果我们明确地把一个位串数值转换成 bit varying(n),如果它超过了n 位, 那么它的右边将被截断。

注意在PostgreSQL7.2 之前,不管是否有明确的转换, bit 都会在右边自动截断或者在在右边填充零的。这个行为现在已经为了和SQL标准兼容修改过来了。

请参考 Section 4.1.2.3 获取有关位串常量的语法的信息。还有一些位逻辑操作符和位处理函数可用; 见Section 9.6

Example 8-3. 使用位串类型

CREATE TABLE test (a bit(3), b bit varying(5));
INSERT INTO test VALUES (B’101′, B’00’);
INSERT INTO test VALUES (B’10’, B’101′);
ERROR:  Bit string length 2 does not match type bit(3)
INSERT INTO test VALUES (B’10’::bit(3), B’101′);
SELECT * FROM test;
a  |  b
—–+—–
101 | 00
100 | 101

 

8.10. 数组

PostgreSQL 允许记录的字段定义成定长或不定长的多维数组。 数组类型可以是任何基本类型或用户定义类型。(不过,复合类型和域的数组还不支持。)

8.10.1. 数组类型的声明

为说明这些用法,我们先创建一个由基本类型数组构成的表:

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

如上所示,一个数组类型是通过在数组元素类型名后面附加方括弧([])来命名的。 上面的命令将创建一个叫 sal_emp 的表,它的字段中有一个 text 类型字符串(name), 一个一维 integer型数组(pay_by_quarter), 代表雇员的季度薪水和一个两维 text 类型数组(schedule), 表示雇员的周计划。

CREATE TABLE 的语法允许声明数组的确切大小,比如:

CREATE TABLE tictactoe (
squares   integer[3][3] );

不过,目前的实现并不强制数组尺寸限制 — 其行为和用于未声明长度的 数组相同。

实际上,目前的声明也不强制数组维数。特定元素类型的数组都被认为是相同的类型, 不管他们的大小或者维数。因此,在 CREATE TABLE 里定义数字或者维数都只是简单的文档,它并不影响运行时的行为。

另外还有一种语法,它遵循 SQL 标准,可以用于声明一维数组。 pay_by_quarter 可以定义为:

pay_by_quarter  integer ARRAY[4],

这个语法要求一个整数常量表示数组尺寸。不过,和以前一样,PostgreSQL 并不强制这个尺寸限制。

8.10.2. 数组值输入

把一个数组数值写成一个文本值的时候, 我们用花括弧把数值括起来并且用逗号将它们分开。 (如果你懂 C,那么这与初始化一个结构很像。) 你可以在任何数组值周围放置双引号,如果这个值包含逗号或者花括弧, 那么你就必须加上双引号。(下面有更多细节。)因此,一个数组常量的常见格式如下:

‘{ val1 delim val2 delim … }’

这里的 delim 是该类型的分隔符, 就是那个在它的 pg_type 记录里指定的那个。 在 PostgreSQL 发布提供的标准数据类型里, 类型 box 使用分号(;),但是所有其它类型都用逗号(,)。 每个 val 要么是一个数组元素类型的常量,要么是一个子数组。一个数组常量的例子是

‘{{1,2,3},{4,5,6},{7,8,9}}’

这个常量是一个两维的,3乘3的数组,由三个整数子数组组成。

(这种数组常量实际上只是我们在 Section 4.1.2.5 里讨论过的一般类型常量的一种特例。常量最初是当作字串看待并且传递给数组输入转换过程。可能需要我们用明确的类型声明。)

现在我们可以显示一些 INSERT 语句。

INSERT INTO sal_emp
VALUES (‘Bill’,
‘{10000, 10000, 10000, 10000}’,
‘{{“meeting”, “lunch”}, {“meeting”}}’);
ERROR:  multidimensional arrays must have array expressions with matching dimensions

请注意多维数组必须匹配每个维的元素数。如果不匹配则导致错误发生。

INSERT INTO sal_emp
VALUES (‘Bill’,
‘{10000, 10000, 10000, 10000}’,
‘{{“meeting”, “lunch”}, {“training”, “presentation”}}’);

INSERT INTO sal_emp
VALUES (‘Carol’,
‘{20000, 25000, 25000, 25000}’,
‘{{“breakfast”, “consulting”}, {“meeting”, “lunch”}}’);

目前的数组实现的一个局限是一个数组的独立元素不能是 SQL 空值。 整个数组可以设置为空,但是你不能有这么一个数组,里面有些元素是空, 而有些不是。(这一点将来可能改变。)

前面的两个插入的结果看起来像这样:

SELECT * FROM sal_emp;
name  |      pay_by_quarter       |                 schedule
——-+—————————+——————————————-
Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

我们还可以使用 ARRAY 构造器语法:

INSERT INTO sal_emp
VALUES (‘Bill’,
ARRAY[10000, 10000, 10000, 10000],
ARRAY[[‘meeting’, ‘lunch’], [‘training’, ‘presentation’]]);

INSERT INTO sal_emp
VALUES (‘Carol’,
ARRAY[20000, 25000, 25000, 25000],
ARRAY[[‘breakfast’, ‘consulting’], [‘meeting’, ‘lunch’]]);

请注意数组元素是普通的 SQL 常量或者表达式;比如,字串文本是用单引号包围的, 而不是像数组文本那样用双引号。ARRAY 构造器语法在 Section 4.2.10 里有更详细的讨论。

8.10.3. 访问数组

现在我们可以在这个表上运行一些查询。 首先,我们演示如何一次访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

name
——-
Carol
(1 row)

数组的脚标数字是写在方括弧内的。 PostgreSQL 缺省使用以一为基的数组习惯, 也就是说,一个 n 元素的数组从array[1]开始, 到 array[n] 结束。

这个查询检索所有雇员第三季度的薪水:

SELECT pay_by_quarter[3] FROM sal_emp;

pay_by_quarter
—————-
10000
25000
(2 rows)

我们还可以访问一个数组的任意长方形片断,或称子数组。 对于一维或更多维数组,一个数组的某一部分是用 脚标下界: 脚标上界 表示的。 比如,下面查询检索 Bill 该周头两天的第一件计划。

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = ‘Bill’;

schedule
————————
{{meeting},{training}}
(1 row)

我们还可以这样写

SELECT schedule[1:2][1] FROM sal_emp WHERE name = ‘Bill’;

获取同样的结果。如果任何脚标写成 lower: upper 的形式,那么任何数组脚标操作总是当做一个数组片断对待。 如果只声明了一个数值,那么都是假设下界为 1,比如:

SELECT schedule[1:2][2] FROM sal_emp WHERE name = ‘Bill’;

schedule
——————————————-
{{meeting,lunch},{training,presentation}}
(1 row)

从一个数组的当前范围之外抓取数据生成一个 SQL 空值,而不是导致错误。 比如,如果 schedule 目前的维是 [1:3][1:2], 然后我们抓取 schedule[3][3] 会生成 NULL。类似的还有, 一个下标错误的数组引用生成空,而不是错误。还有就是抓取一个完全在数组的当前范围之外的数组片断, 也是生成一个空值数组;但是如果要求的片断部分覆盖数组的范围,那么它会自动缩减为抓取覆盖的范围。

任何数组的当前维数都可以用 array_dims 函数检索:

SELECT array_dims(schedule) FROM sal_emp WHERE name = ‘Carol’;

array_dims
————
[1:2][1:1] (1 row)

array_dims 生成一个 text 结果, 对于人类可能比较容易阅读,但是对于程序可能就不那么方便了。 我们也可以用 array_upper 和 array_lower 函数检索,它们分别返回指定数组维的上界和下界。

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = ‘Carol’;

array_upper
————-
2
(1 row)

8.10.4. 修改数组

一个数组值可以完全被代替:

UPDATE sal_emp SET pay_by_quarter = ‘{25000,25000,27000,27000}’
WHERE name = ‘Carol’;

或者使用 ARRAY 表达式语法:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = ‘Carol’;

或者只是更新某一个元素:

UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = ‘Bill’;

或者更新某个片断:

UPDATE sal_emp SET pay_by_quarter[1:2] = ‘{27000,27000}’
WHERE name = ‘Carol’;

我们可以通过给一个和已存在的元素相邻元素赋值的方法, 或者是向已存在的数据相邻或重叠的区域赋值的方法来扩大一个数组。 比如,如果一个数组 myarray 当前有 4 个元素,那么如果我们给 myarray[5] 赋值后,它就有五个元素。目前,这样的扩大只允许多一维数组进行, 不能对多维数组进行操作。

数组片段赋值允许创建不使用一为基的下标的数组。 比如,我们可以给 array[-2:7] 赋值, 创建一个脚标值在 -2 和 7 之间的数组。

新的数组值也可以用连接操作符 || 构造。

SELECT ARRAY[1,2] || ARRAY[3,4];
?column?
———–
{1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
?column?
———————
{{5,6},{1,2},{3,4}}
(1 row)

连接操作符允许把一个元素压入一个一维数组的开头或者结尾。它还接受两个 N 维的数组,或者一个 N 维和一个 N+1 维的数组。

在向一个一维数组的开头压入一个元素后,结果是这样的一个数组: 它的低界下标等于右手边操作数的低界下标减一,如果向一个一维数组的结尾压入一个元素, 结果数组就是一个保持左手边操作数低界的数组。比如:

SELECT array_dims(1 || ARRAY[2,3]);
array_dims
————
[0:2] (1 row)

SELECT array_dims(ARRAY[1,2] || 3);
array_dims
————
[1:3] (1 row)

如果两个相同维数的数组连接在一起,结果保持左手边操作数的外层维数的低界下标。 结果是这样一个数组:它包含左手边操作数的每个元素,后面跟着右手边操作数的每个元素。比如:

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
array_dims
————
[1:5] (1 row)

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
array_dims
————
[1:5][1:2] (1 row)

如果一个 N 维的数组压到一个 N+1 维数组的开头或者结尾, 结果和上面的数组元素的情况类似。每个 N 维的子数组实际上都是 N+1 维数组的外层维数。比如:

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
array_dims
————
[0:2][1:2] (1 row)

数组也可以用函数 array_prepend, 和 array_append, 以及 array_cat 构造。头两个只支持一维数组, 而 array_cat 支持多维数组。 请注意使用上面讨论的连接操作符要比直接使用这些函数好。实际上, 这些函数主要用于实现连接操作符。不过,在用户定义的创建函数里直接使用他们可能有必要。一些例子:

SELECT array_prepend(1, ARRAY[2,3]);
array_prepend
—————
{1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
array_append
————–
{1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
array_cat
———–
{1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
array_cat
———————
{{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
array_cat
———————
{{5,6},{1,2},{3,4}}

8.10.5. 在数组中检索

要搜索一个数组中的数值,你必须检查该数组的每一个值。 你可以手工处理(如果你知道数组尺寸)。比如:

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;

不过,对于大数组而言,这个方法很快就会让人觉得无聊,并且如果你不知道数组尺寸,那就没什么用了。 另外一个方法在 Section 9.17 里描述。 上面的查询可以用下面的代替:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

另外,你可以用下面的语句找出所有数组有值等于 10000 的行:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

提示数组不是集合;象我们前面那些段落里描述的那样使用数组通常表明你的库设计有问题。 数组字段通常是可以分裂成独立的表。 很明显表要容易搜索得多。并且在元素数目非常庞大的时候也可以更好地伸展。

8.10.6. 数组输入和输出语法

一个数组值的外部表现形式由一些根据该数组元素类型的 I/O 转换规则分析的项组成, 再加上一些标明该数组结构的修饰。 这些修饰由围绕在数组值周围的花括弧({ 和 }), 加上相邻项之间的分隔字符组成。分隔字符通常是一个逗号(,), 但也可以是其它的东西:它由该数组元素类型的 typdelim 设置决定。 (在 PostgreSQL 版本提供的标准数据类型里, 类型 box 使用分号(;),但所有其它的类型使用逗号。) 在多维数组里,每个维(行,面,体,等)有自己级别的花括弧,并且在同级相邻的花括弧项之间必须写分隔符。

如果数组元素值是空字串或者包含花括弧,分隔符,双引号,反斜杠或者空白, 那么数组输出过程将在这些值周围包围双引号。在元素值里包含的双引号和反斜杠将被反斜杠逃逸。 对于数值数据类型,你可以安全地假设数值没有双引号包围,但是对于文本类型,我们就需要准备好面对有双引号包围, 和没有双引号包围两种情况了。(这是对 7.2 以前的 PostgreSQL 版本的行为的一个改变。)

缺省时,一个数组的某维的下标索引是设置为一的。如果一个数组的某维的下标不等于一, 那么就会在数组结构修饰域里面放置一个实际的维数。这个修饰由方括弧([]) 围绕在每个数组维的下界和上界索引,中间有一个冒号(:)分隔的字串组成。 数组维数修饰后面跟着一个等号操作符(=)。比如:

SELECT 1 || ARRAY[2,3] AS array;

array
—————
[0:2]={1,2,3}
(1 row)

SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array;

array
————————–
[0:1][1:2]={{1,2},{3,4}}
(1 row)

这个语法也可以用于在一个数组文本中声明非缺省数组脚标。比如:

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT ‘[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}’::int[] AS f1) AS ss;

e1 | e2
—-+—-
1 |  6
(1 row)

如上所述,在书写一个数组的数值的时候你要用双引号包围任意独立的数组元素。 如果元素数值可能令数组数值分析器产生歧义,那么你必须这么做。 比如,那些包含花括弧,逗号(或者任何其它的分隔字符), 双引号,反斜杠,或者前导的空白元素都必须加双引号。 要把双引号或者反斜杠放到数组元素值里,给它们加一个反斜杠前缀。 另外,你可以用反斜杠逃逸的方法保护所有那些可能被当做数组语法的字符。

你可以在左花括弧前面或者右花括弧后面写空白。你还可以在任意独立的项字串前面或者后面写空白。 所有这些情况下,这些空白都会被忽略。不过,在双引号包围的元素里面的空白,或者是元素里被两边非空白字符包围的空白,都不会被忽略。

注意请记住你在 SQL 命令里写的任何东西都将首先解释成一个字串文本, 然后才是一个数组。这样就造成你所需要的反斜杠数量翻了翻。 比如,要插入一个包含反斜杠和双引号的 text 数组, 你需要这么写

INSERT … VALUES (‘{“\\\\”,”\\””}’);

字串文本处理器去掉第一层反斜杠,然后省下的东西到了数组数值分析器的时候看起来象 {“\\”,”\””}。 接着,该字串传递给 text 数据类型的输入过程,分别变成 \ 和 “。 (如果我们用的数据类型对反斜杠也有特殊待遇,比如 bytea, 那么我们可能需要在命令里放多达八个反斜杠才能在存储态的数组元素中得到一个反斜杠。) 我们也可以用美元符包围(参阅 Section 4.1.2.2)来避免双份的反斜杠。

提示ARRAY 构造器语法(参阅 Section 4.2.10)通常比数组文本语法好用些,尤其是在 SQL 命令里写数组值的时候。 在 ARRAY 里,独立的元素值的写法和数组里没有元素时的写法一样。

 

8.11. 复合类型

复合类型描述一行或者一条记录的结构; 它实际上只是一个字段名和它们的数据类型的列表。 PostgreSQL 允许像简单数据类型那样使用复合类型。 比如,一个表的某个字段可以声明为一个复合类型。

8.11.1. 声明复合类型

下面是两个定义复合类型的简单例子:

CREATE TYPE complex AS (
r       double precision,
i       double precision
);

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

语法类似与 CREATE TABLE,只是这里只可以声明字段名字和类型; 目前不能声明约束(比如 NOT NULL 这样的)。请注意 AS 关键字是很重要的; 没有它,系统会认为这是完全不同的 CREATE TYPE 命令,因此你会看到奇怪的语法错误。

定义了类型,我们就可以用它创建表:

CREATE TABLE on_hand (
item      inventory_item,
count     integer
);

INSERT INTO on_hand VALUES (ROW(‘fuzzy dice’, 42, 1.99), 1000);

或者函数:

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS ‘SELECT $1.price * $2’ LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

在你创建表的时候,也会自动创建一个复合类型,名字与表名字相同,表示该表的复合类型。 比如,如果我们说过

CREATE TABLE inventory_item (
name            text,
supplier_id     integer REFERENCES suppliers,
price           numeric CHECK (price > 0)
);

然后,和上面显示的相同的 inventory_item 复合类型也会作为副产品创建, 并且可以和上面一样使用。不过,需要注意目前的实现的一个重要限制:因为现在还没有约束和复合类型联结, 所以在表定义中显示的约束并不适用于表之外的复合类型。 (一个部分绕开的办法是使用域类型作为复合类型的成员。)

8.11.2. 复合类型值输入

要以文本常量书写复合类型值,在圆括弧里包围字段值并且用逗号分隔他们。 你可以在任何字段值周围放上双引号,如果值本身包含逗号或者圆括弧,你必须用双引号括起。 (更多细节见下面。)因此,复合类型常量的一般格式如下:

‘( val1 , val2 , … )’

一个例子是

‘(“fuzzy dice”,42,1.99)’

如果 inventory_item 类型在前面已经定义了,那么这是一个合法的数值。 要让一个字段是空,那么在列表里它的位置上不要写任何字符。比如,下面这个常量在第三个字段声明一个 NULL:

‘(“fuzzy dice”,42,)’

如果你想要一个空字串,而不是 NULL,写一对双引号:

‘(“”,42,)’

这里的第一个字段是一个非 NULL 空字串,第三个字段是 NULL。

(这些常量实际上只是我们在 Section 4.1.2.5 讨论的一般类型常量的一个特殊例子。 这些常量一开始只是当作字串,然后传递给复合类型输入转换过程。一个明确的类型声明可能是必须的。)

我们也可以用 ROW 表达式语法来构造复合类型值。 在大多数场合下,这种方法都比用字串文本的语法更简单,因为你不用操心多重引号。 我们已经在上面使用了这种方法了:

ROW(‘fuzzy dice’, 42, 1.99)
ROW(”, 42, NULL)

只要你在表达式里有超过一个字段,那么关键字 ROW 就实际上是可选的, 所以可以简化为

(‘fuzzy dice’, 42, 1.99)
(”, 42, NULL)

ROW 表达式语法在Section 4.2.11 里有更详细的讨论。

8.11.3. 访问复合类型

要访问复合类型字段的一个域,我们写出一个点以及域的名字,非常类似从一个表名字里选出一个字段。 实际上,因为实在太像从表名字中选取字段,所以我们经常需要用圆括弧来避免分析器混淆。 比如,你可能需要从 on_hand 例子表中选取一些子域,像下面这样:

SELECT item.name FROM on_hand WHERE item.price > 9.99;

这样将不能工作,因为根据 SQL 语法, item 是从一个表名字选取的,而不是一个域名字。 你必须像下面这样写:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者如果你也需要使用表名字(比如,在一个多表查询里),这么写:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

现在圆括弧对象正确地解析为一个指向 item 字段的引用,然后就可以从中选取子域。

类似的语法问题适用于在任何地点从一个复合类型值中查询一个域。 比如,要从一个返回复合类型值的函数中只选取一个字段,你需要写像下面这样的东西

SELECT (my_func(…)).field FROM …

如果没有额外的圆括弧,会产生一个语法错误。

8.11.4. 修改复合类型

下面是一些插入和更新复合类型字段的正确语法。首先,插入或者更新整个字段:

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE …;

第一个例子省略了 ROW,第二个使用它;我们用哪种方法都行。

我们可以更新一个复合字段的独立子域:

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE …;

请注意,这里我们不需要(实际上是不能)在 SET 后面出现的字段名周围放上圆括弧, 但是我们在等号右边的表达式里引用同一个字段的时候却需要圆括弧。

我们也可以声明子域是 INSERT 的目标:

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);

如果我们没有为字段的所有子域提供数值,那么剩下的子域将用空值填充。

8.11.5. 复合类型输入和输出语法

一个复合类型的文本表现形式包含那些根据独立的子域类型各自 I/O 转换规则解析的项, 加上一些表明这是复合结构的修饰。这些修饰包括整个数值周围的圆括弧(( 和 )), 加上相邻域之间的逗号(,)。圆括弧外面的空白被忽略,但是在圆括弧里面, 它被当作子域数值的一部分,根据该子域的数据类型,这些空白可能有用,也可能没用。 比如,在

‘(  42)’

里,如果子域类型是整数,那么空白将被忽略,但是如果是文本,那么就不会忽略。

如前面显示的那样,在给一个复合类型写数值的时候,你可以在独立的子域数值周围用双引号包围。 如果子域数值会导致复合数值分析器歧义,那么你必须这么做。 特别是子域包含圆括弧,逗号,双引号,或者反斜杠的场合,必须用双引号括起来。 要想在双引号括起来的子域数值里面放双引号,那么你需要在它前面放一个反斜杠。 (同样,在一个双引号括起的子域数值里面的一对双引号表示一个双引号字符,就像 SQL 字串文本的单引号规则一样。) 另外,你可以用反斜杠逃逸的方法保护所有可能会当作复合类型语法的数据字符。

一个完全空的子域数值(在逗号或者逗号与圆括弧之间没有字符)表示一个 NULL。 要写一个空字串,而不是一个 NULL,写 “”。

假如子域数值是空字串或者包含圆括弧,逗号,双引号,反斜杠或者空白,复合类型输出过程会在子域数值周围放上双引号。 (为空白这么处理不是必须的,但是可以增强易读性。)在一个子域数值里面嵌入的双引号和反斜杠将会写成两份。

注意请注意你写的任何 SQL 命令都首先被当作字串文本解析,然后才当作复合类型。 这就加倍了你需要的反斜杠数目。比如,要插入一个包含双引号和一个反斜杠的 text 子域到一个复合类型数值里, 你需要写

INSERT … VALUES (‘(“\\”\\\\”)’);

字串文本处理器先吃掉一层反斜杠,这样到大复合类型分析器的东西看起来像 (“\”\\”)。 然后,字串填给 text 数据类型的输入过程,变成 “\。 (如果我们面对的数据类型还会对反斜杠另眼相看,比如 bytea, 那么我们可能需要在命令里多达八个反斜杠以获取在存储的复合类型子域中有一个反斜杠。) 美元符包围(参阅 Section 4.1.2.2)可以用于避免双份反斜杠的问题。

提示在 SQL 命令里写复合类型值的时候,ROW 构造器通常比复合文本语法更容易使用。 在 ROW 里,独立的子域数值的写法和并非作为复合类型的成员书写的方法一样。

 

8.12. 对象标识符类型

PostgreSQL 在内部使用对象标识符(OID)作为各种系统表的主键。 同时,系统不会给用户创建的表增加一个 OID 系统字段(除非在建表时声明了 WITH OIDS 或者是配置参数 default_with_oids 设置成了真)。 类型 oid 代表一个对象标识符。除此以外还有几个 oid 的别名:regproc,regprocedure,regoper, regoperator,regclass,和 regtype。 Table 8-19 显示了概要。

目前 oid 类型是用一个无符号的四字节整数实现的。 因此,它是不够用于提供大数据库范围内的唯一性保证的, 甚至在单个的大表中也不行。因此,我们不鼓励在用户创建的表中使用 OID 字段做主键。OID 最好只是用于引用系统表。

oid 类型本身除了比较之外还有几个操作。 不过,它可以转换为整数,然后用标准的整数操作符操作。(如果你这么干, 那么请注意可能的有符号和无符号之间的混淆。)

OID 别名类型没有自己的操作,除指明的输入和输出过程之外。 这些过程可以为系统对象接受和显示符号名,而不仅仅是类型 oid 将要使用的行数值。别名类型允许我们简化为对象查找 OID 值的过程。 比如,检查和一个表 mytable 相关的pg_attribute 行, 我们可以写

SELECT * FROM pg_attribute WHERE attrelid = ‘mytable’::regclass;

而不用

SELECT * FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = ‘mytable’);

虽然看上去不坏,但是这个例子还是简化了好多,如果在不同的模式里有好多叫 mytable 的表,那么我们需要写一个更复杂的子查询。 regclass 的输入转换器处理根据模式路径设置的表检索工作,所以它自动干了”正确的事情”。 类似的还有,把一个表的 OID 转换成 regclass 是查找一个 OID 对应的符号名称的最简单方法。

Table 8-19. 对象标识类型

名字 引用 描述 数值例子
oid 任意 数字的对象标识符 564182
regproc pg_proc 函数名字 sum
regprocedure pg_proc 带参数类型的函数 sum(int4)
regoper pg_operator 操作符名 +
regoperator pg_operator 带参数类型的操作符 *(integer,integer)或者-(NONE,integer)
regclass pg_class 关系名 pg_type
regtype pg_type 数据类型名 integer

所有 OID 别名类型都接受有模式修饰的名字,并且如果在当前 搜索路径中,在不增加修饰的情况下无法找到该对象,那么在输出 时将显示有模式修饰的名字。regproc 和 regoper 别名类型将只接受唯一的输入名字(不能重载),因此它们的用途有限; 对于大多数应用,regprocedure 或regoperat或者 更合适。对于 regoperat或者,单目操作符是通过在那些未用的操作数 上写NONE来标识的。

OID 别名类型的一个额外的属性是如果这些类型之一的常量出现在一个存储的表达式里 (比如字段缺省表达式或者试图),它在被引用的对象上创建一个依赖性。 比如,如果一个字段有缺省的 nextval(‘my_seq’::regclass) 表达式, PostgreSQL 理解缺省表达式依赖于序列 my_seq; 系统将不允许在删除缺省的表达式之前删除该序列。

系统使用的另外一个标识符类型是 xid,或者说是事务(缩写xact) 标识符。它是系统字段 xmin 和 xmax 的 数据类型。事务标识符是 32 位的量。

系统需要的第三种标识符类型是 cid,或者命令标识符。 它是系统字段 cmin 和 cmax 的数据类型。 命令标识符也是 32 位的量。

系统使用的最后的标识符类型是 tid,或者说是元组标识符。 它是系统表字段 ctid 的数据类型。元组 ID 是一对儿数值 (块号,块内的元组索引),它标识该元组在其所在表内的物理位置。

(系统字段在 Section 5.4 里有更多解释。)

 

8.13. 伪类型

PostgreSQL 类型系统包含一系列特殊用途的 条目,它们按照类别来说叫做 伪类型。一个伪类型不能作为 字段的数据类型,但是它可以用于声明一个函数的参数或者结果类型。 每个可用的伪类型在一个函数不只是简单地接受并返回某种SQL数据类型的 情况下都很有用。 Table 8-20列出了现有的伪类型。

Table 8-20. 伪类型

名字 描述
any 表示一个函数接受任何输入数据类型
anyarray 表示一个函数接受任意数组数据类型(参阅 Section 32.2.5
anyelement 表示一个函数接受任何数据类型(参阅 Section 32.2.5)。
cstring 表示一个函数接受或者返回一个空结尾的 C 字串
internal 表示一个函数接受或者返回一种服务器内部的数据类型
language_handler 一个过程语言调用句柄声明为返回 language_handler
void 表示一个函数不返回数值
record 标识一个函数返回一个未声明的行类型
trigger 一个触发器函数声明为返回 trigger
opaque 一个已经过时的类型,以前用于所有上面这些用途

用 C 编写的函数(不管是内置的还是动态装载的)都可以声明为接受或者返回 这样的伪数据类型。在把伪类型用做函数参数类型的时候,保证函数行为正常 就是函数作者的任务了。

用过程语言编写的函数只能根据它们的实现语言是否可以使用伪类型而使用 它。目前,过程语言都不允许使用伪类型作为参数类型,并且只允许使用 void 和 record 作为结果类型(如果函数用做触发器,那么加上 trigger)。 一些多态的函数还支持使用 anyarray 和anyelement类型。

伪类型 internal 用于声明那种只能在数据库系统内部调用的函数, 它们不能直接在SQL查询里调用。如果函数至少有一个 internal 类型 的参数,那么我们就不能从SQL里调用它。为了保留这个限制的类型安全, 我们一定要遵循这样的编码规则:不要创建任何声明为返回 internal 的函数,除非它至少有一个 internal 参数。

 

 

分类上一篇:无,已是最新文章    分类下一篇:

Leave a Reply