POSTGRESQL数据库中的数据类型

6.13.3 访问数组

首先，演示一下如何访问数组的某个一个元素。下面的查询找出第二季度的薪水和第一季度的薪水不同的员工：

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

name

-------

Carol

(1 row)

数组的下标是写在方括号的。默认的情况下，PostgreSQL认为数组都是一维的。

下面的查询找出所有员工的第三季度的薪水：

SELECT pay_by_quarter[3] FROM sal_emp;

pay_by_quarter

----------------

10000

25000

(2 rows)

还可以访问一个数组的任意长方形片断，或者叫分片。对于一维或多维数组，一个数组的某一部分是用“下标下界:下标上界“表示的。例如：

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';

如果省略了下标下界，则下标下界默认为1，所以schedule[1:2][1]等价于schedule[1:2][1:1]，schedule[1:2][2]等价于schedule[1:2][1:2]。例如：

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

schedule

-------------------------------------------

{{meeting,lunch},{training,presentation}}

(1 row)

如果查询指定的分片的下标的下界超出了数组的下标的上界，系统不会报错，将会返回一个空数组，例如：

SELECT schedule[3:5][1:2] FROM sal_emp WHERE name = 'Bill';

schedule

----------

{}

(1 row)

如果查询指定的分片的下标的下界没有超出数组的下标的上界，但子数组的下标的上界超出了超出数组的下标的上界，系统也不会报错，会自动将子数组的下标的上界设为数组的下标的上界，例如：

SELECT schedule[1:5][2] FROM sal_emp WHERE name = 'Bill';

schedule

-------------------------------------------

{{meeting,lunch},{training,presentation}}

(1 row)

可以用array_dims 函数来得到任何数组的当前维数信息和和每个维对应的下标的上界与下界，例如：

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

array_dims

------------

[1:2][1:2]

(1 row)

也可以用 函数array_upper 和 array_lower 得到数组指定的维数下标的上界和下界。

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

array_upper

-------------

2

(1 row)

6.13.4 修改数组
6.13.4.1 使用UPDATE命令

可以一次更新数组的的所有元素，例如：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'

WHERE name = 'Carol';

或者使用数组构造器，例如：

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';

如果一个数组只有n个元素，使用UPDATE命令给数组的第n+m个元素赋值，那么数组将会变成一个有n+m个元素的数组，其中的第n到第n+m-1个元素会被自动赋为空值。当前只能对一维数组进行这样的操作。

在更新一个数组片段时，指定数组的下标可以为负数，更新操作结束后，数组的下标将以负数开始，而不是从1开始，例如，顺序执行下面的几个命令，注意观察输出的结果：

（1）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)

（2）select array_dims(pay_by_quarter) from sal_emp;

array_dims

------------

[1:4]

[1:4]

(2 rows)

（3）UPDATE sal_emp SET pay_by_quarter[-1:2] = '{3,3,27000,27000}';

（4）select array_dims(pay_by_quarter) from sal_emp;

array_dims

------------

[-1:4]

[-1:4]

(2 rows)

（5）select * from sal_emp;

name | pay_by_quarter | schedule

-------+--------------------------------------+-------------------------------------------

Bill | [-1:4]={3,3,27000,27000,10000,10000} | {{meeting,lunch},{training,presentation}}

Carol | [-1:4]={3,3,27000,27000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}

(2 rows)

6.13.4.2 数组连接运算符

可以用运算符 || 连接两个数组形成一个新的数组，例如：

（1）SELECT ARRAY[1,2] || ARRAY[3,4];

?column?

-----------

{1,2,3,4}

(1 row)

（2）ELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];

?column?

---------------------

{{5,6},{1,2},{3,4}}

(1 row)

连接运算符可以连接一个元素和一个一维数组，连接两个 N 维的数组，连接一个N维数组和一个N+1 维的数组。

如果连接一个元素和一个一维数组，结果数组的下标下界与参加运算的一维数组的下标下界相同，例如：

（1）SELECT array_dims(1 || '[0:1]={2,3}'::int[]);

array_dims

------------

[0:2]

(1 row)

（2）SELECT array_dims(ARRAY[1,2] || 3);

array_dims

------------

[1:3]

(1 row)

如果连接连接两个 N 维的数组，结果数组的下标下界与运算符左边的数组的下标下界相同，例如：

（1） SELECT array_dims('[-1:0]={2,3}'::int[] || ARRAY[1,2]);

array_dims

------------

[-1:2]

(1 row)

（2）SELECT array_dims(ARRAY[1,2] || '[-1:0]={2,3}'::int[]);

array_dims

------------

[1:4]

(1 row)

如果连接一个 N 维数组和一个 N+1 维的数组，N维数组将变成N+1数组的一个元素。例如：

（1）SELECT ARRAY[1,2] || ARRAY[[3,4],[5,6]];

?column?

---------------------

{{1,2},{3,4},{5,6}}

(1 row)

（2）SELECT ARRAY[[3,4],[5,6]] || ARRAY[1,2];

?column?

---------------------

{{3,4},{5,6},{1,2}}

(1 row)

也可以用函数array_prepend、array_append和array_cat连接两个数组。前面两个函数只支持一维数组，array_cat支持多维数组。最好使用连接运算符，不要直接使用这些函数，在用户自定义函数中如果有必要，可以直接使用这些函数。例如：

（1）SELECT array_prepend(1, ARRAY[2,3]);

array_prepend

---------------

{1,2,3}

(1 row)

（2）SELECT array_append(ARRAY[1,2], 3);

array_append

--------------

{1,2,3}

(1 row)

（3）SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);

array_cat

-----------

{1,2,3,4}

(1 row)

（4）SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);

array_cat

---------------------

{{1,2},{3,4},{5,6}}

(1 row)

（5）SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);

array_cat

---------------------

{{5,6},{1,2},{3,4}}

6.13.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;

如果数组中的元素过多，上面的方法显然过于繁琐。另外一个方法在第7.19节里描述。可以对数组使用ANY谓词，上面的查询可以用下面的例子来代替：

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

也可以对数组使用ALL谓词，下面的查询找出数组pay_by_quarter的所有元素的值都等于 10000 的员工的记录：

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

6.13.6 数组输入和输出语法

数组在被输出时，用一个文本字符串来表示，所有的数组元素用两个大括号（{和}）括起来，不同的数组元素用一个分割符分开。分隔符由数组元素的类型来决定，对于内置数据类型，除了box类型使用分号（;）以外，其它的数据类型的分隔符都是逗号（,），例如，下面是一个一位整型数组，它有4个元素：

{1,2,3,4}

对于多维数组，它的每一个维的数据都要用两个大括号({和})括起来，例如，下面是一个二维数组，假设它的名字是array_two_dims：

{{1,2},{3,4}}

array_two_dims[1][1]=1，array_two_dims[1][2]=2，array_two_dims[2][1]=3，array_two_dims[2][2]=4。

对于字符串类型的数组，如果它的某个元素的值中含有大括号、分隔符、双引号、反斜杠或空格，或者该值在转换成大写后是字符串NULL，在输出时，元素的值将用双引号引起来,而且值中的双引号和反斜杠前面将被自动加上一个反斜杠。在输出时，如果元素的值是空串，将会用两个双引号来表示，空值用NULL表示。

对于字符串类型的数组，推荐使用数组构造器来为数组输入数据，如果使用普通的字符串语法来输入数据，在元素的值中出现反斜杠和双引号的情况下，需要使用转义表示法，而且反斜杠数量要翻倍，下面的例子插入两个数组元素，第一个元素是反斜杠，第二个元素是双引号。

INSERT ... VALUES (E’{"////","//""}’);

这是因为字符串文本处理器会去掉第一层反斜杠，然后剩下的字符串就是{"//","/""}。 接着该字串被传递给 text 数据类型的输入函数，分别变成 / 和 "。

也可以用美元符号限定的字符串的格式来为数组输入数据，这样就可以避免使用双倍的反斜杠。

下面是一些实例：

CREATE TABLE test( name text[]);

insert into test values(E'{"////","//""}');

insert into test values(E'{"////","//""}');

insert into test values('{null,null}'); --数组的两个元素都是空值

insert into test values('{"null",""}'); --数组的第一个元素是字符串null，第二个元素是一个空串

insert into test values('{"ggg ",""}');

select * from test;

name

-------------

{"//","/""}

{"//","/""}

{NULL,NULL}

{"null",""}

{ggg,""}

(5 rows)

6.14 复合数据类型

复合数据类型由一个或多个域组成，每个域的数据类型可以是数据库的基本数据类型，也可以是复合数据类型，它类似于C语言中的结构，PostgreSQL 允许像使用简单数据类型那样使用复合数据类型 例如，一个表的某个列可以被声明为一个复合类型。

6.14.1 定义复合类型

使用命令CREATE TYPE创建一个复合类型，例如：

（1）CREATE TYPE complex AS (

r double precision,

i double precision

);

（2）CREATE TYPE inventory_item AS (

name text,

supplier_id integer,

price numeric

);

命令CREATE TYPE的语法类似于CREATE TABLE。

创建了复合数据类型以后，就可以用建表时使用它，例如：

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 test( name text[])；

CREATE TYPE test AS (

r double precision,

i double precision

);

错误： 表test已经存在。

6.14.2 复合类型值的输入和输出格式

复合类型常量的一般格式如下：

'( val1 , val2 , ... )'

例如，'("fuzzy dice",42,1.99)'就是前面创建的inventory_item类型的值。

如果值列表中的值的个数比复合类型的域的个数少，那么复合类型的排在前面的域将被赋给值列表中对应的值，剩下的域将被置为空值（NULL）。例如，下面这个常量将inventory_item的第三个域置为空值：

'("fuzzy dice",42,)'

如果复合数据类型的某个域的值是空串，则用两个双引号表示，例如：

'("",42,)'

另外如果想把某个域的值设为空值，逗号与逗号（或括号）之间不要出现任何字符。例如，下面的常量将inventory_item的第二个域设为空值：

'("",,1.8)'

下面的常量将inventory_item的所有域都设为空值：

'(,,)'

也可以用数据行构造器（相信信息参考第2.12节）的语法来构造复合类型值，推荐使用这种语法。这种方法通常比用字符串的语法更简单。在输入的字符串中含有反斜杠和双引号的情况下，必须使用转义语法，如果使用字符串的语法，反斜杠的数目必须翻倍（详细信息参考本章6.13.6 节），使用数据行构造器则要简单一些，反斜杠的数目不需要翻倍。 例如：

ROW('fuzzy dice', 42, 1.99)

ROW('', 42, NULL)

ROW(E'/"', 42,88),1000) --E'/"' 表示输入的是一个双引号

ROW(E'//', 42,88), 1000 --E'//'表示输入的是一个反斜杠

只要数据行构造器中出现的域的个数超过一个，ROW关键字也可以被省略，例如，ROW('fuzzy dice', 42, 1.99)和('fuzzy dice', 42, 1.99)是等价的。

复合数据类型的值在被输出时，如果它的某个域是字符串类型，而且这个域的值中含有反斜杠和双引号，那么一个反斜杠将用两个反斜杠表示，一个双引号将用两个双引号表示。空串用两个双引号表示。如果字符串里面含有逗号、双引号、括号、空格和反斜杠，这个字符串将用两个双引号括起来，其它的字符串不会用双引号括起来。例如：

INSERT INTO on_hand VALUES (ROW(E'/"', 42,88), 1000);

INSERT INTO on_hand VALUES (ROW(E'//', 42,88), 1000);

INSERT INTO on_hand VALUES (ROW('', 42,88), 1000); --注意，ROW里面的第一值是两个单引号，不是一个双引号

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

INSERT INTO on_hand VALUES (ROW('(fuzzy', 42, 1.99), 1000);

INSERT INTO on_hand VALUES (ROW('fuzzy', 42, 1.99), 1000);

test=# select * from on_hand;

item | count

--------------------------+-------

("""",42,88) | 1000

("//",42,88) | 1000

("",42,88) | 1000

("fuzzy dice",42,1.99) | 1000

("(fuzzy",42,1.99) | 1000

("fuzzy dice",42,1.99) | 1000

("(fuzzy",42,1.99) | 1000

(fuzzy,42,1.99) | 1000

(8 rows)

6.14.3 访问复合类型的值

要访问一个复合类型的值的某个域，可以使用类似下面的语法：

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

列名必须用括号括起来，如果item没有用括号括起来，系统会认为item是一个表的名字，而不是表on_hand中的列的名字，也可以在列名前面加上表的名字，例如：

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

如果一个函数返回一个复合类型的值，要访问这个值的某个域，也要使用上面的语法，例如：

SELECT (my_func(...)).field FROM ...

6.14.4 更新复合类型的值

如果要更新一个复合类型的值的所有域，使用类似下面的语法：

UPDATE mytab SET complex_col = ROW(1.1,2.2);  --假定表mytab的列complex_col的数据类型是在前面的例子中(在第6.14.1节里定义)定义的类型complex

也可以只更新一个复合类型值的某个域：

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

注意，不能把在 SET 后面出现的列名用括号括起来，但是在等号右边的表达式中引用同一个列时要把列名用括号括起来。

INSERT语句中也可以指定只为列的某些域提供值，例如：

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

如果没有为列的所有域提供值，那么剩下的域将用空值填充。

6.15 对象标识符类型（oid）

oid 类型是一个无符号的四字节整数。PostgreSQL 在数据库内部使用oid类型的列作为各种系统表的主键。用户创建的表也可以有一个类型为oid的列（建表时使用WITH OIDS子句），因为oid类型只是一个四字节的整数，在一个很大的表中，oid的值可能不唯一，所以不要在用户创建的表中使用 oid 类型的列作为主键。oid类型的列对于用户建立的表没有任何作用，最好不要在用户创建的表中添加系统列oid。

6.16 伪类型

PostgreSQL中有一种特殊的数据类型，这种数据类型叫伪类型。一个伪类型不能作为表的列的数据类型。伪类型只能被用作函数的参数的数据类型和函数的返回值的数据类型。表6-21列出了所有的伪类型。

表 6-21. 伪类型

类型名称	描述
anyarray	表明函数接受或返回任意数组类型
anyelement	表明函数接受或返回任意数据类型
anyenum	表明函数接受或返回任意枚举类型
anynonarray	表明函数接受或返回任意非数组类型
cstring	表明函数接受或返回任意以/0结束的字符串
internal	表明函数接受或返回任意服务器内部的数据类型
record	表明函数返回一个不确定的数据行行类型
trigger	表明函数将在触发器中使用。
void	表明函数没有返回值

用 C语言编写的函数（无论是数据库内置的还是动态装载的）都可以接受或者返回任意的伪数据类型。函数的作者应该保证函数在使用伪数据类型的数据时可以正常地工作。

用过程语言编写的函数只能使用适用于该过程语言的伪数据类型。当前，所有的过程语言都能使用使用 void 和 record 作为函数返回值的数据类型（如果函数用做触发器，它的返回值的类型必须是trigger）。PL/pgSQL 还支持anyelement、anyarray、anynonarray和anyenum作为函数的参数和返回值的数据类型。

伪类型internal用于声明那些只能在数据库系统内部被调用的函数，这些函数不能在用户的SQL查询里被直接调用。如果函数至少有一个internal 类型的参数，SQL查询就不能直接调用它。只有在函数除至少有一个 internal类型的参数的情况下，才能将函数的返回值的类型定义为internal类型，一定要遵守这条规则。

anyelement、anyarray、anynonarray和anyenum又被叫做多态数据类型，使用这些数据类型的函数叫做多态函数。多态函数的参数的数据类型是不确定，实际的数据类型由传递给函数的参数的数据类型决定。anyelement表示任意数据类型，包括数组类型。anyarray表示任意数组类型。anyenum表示任意枚举类型。anynonarray表示任意非数组类型。

如果一个函数的返回值的数据类型是多态类型，那么该函数至少有一个参数的类型是多态类型。