PostgreSQL数据类型

http://blog.csdn.net/neo\_liu0000/article/category/797059

 

第六段  数据类型

6.1概述

 

    PostgreSQL 提供了丰富的数据类型。用户可以行使 CREATE TYPE
命令于数据库中创造新的数据类型。PostgreSQL
的数据类型被分成四种,分别是主导数据列、复合数据类型、域和伪类型。

 

   
基本数据列是数据库内置的数据类型,包括integer``、``char``、``varchar``等数据类型。表``6-1``列出了PostgreSQL提供的享有骨干数据列。复合数据类型是用户自己定义的,使用CREATE
TYPE命令就能创造一个复合数据类型。域是平等种植特别的主导数据列,它由中心数据类丰富一个绳规范做,使用CREATE
DOMAIN命令就能创造一个域,关于域的详细信息,参考《SQL命令手册》对CREATE
DOMAIN命令的诠释。伪类型是负有特殊作用的数据类型,这些数据类型不能够当做表底排的数据类型,只能当作函数的参数或返回值的数据类型。

 

    下面的小节将见面详细介绍中心数据列、复合数据类型和伪类型。

表 6-1. 核心数据类

名字

描述

bigint

起号 8 字节整数

bigserial

于添八字节整数

bit [ (n) ]

定长位串

bit varying [ (n) ]

转换长位串

boolean

逻辑布尔量 (真/假)

box

面中的长方形

bytea

老二迈入制数据(”字节约数组”)

character varying [ (n) ]

转移长字符串

character [ (n) ]

定长字符串

cidr

IPv4 或者 IPv6 网络地址

circle

面中之圆满

date

日历日期(年,月,日)

double precision

双双精度浮点数字

inet

IPv4 要么 IPv6 网络地址

integer

季许节长有号整数

interval [ (p) ]

光阴间隔

line

面中的绝加上直线

lseg

平面中之线条

macaddr

MAC 地址

numeric [ (p, s) ]

而是摘精度的可靠数字

path

平面中之几哪里途径

point

面中的接触

polygon

平面中之封几何途径

real

独精度浮点数

smallint

来号两许节整数

serial

自打加四配节整数

text

更换长字符串

time [ (p) ] [ without time zone ]

平上里的时光

time [ (p) ] with time zone

一如既往龙里之年华,包括时区

timestamp [ (p) ] [ without time zone ]

日期和时

timestamp [ (p) ] with time zone

日子与时空

tsquery

全文检索查询

tsvector

全文检索文档

txid_snapshot

用户级别事务ID快照

uuid

通用唯一标识符

xml

XML数据

兼容性: 下列类型是在SQL标准被定义之:
bitbit varyingboolean
charcharactercharacter varyingvarchardate
double precisionintegerintervalnumericdecimal
realsmallinttime (包括发生时区和无时区底), timestamp
(包括有时区和无时区之)。

   
PostgreSQL的词法分析器在条分缕析用户发之SQL命令时,首先将里面的特词分成五类:整数、非整数数字、字符串、标识符和严重性字。大部分之非数值常量首先给当是字符串。

 

    SQL语言提供了明确地指定字符串的类型的机制。例如:




SELECT 'Origin':: text AS "label", '(0,0)':: point AS "value";

 

 label  | value

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

 Origin | (0,0)

(1 row)

 

于地方的例证中,用户指定’Origin’ 的种是text,'(0,0)’的类是
point。如果用户并未明确地指定同’Origin’和'(0,0)’的数据类型,系统先拿它们的档次设为`unknown“,以后再确定它的实际数据类型。“`

 

6.2 数值类

   
数值类包括2、4或8字节的整数,4还是8字节之浮点数和得定义精度的十上前制数。
表6-2 列有了颇具数值类。

表6-2. 数值类

名字

积存空间

描述

取值区间

smallint

2 字节

小整数

-32768 到 +32767

integer

4 字节

常用之整数

-2147483648 到 +2147483647

bigint

8 字节

大整数

-9223372036854775808 到 9223372036854775807

decimal

变长

用户定义精度,可以规范地代表小数

无限制

numeric

变长

用户定义精度,可以准确地表示小数

无限制

real

4 字节

精度可变,不克纯粹地代表小数

精度是6个十上制位

double precision

8 字节

精度可变,不克精确地代表小数

精度是15独十前行制位

serial

4 字节

稍稍范围之自增整数

挺范围的自增整数

bigserial

8 字节

好范围之自增整数

1 到 9223372036854775807

    数值类常量的语法在第1.4.4节里描述。
数值类型有一致套完整的数学运算符和函数。相关消息请参考第7章节。下面将详细描述这些类别。

6.2.1 整数类型

    类型 smallintintegerbigint
只能保留整数,也就是没有小数部分的数字。如果打算在一个整数品种受到保留一个越她亦可代表的值范围的整数,数据库将会见报错。

   
常用之类是integer,因为它提供了在表示范围、存储空间与特性之间的特级平衡。只有以磁盘空间紧张之情事下才使
smallint。只有在 integer绝小的上才祭
bigint,因为以拓展数学运算时,interger类型的数据bigint类型的数量而赶紧。

    SQL标准就定义了整数类型 integer(或int)、smallint``和``bigint

6.2.2 任意精度数值

numeric品类最多会积存有1000独数字位的数字又能够拓展规范之数值计算。它最主要用来需要规范地意味着数字之场地,如圆金额。不过,对numeric
类型进行算术运算比整数类型以及浮点类型要慢很多。

numeric列有三三两两独术语,分别是标度和精度。numeric``类型的标度(scale)是到稍微数沾右侧有小数员之个数,
numeric 的精度(precision)是享有数字位的个数,因例如, 23.5141
的精度是6如果标度为4。可以看整数的标度是碎。

numeric
类型的顶老精度和太老标度都是得安排的。可以用底的语法定义一个numeric类型:

(1)NUMERIC(precision, scale)

(2)NUMERIC(precision)

(3)NUMERIC

 

   
精度必须也正数,标度可以呢零或者正数。在面的第二栽语法中莫点名标度,则系统会将标度设为0,所以NUMERIC(precision,0)``和NUMERIC(`precision)是等价的。第三种类型的语法没有指定精度和标度,则这种类型的列可以接受任意精度和标度的numeric数据(在系统能表示的最大精度范围内),而不会对输入的数据进行精度或标度的变换。如果一个列被定义成numeric类型而且指定了标度,那么输入的数据将被强制转换成这个标度(如果它的标度比定义列的numeric“的标度大),进行标度转换时之条条框框是四放弃五合。如果输入的数开展标度转换后获得的数目在有些数接触左边的数据位的个数超过了排的色的精度减去标度的不比,系统以会报告相近下面的荒谬:“`

`错误:  numeric种数据溢出。“`

`细节:  precision3, scale3的数得给四放弃五契合成小于1的数。“`

`    “下面是一个实例:“`

create table test ( col1 numeric(3,3));

insert into test values(0.5678);

insert into test values(0.5671);

insert into test values ( 1.4);

`错误:  numeric种类数据溢出。“`

`细节:  precision3, scale3的再三要吃四放弃五入成小于1的数。“`

=>select * from test;

col1

-------

0.568

0.567

`(2 rows)“`

numeric 类型接受一个例外之值
NaN``”,它的意思是“不是一个数字”。任何在 NaN
上面的操作都生成另外一个 NaN。 如果当 SQL
命令里将这些值当作一个常量写,必须管其之所以单引号引起来,比如
UPDATE table SET x = 'NaN'。在输入时,”NaN”的大小写无关紧要。

 

  
注意:于任何的数据库被,NaN和其它的数值数据都非抵,两只NaN也是未齐的,在postgresSQL中,为了索引实现之惠及,NaN被当大于或等于有非NaN的数值。

 

    类型
decimalnumeric凡是相等价格的,两栽档次且是SQL标准定义的,SQL标准要求numeric的默认精度应该是0,PostgreSQL没有执行之规则,为了增强程序的移植性,最好还要指定numeric的精度和标度。

6.2.3 浮点类型

数据类型 realdouble precision
表示不纯粹的转移精度之数字。这些项目实现了IEEE
标准754次之进制浮点数算术(分别针对应单精度与双精度)。

 

   
不纯粹之意是有些数值不克纯粹地用real和double precision``表示,积存于数据库里的只是其的贴近似值。如果要求准确地保存某些数值(比如计算货币金额),应使用
numeric
类型。另外,比较少单浮点数是否等时,可能会见沾意想不到的结果。

    通常,real
类型的表示的数值范围是起码-1E+37至+1E+37,精度至少是6位小数。double precision
类型表示的限制通常是-1E+308至+1E+308
,精度是最少15员小数。太可怜还是太小的数值还见面导致错误。如果输入数据的精度太强,会受盖成可以叫奉之精度。太接近零的数字,如果和0的里表示形式一样,会生下溢(underflow)的失实。

    浮点类型还有几独独特值:

Infinity
-Infinity
NaN

这些价值分别表示 IEEE 754标准中之异值”正无根本大”,”负无穷大”,
以及”不是一个数字”。如果当 SQL
命令里将这些数值作为常量写,必须在她用单引号引起来,例如UPDATE table SET x = 'Infinity'
输入时,这些价值的高低写无关紧要。

 

注意:IEEE
754规范要求NaN和另外的数值数据都不顶,两只NaN也是免齐的,在postgresSQL中,为了索引实现之方便,NaN被当大于或顶所有非NaN的数值。

 

    PostgreSQL 还支持 SQL 标准被定义的型floatfloat(p)p
定义以二进制位表示的最低可以接受的精度,p的取值在1暨53间。实际上,如果p的取值在1顶24中,float(p)受用作是real类型,如果p的取值在25到53次,float(p)叫当做是double precision``类型。不带精度的float被当作是`double precision“类型。“`

6.2.4 序列号项目(Serial)

serialbigserial
并无是真的的数据类型,只是以可以被表中的数据行设置一个唯一的标识。它仿佛其他一些数据库中之
AUTO_INCREMENT 属性。使用它的法门如下:

CREATE TABLE tablename (

        colname SERIAL

);

方的吩咐实际上上等价于下面的有数久命令:

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename(

        colname integer DEFAULT nextval(‘tablename_colname_seq’)
NOT NULL

);

 

   
上面的一声令下于表明中开创了一个类型也无符号整数的排列,该列与一个队列对象相关联,这个队列对象的初始值是1,
表中各插入一漫漫新的记录,该队的值会自动加相同,在向表中插入数据时,INSERT命令不要为该列指定数量,或者指定它的值也DEFAULT。

    下面是一个实例:

create table test3 ( product_id serial, name char(5));

insert into test3(name)  values(‘pen’);

insert into test3(name)  values(‘car’);

insert into test3  values(DEFAULT, ‘bike’);

=>select * from  test3;

product_id | name

————+——-

          1 | pen

          2 | car

          3 | bike

(3 rows)

 

  
 注意:insert命令中得不要啊serial或bigserial类型的排列指定一个无是DEFAULT的价值,因为对此这么的授命系统是无会见报错的,会造成serial或bigserial类型的排列上之数值出现紊乱。

6.3 字符类型

表 6-3. 字符类型

名字

描述

character varying(n), varchar(n)

变长,最特别长有限定

character(n), char(n)

定长, 不足上空白

text

变长,最可怜尺寸没有限定

     表6-3排列有了PostgresSQL中可运用的字符类型。

    SQL标准定义了有限种植为主的字符类型:character varying(n)
character(n),这里的n大凡一个刚刚整数。两种植档次最多可储存n只字符。试图存储更增长的字串到这些类别的列里,系统会报错,除非有超出长度n的字符都是空格(这种气象下该字符串将被截断成长度为n的字符串)。如果要存储的字符串的尺寸比n小,类型也
character 的列将自动用空格填充该字符串,使其的尺寸达到n,而路也
character varying 的排直接保存该字符串,不见面针对其进行其它处理。

    如果明确将把一个数值转换成为character(n)或者
character varying(n)``类型,如果换之后的字符串的长逾n,那么其将给机关截断成长度为n的字符串,系统非见面报错(这也是SQL标准要求的)。

char(n)varchar(n) 分别是
character(n)character varying(n)的号。没有概念长度的character
等同于
character(1)。没有概念长度的character varying路接受任意长度的字符串,这是PostgreSQL的壮大特性。

 

另外,PostgreSQL
提供了text类型,它可以储存任意长度的字符串,而且长度没有尽酷范围。尽管SQL标准被从不定义text类型,但许多别样
SQL 数据库系统被出此类别。

 

character(n)``类型的数据以存储时长不足n的字符串会就此空格填充,在展示数据常常也会管填充的空格显示出来,但是以可比少单character``类型的价值的时,字符串的兼具结尾空格符号将活动为忽视,在转移成另外字符串类型的时候,character``类型的值内结尾的空格字符都见面于删。请小心,对于
character varying
text``类型的价值,结尾的空格在拍卖时凡无会见叫忽略的。

对于character(n)
character varying(n)``类型,许存储的绝丰富字符串所占据的仓储空间约1GB。如果想囤积长度没有上限的长字串,那么用
text``类型或者没有点名长度的character varying

 

   提示:
这三种植多少列中从未性能差别,不过character(n)character varying(n)``类型多使用了物理存储空间
虽然于好几其他的数据库系统里,character(n)
character varying(n)``快一些, 但在 PostgreSQL
里没有这种情形。在大部分动静下,应该运用text或者character varying

要参见第1.4.1节和1.4.2节得到字符串常量的底语法信息,参考第7.4节得到处理字符串的运算符和函数的信。数据库的字符集决定用于存储文本值的字符集,有关字符集的详细信息,请参考《数据库管理员指南》第5章节。

 

   下面是一个应用字符串的实例:

CREATE TABLE test1 (a character(4));

INSERT INTO test1 VALUES (‘ok’);

INSERT INTO test1 VALUES (‘ok ‘);  –ok后面和了一个空格

SELECT a, char_length(a) FROM test1; –函数char_length``在第7.4节中出详实介绍.

  a   | char_length

——+————-

 ok   |           2

 ok   |           2

(2 rows)

 

CREATE TABLE test2 (b varchar(5));

INSERT INTO test2 VALUES (‘ok’);

INSERT INTO test2 VALUES (‘good      ‘);

INSERT INTO test2 VALUES (‘too long’);

不当:  输入的字符串对于项目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

 

    在 PostgreSQL 还有另外两种定长字符串类型,在表6-4
里亮。这两种类型是供系统内部使用的,应用程序不应该使用这两种类型。name类型长度当前必定为
64 字节(63
可用字符加上了符)。类型"char"(注意引号)和char(1)凡是匪一样的,它独自占一个字节的囤空间,它在网中当枚举类型用。

发明6-4。 特殊字符类型

名字

积存空间

描述

"char"

1 字节

单字节里面类型

name

64 字节

针对象名的里边类型

 

6.4 二进制数据类型

bytea 类型可以储存二迈入制字符串,如表6-5所显示。

 

表6-5. 老二进制数据类型

名字

囤空间

描述

bytea

1要么4 字节加上实际的第二上制字符串

变长的次向前制字符串

  

老二前行制字符串是一个字节数值的队。SQL
标准定义了同种不同的亚进制字符串类型,叫做 BLOB 或者
BINARY LARGE OBJECT``,彼输入格式和 bytea
不同,但是提供的函数和操作符大多一样。``bytea``类型数据的具体含义由应用程序自己决定,数据库也提供了和普通文本字符串的处理方式类似的方法来对``bytea``类型数据进行输入和输出。

 

可使字符串常量的语法来输入bytea类型的数据,对突出的字符如单引号、反斜杠、不可打印的字符以及0,要采用转义表示法,具体用法而表6-6所显示。

 

表6-6. `需要展开转义处理的字符“`

十向前制数值

描述

输入格式

例子

出口格式

0

'//000'

select '//000'::bytea;

/000

39

单引号

'/'' ``或者`` '//047'

select '/''::bytea;

'

92

反斜杠

'////' ``或者`` '//134'

select '////'::bytea;

//

0 到 31 和 127 到 255

不可打印的字符

'//xxx' (八前进制值)

SELECT '//001'::bytea;

/001

   

bytea类型的多少以出口时为要是拓展转义处理,反斜杠用有限个反斜杠表示,不可打印的字符用反斜杠加上表示它的价值的老三个八上制位表示,可打印的字符用它们自己表示。如表6-7所著。

表6-7. bytea 出口格式

十上前制数值

描述

转义以后的出口个数

例子

出口结果

92

反斜杠

//

select '//134'::bytea;

//

0 ``到`` ``31 ``和`` ``127 ``到`` ``255

不行打印的八向前制字符

/xxx(octal value)

select '//001'::bytea;

/001

32 ``到`` ``126

但是打印的八迈入制字符

客户端字符集表现形式

(``1``)``select '//175'::bytea;

(``2``)``select '//165//166'::bytea

(``1``)``}

(``2``)``uv

 

6.5 日期/时间项目

    PostgreSQL 支持
SQL标准中有所的日期及时间档次,如表6-8所出示。这些数据类型上得进行的操作以第7.9节里描述。

表6-8. 日期/时间档次

名字

储存空间尺寸

描述

最小值

最大值

分辨率

timestamp [ (p) ] [ without time zone ]

8 bytes

席卷日期与日

4713 BC

294276 AD

1微妙/ 14 位

timestamp [ (p) ] with time zone

8 bytes

包日期及时,带时区

4713 BC

294276 AD

1微妙/ 14 位

interval [ (p) ]

12 bytes

光阴间隔

-178000000 年

178000000 年

1微妙/ 14 位

date

4 bytes

只是发生日期

4713 BC

5874897 AD

1 天

time [ (p) ] [ without time zone ]

8 bytes

惟有时间

00:00:00

24:00:00

1微妙/ 14 位

time [ (p) ] with time zone

12 bytes

除非时间,带时区

00:00:00+1459

24:00:00-1459

1微妙/ 14 位

timetimestamp
interval好定义精度值p,这个精度值定义用来代表秒的略数位的个数,默认的景况下,没有精度限制。对于timestampinterval``,``p``的取值范围是``0``到``6``(实际的精度可能小于``6``)。对于time``,``p``的取值范围是``0``到``10``。

 

   
类型time with time zone举凡SQL标准定义的,这个项目有些多余。在大部情景下,datetimetimestamp without time zone
timestamp with time zone 的整合就能满足任何利用需求。

    类型 abstimereltime
是低分辨率时间项目,它们是数据库中采用的类,在应用程序里面未应有采取即时点儿单种类。

6.5.1 日期/时间输入

     日期和时空可以据此多格式来代表,包括 ISO
8601、SQL标准被定义之格式等。对于片格式,日期输入里之月份及天的象征或会见生出歧义,如果参数DateStyle
被安装为 MDY,数据库将照“月-日-年”的格式来诠释输入的数量,DMY
表示“日-月-年”,而 YMD表示“年-月-日”。

 

   
PostgreSQL在拍卖日期/时间输入上于SQL标准要灵活得差不多。像一个文本字符串一样,任何日期或时间之输入还得用单引号括起来。SQL标准定义之语法如下:

type [ (p) ] ‘value

    对于timetimestamp
interval类的数额足以指定精度p,p的取值范围上平等节就摆了。如果无定义p,默认是输入的时常量的精度。
表6-9、6-10暨6-11列有了日期/时间数额以输入和输出时使用的关键字。

表6-9. 表示月之最主要字

缩写

January

Jan

February

Feb

March

Mar

April

Apr

May

 

June

Jun

July

Jul

August

Aug

September

Sep, Sept

October

Oct

November

Nov

December

Dec

发明6-10. 表示天的重中之重字

缩写

Sunday

Sun

Monday

Mon

Tuesday

Tue, Tues

Wednesday

Wed, Weds

Thursday

Thu, Thur, Thurs

Friday

Fri

Saturday

Sat

 

表6-11. 日子/时间域修饰符

标识符

   描述

ABSTIME

忽略

AM

12:00在先的流年

AT

忽略

JULIAN, JD, J

下一个域用儒略日表示(Julian Day)

ON

忽略

PM

12:00下的日

T

产一个域凡是岁月

 

6.5.1.1 日期

   表6-12 显示了date 类型可能的输入格式。

表6-12. 日期格式

例子

描述

January 8, 1999

无论参数datestyle取任何价值,都没有歧义

1999-01-08

ISO-8601 格式,任何模式下还是1999年1月8如泣如诉(推荐使用该格式)

1/8/1999

有歧义,在MDY下是1月8日;在 DMY 模式下是召开8月1日

1/18/1999

MDY模式下是1月18日,其它模式下为拒

01/02/03

MDY 模式下之2003年一月2日; DMY 模式下之 2003 年 2月 1日; YMD
模式下的2001年二月三日

1999-Jan-08

别模式下还是1月8日

Jan-08-1999

外模式下还是1月8日

08-Jan-1999

旁模式下都是1月8日

99-Jan-08

YMD 模式下是1月8日,其它模式报错

08-Jan-99

1月8日,YMD 模式下会报错

Jan-08-99

1月8日,YMD 模式下会报错

19990108

ISO-8601; 任何模式下还是1999年1月8日

990108

ISO-8601; 任何模式下还是1999年1月8日

1999.008

年与年里的第几天

J2451187

儒略日

January 8, 99 BC

公元前99年

6.5.1.2 时间

      时间项目包括 time [ (p) ] without time zone
time [ (p) ] with time zone。 只写 time 等同于
time without time zone。对于项目time [ (p) ] with time zone``,需要同时指定时间和日期。如果在
time without time zone 类型的输入被指定了时区,时区会吃忽视。

表6-13. 时间输入

例子

描述

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

拉动齐的时区

表明6-14. 时区输入

例子

描述

PST

太平洋规范时间(Pacific Standard Time)

America/New_York

时区全称

PST8PDT

POSIX风格的时区名称

-8:00

ISO-8601 与 PST 的偏移

-800

ISO-8601 与 PST 的偏移

-8

ISO-8601 与 PST 的偏移

Zulu

军方对 UTC 的缩写(译注:可能是美军)

Z

zulu 的缩写

视图``pg_timezone_names``列出了所有可以识别的时区名``。

6.5.1.3 时间戳(timestamp)

      
时间戳类型的输入由一个日子及日的通组成,后面随着一个可选的时区,一个可选的
AD 或者 BCAD/BC
可以出现在时区前面,但最好放在时区的后面)。下面是片只实例,它们兼容ISO
8601:

(1)1999-01-08 04:05:06

(2)1999-01-08 04:05:06 -8:00

 

尚足以用下的格式

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。在PostgreSQL中,timestamp without time zone``类型的常量前面必须加上TIMESTAMP
WITH TIME ZONE, 例如,TIMESTAMP WITH TIME ZONE ‘2004-10-19
10:23:54+02’。

`timestamp without time zone“类型的常量中一旦出时区信息,时区信息会受网活动忽略。“`

timestamp with time zone项目的数额的里存储的格式总是UTC(全球统一时间,以前为叫格林威治时间GMT)。如果一个输入值中指定了时区,系统将为该时区为基于用其换为UTC格式,如果在输入的价值备受没有点名声明,系统以参数timezone的值当指定时区为因,将它换为UTC格式。

 

假如只要出口一个 timestamp with time zone``类型的数据,它连接打
UTC被反至参数timezone指定的时区,并给出示也该时区的本地时间。
要看其他时区的该日,要么修改 参数参数timezone``的值,要么使用
AT TIME ZONE 子句(参考第7.9.3节)。

 

       在 timestamp without time zonetimestamp with time zone
之间的拓转换是日常假设 timestamp without time zone
数值的时区是参数timezone 指定的时区。可以用AT TIME ZONE
指定其他的时区。

6.5.1.4 时间间隔

`` ``interval``类型的数值可用底语法来定义:

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

这里quantity 是一个数字(可能出记号),unitmicrosecond
millisecond``、``secondminute,、hourday
weekmonthyeardecadecenturymillennium要这些单位之缩写或复数,direction
可以是 ago 或者也空。符号 “@``” 是可选的,可以不写。

 

    天、小时、分钟及秒的数值的尾可以不用强烈地同单位。
比如,“``1 12:59:10``”
“``1 day 12 hours 59 min 10 sec``”大凡齐价格的。

    可挑选精度 p 的取值在0到6 之间,默认是输入的常量的精度。

 

6.5.1.5 特殊值

        PostgreSQL
为好起见支持几个例外输入值,所有这些价值在SQL命令里作为常量使用时,要为此单引号引起来。now、today、tomorrow和yesterday``在被读取时会被自动转换为普通的日期或时间值。

表 6-15. 非同寻常日期/时间输入

输入的字符串

中的数据类型

描述

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_DATECURRENT_TIMECURRENT_TIMESTAMPLOCALTIME
LOCALTIMESTAMP。最后四只函数接受一个可选的秒的精度值(在第7.9.4节里对这些函数有详细描述)。

6.5.2 日期/时间输出

        时间/日期类型的输出格式来四栽:ISO
8601、SQL(Ingres)、传统的POSTGRES和German。可以用命令SET datestyle``来设置时光/日期类型的出口格式,``默认是
ISO 格式(SQL 标准要求采取 ISO 8601 格式)。表 6-16
列有了了各个种输出格式的实例。

表 6-16. 日期/时间输出格式

类型

描述

例子

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

    如果参数datestyle里发生DMY
信息,在SQL和POSTGRES风格里,日期在月前出现,否则月份面世于日期之前。表6-17
是另外一些实例。

表 6-17. 日期顺序

类型

描述

例子

SQL, DMY

//`年“`

17/12/1997 15:37:16.00 CET

SQL, MDY

//`年“`

12/17/1997 07:37:16.00 PST

Postgres, DMY

//`年“`

Wed 17 Dec 07:37:16 1997 PST

`   “`

``interval 的出口格式和输入格式类似,centuryweek
被转移成年和天,而 ago 被转换成适量的标志。在 ISO 模式下输出格式如下:

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

 

   
可以为此命令SET datestyle来安装日期/时间的出口格式,也得以当文件postgresql.conf
里修改参数DateStyle 的价值。也堪用函数to_char(参阅第7.8节)更灵活地决定输出格式。

6.5.3时区

     PostgreSQL 目前支持 1902 年交 2038 年之内的夏时制时间(对应为人情
Unix
系统时之表示的限量)。如果日跨这个限制,那么要时间是选的时区的”标准日”。SQL
标准在日期及岁月档次及效应及出局部乱七八糟,下面是片只常见的题材:

(1)``date (日期)类型以及时区没有涉及,而 time
(时间)类型也生要可跟时区关联。然而现实世界的时区必须同同时光跟日期同时涉嫌才发生义。

(2)默认的时区用同UTC的撼动来代表。因此,当以 DST
的境界进行日期/时间运算时,无法用日变成为夏时制时间。

 

   
为了化解这些问题,建议以运时区的时候,使用那些以涵盖日期以及日的日期/时间档次。建议不要动类
time with time zone (保留此类型是为同SQL标准相当)。

    PostgresSQL有三种办法来指定时区名:

(1)使用时区的齐全,例如`America/New_York,视图pg_timezone_names“列出了拥有可以识别的时区名。“`

(2)使用时区的缩写,例如`PST,视图pg_timezone_names“列出了具备可以识别的时区缩写。“`

`(3POSXI作风的时区表示拟,例如PST8PDT。“`

每当实际的采取被,最好以全称的时区名。参数``timezone``和``log_timezone``的值不能使用缩写的时区表示方式,运算符``AT TIME ZONE``可以使用缩写的时区表示方式。时区名称不分轻重缓急写,所有的时区信息寄存于数据库软件之安装目录的子目录.../share/timezone/
和`…/share/timezonesets/“里面。“`

    可以在 文件postgresql.conf 里设置配置参数
timezone,还好为此底的不二法门来装时区:

若果文件postgresql.conf
里没有安装timezone,服务器试图以服务器主机及的操作系统环境变量TZ的价当服务器的默认时区。
如果没定义TZ,或者TZ的价是 PostgreSQL 无法识别的时区,
那么服务器将由此检查C 库函数 localtime()
的一言一行规定来操作系统的默认时区(如果postgresql.conf
里没有安装参数log_timezone``,这些规则为就此来规定参数log_timezone的值)。

SQL 命令 SET TIME ZONE 为会说话设置时区,它等于与命令SET TIMEZONE TO。

 

6.5.4 内部贯彻

    PostgreSQL 使用儒略历法(Julian dates)来开展富有的日子/时间测算。
这种方式假要同一年之长是365.2425天,它可好确切地测算起4713
BC(公元前4713年)到深远之底前景底肆意一上的日期。

6.6 布尔项目

    PostgreSQL 支持SQL标准被定义之 boolean
数据类型。boolean``类型只好发出星星点点单取值:真(True)
或借用(False)。空值表示状态未知(unknown)。可以采用下列常量来表示”真”,它们是相当价格的,推荐用TRUE:

TRUE

't'

'true'

'y'

'yes'

'1'

动下列常量来表示假,它们是齐价格的,推荐使用FALSE:

FALSE

'f'

'false'

'n'

'no'

'0'

 

    下面是运布尔档次的实例:

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

 

`   “布尔项目在蕴藏时占用一个字节的空中。“`

6.7 朵举类型

   PostgtesSQL中的枚举类型类似于C语言中之enum类型。

6.7.1 创建枚举类型

   可以就此命令CREATE TYPE 来创造枚举类型,例如:

CREATE TYPE mood AS ENUM (‘sad’, ‘ok’, ‘happy’);

 

   枚举类型被创造以后,可以以建表的时候使用它,例如:

 

CREATE TYPE mood AS ENUM (‘sad’, ‘ok’, ‘happy’);

CREATE TABLE person (

    name text,

    current_mood mood

);

INSERT INTO person VALUES (‘Moe’, ‘happy’);

SELECT * FROM person WHERE current_mood = ‘happy’;

 name | current_mood

——+————–

 Moe  | happy

(1 row)

6.7.2 朵举类型的排序

  
   枚举类型的价的依次就是于创立项目时指定的值列表中每个值出现的相继。可以本着枚举类型进行较操作,也得使集函数,例如:

 

INSERT INTO person VALUES (‘Larry’, ‘sad’);

INSERT INTO person VALUES (‘Curly’, ‘ok’);

SELECT * FROM person WHERE current_mood > ‘sad’;

 name  | current_mood

——-+————–

 Moe   | happy

 Curly | ok

(2 rows)

 

SELECT * FROM person WHERE current_mood > ‘sad’ ORDER BY
current_mood;

 name  | current_mood

——-+————–

 Curly | ok

 Moe   | happy

(2 rows)

 

SELECT name FROM person

  WHERE current_mood = (SELECT MIN(current_mood) FROM person);

 name 


 Larry

(1 row)

 

6.7.3 类型安全

      不能够针对个别只不等之枚举类型的值进行较操作,否则系统会报错,例如:

 

CREATE TYPE happiness AS ENUM (‘happy’, ‘very happy’, ‘ecstatic’);

CREATE TABLE holidays (                                          

    num_weeks int,

    happiness happiness

);

INSERT INTO holidays(num_weeks,happiness) VALUES (4, ‘happy’);

INSERT INTO holidays(num_weeks,happiness) VALUES (6, ‘very happy’);

INSERT INTO holidays(num_weeks,happiness) VALUES (8, ‘ecstatic’);

INSERT INTO holidays(num_weeks,happiness) VALUES (2, ‘sad’);

荒唐:  enum类型happiness常量语法错误: “sad”。

SELECT person.name, holidays.num_weeks FROM person, holidays

  WHERE person.current_mood = holidays.happiness;

荒谬:  运算符不设有: mood = happiness。  —
mood和happiness是不同的枚举类型

 

  
可以拿简单个例外之枚举类型的价值转换成为其他种类的多少,然后还展开较,例如:

SELECT person.name, holidays.num_weeks FROM person, holidays

  WHERE person.current_mood::text = holidays.happiness::text;

 name | num_weeks

——+———–

 Moe  |         4

(1 row)

 

6.7.4 实现细节

    枚举类型的价的文书标签是分别轻重缓急写的,例如,'happy'
和`’HAPPY’是不同的。另外,值的文本标签的长度不能超过63“个字符。“`

 

6.8 几哪类型

    几哪数据类型表示二维空间的靶子。表6-18 显示了PostgreSQL
里面有的几哪类型。最基本的类是“点”,它是另外数据类型的基本功。

表 6-18. 几何类型

名字

囤空间

描述

表现形式

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>(圆心与半径)

   
对于这些几乎哪里类型,PostgreSQL提供了广大运算符和函数。它们在第7.11节里来说明。

6.8.1点(point)

      点是极致基本的几哪里类型。下面语法定义point``类型的值:

( x , y )

  x , y

x和y都是浮点数,表示横坐标和纵坐标。

6.8.2 线段(lseg)

    线段 (lseg)用单薄单点来表示。 lseg 的值用下面语法定义:

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

  ( x1 , y1 ) , ( x2 , y2

    x1 , y1   ,   x2 , y2

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

6.8.3 长方形(box)

    长方形是故有限独针对比赛个点来代表的。 它的值用下面的语法定义:

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

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

(3) x1 , y1   ,   x2 , y2

(x1,y1)(x2,y2) 是长方形的一样对针对性角点。

 

长方形的数量在输出使第一栽语法。

6.8.4路径(path)

   
路径由同样密密麻麻连续的点构成。路径可能是发掘的,列表中率先个点和末段一个接触并未连接,也或是闭路的,第一只和尾声一个沾连接起来。

path 的值用下面语法定义:

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

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

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

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

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

    这里的接触是结合路径的线的端点。
方括弧[]标明路径是挖的,圆括弧()标志路径是闭路的。

    路径的数码以输出时以第一种植语法。

6.8.5多边形(polygon)

  
  多边形由同密密麻麻接触代表(多边形之顶峰)。多边形于概念上和闭路路径一样,但是它们和闭路路径的囤积方不同等同时产生温馨的同等学支持函数。

polygon 的值用下列语法定义:

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

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

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

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

这边的接触是组成多边形边界的线条的端点。

    多边形数据以出口使第一种植语法。

6.8.6圆(circle)

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

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

(2)( ( x , y ) , r )

(3) ( x , y ) , r 

(4) x , y   , r 

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

    圆的数码以输出时利用第一种植格式。

 

6.9 网络地址类型

    PostgreSQL 提供了用来存储
IPv4、IPv6和MAC地址的数据类型,如表6-19所出示。
使用这些数据类型存储网络地址比用纯文本类型要好,因为这些类别提供输入错误检查及组成部分准的运算符和函数来拍卖网络地址(参考第7.12节)。

表 6-19. 网络地址类型

名字

存储空间

描述

cidr

7 或 19 字节

IPv4 和 IPv6 网络

inet

7 或 19 字节

IPv4 或 IPv6 网以及主机

macaddr

6 字节

MAC 地址

    

在对inet 或者cidr 类型的多寡开展排序的时段,IPv4 地方总是排在 IPv6
地点前面,包括那些封装在IPv6地址间要映射成 IPv6 地方之 IPv4
地点,例如::10.2.3.4 要::ffff::10.4.3.2。

6.9.1 `inet“`

``inet品种保存一个主机 IPv4 要IPv6
地点,也可以又保留该主机所当的子网信息。子网是经掩码来代表的。如果网络掩码是32以地址是IPv4,那么它们不意味着其余子网,只是表示同样尊主机。在
IPv6 里,地址长度是 128 位,因此 128
位掩码表示一个唯一的主机地址。注意要仅想保留网络地址,应该使 cidr
而不是 inet

   

欠类型的输入格式是“地址``/y ”,这里的地址是 IPv4 或者 IPv6 ,y
是子网掩码的位数的个数 如果简单“/y”, 则子网掩码对 Ipv4 是 32,对 IPv6
凡是
128,所以该值表示只是发同台主机。数据在亮时,如果y等于32,”/32”不见面被显示出。

6.9.2 `cidr“`

`` `` cidr保存一个IPv4 或者 IPv6
类型的网络地址。其输入和输出格式遵守无类域间路由(Classless Internet
Domain Routing)标准。它的格式是“地址``/y``”,这里 的地址 是 IPv4 或者
IPv6 网络地址,y是 网络掩码的二进制位数。如果简单/y
那么掩码的大小用传统的IP地址分类体系计算出来(IP地址分为四类,分别是A、B、C和D)。如果网络地址中针对应非网络掩码的二进制位中出现了“1”,则不是一个官方的cidr类型的数码,例如,192.168.1.0/16即使不是一个法定的cidr地址。

    

表6-20是CIDR实例:

表6-20. 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

6.9.3 inetcidr 对比

``   inetcidr 类型之间的本质区别在于inet
地址中针对应非网络掩码的二进制位中得起“1”,cidr
则免收受这种形式之地址。

    提示: 不喜欢inetcidr值的出口格式,可以使函数hosttextabbrev

6.9.4 `macaddr“`

`` macaddr 类型存储 MAC 地址,例如,以太网网卡的硬件地址(MAC
地址还用于其他用途)。MAC地址发生下几乎种植格式,它们都指定的凡跟一个地方:

‘08002b:010203’

‘08002b-010203’

‘0800.2b01.0203’

’08-00-2b-01-02-03′

’08:00:2b:01:02:03′

 

     对于a 到 f,大小写都得以。输出总是利用地方列有底末梢一种植形式。

 

6.10号串类型

        各项串是由于 1和0的组合的错。有三三两两栽位串类型
bit(n)bit varying(n)n举凡一个刚整数。

bit(n)品种的数量必须精确地兼容长度n,否则系统会报错。bit varying``类型可以接受变长位串,但各类串的长短为不可知超过n。bit
等同于 bit(1),没有点名长度的bit varying 类型的尺寸将从未界定。

 

       注意: 如果明确地把一个号串值转换成
bit(n)``类型,那么其的右将被截断或者在右侧补一起零,直到刚好构成n
位位串,系统不见面报错。类似地,如果明确地把一个各类串数值转换成
bit varying(n),如果她的尺寸超过了n
位,那么它们的超出n的一对以为电动截掉。

    

位串常量的语法的信息详见第1.4.3节。用于各类串的运算符和函数,在第7.6节里产生详细描述。

        下面是行使位串的实例:

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′);

荒唐: 位串长2跟类型bit(3)不匹配。

INSERT INTO test VALUES (B’10’::bit(3), B’101′);

SELECT * FROM test;

  a  |  b

—–+—–

 101 | 00

 100 | 101

 

6.11 UUID 类型

       uuid类型用于存储RFC 4122跟ISO/IEC
9834-8:2005备受定义的通用唯一标识符(stores Universally Unique
Identifiers,UUID),有部分数据库被把这个路称为GUID(globally unique
identifier)。UUID由一个128个的数字组成,它的科班的输入格式由32独稍写的十六迈入制位组成,这32单十六前行制位被分成4单组,第一只组有含8独十六前进制位,接下的老三个组个包含4单十六上制位,最后一个组包含12只十六向前制位。不同的组横杠分开(-),例如:a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11。

 

        PostgreSQL还接受下面几乎种格式,其中的十六上制位可以是大写的:

(1)A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11

(2){a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}

(3)a0eebc999c0b4ef8bb6d6bb9bd380a11

 

       
uuid类型的数码以出口时老是利用方面的率先种格式。PostgreSQL只是负存储uuid类型的多寡,同时提供了UUID类型的于函数,但未提供其他产生UUID的函数(因为没另外一个算法是相符有的行使项目的),应用程序必须自己开生成uuid的函数。

 

6.12 XML 类型

数据库可为此xml类型来存储xml数据,也得用text类型来存储xml文档,但是text类型没有供函数来处理xml数据,使用起来没xml类型方便。第7.13节列出了处理XML数据的函数。

xml类型可以存储xml文档(document),也堪储存由XMLDecl?
content定义之xml内容有(content)。xml内容有可以生出差不多只顶级元素。可以为此表达式xmlvalue
IS DOCUMENT来判定一个xml值是xml文档还是xml内容有。

6.12.1 创建xml值

     使用函数XMLPARSE从字符串中生成xml数据,它的语法如下:

XMLPARSE ( { DOCUMENT | CONTENT } value)

 

     下面是部分实例:

(1)XMLPARSE (DOCUMENT ‘<?xml
version=”1.0″?><book><title>Manual</title><chapter>…</chapter></book>’)

(2)XMLPARSE (CONTENT
‘abc<foo>bar</foo><bar>foo</bar>’)

 

     
XMLPARSE是SQL标准被定义的函数,PostgreSQL同时提供了下的艺术来创造xml值:

xml ‘<foo>bar</foo>’

‘<foo>bar</foo>’::xml

 

xml类型不会见证明其的值中是否含有文档类型声明(Document Type
Declaration,DTD)。

 

      函数xmlserialize可以以xml值转还变成字符串,它的语法如下:

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

中的路(type)可以是character、 character
varying或text(或者这些类别的别名)。

 

       使用函数XMLPARSE 和XMLSERIALIZE的时光,xml值的类型由参数“xml
option”来控制,可以利用SET命令来改变是参数的价,它的语法如下(使用任何一个且可以):

(1)SET XML OPTION { DOCUMENT | CONTENT };

(2)SET xmloption TO { DOCUMENT | CONTENT };

xml
option的默认值是CONTENT,任何形式的XML数据对于CONTENT来说都是法定的。

 

 

6.12.2 编码处理

   
客户端编码、服务器编码和xml数据的编码类型或者无相同。如果利用文本模式于客户端和服务器之间传送数据,数据库会自动对字符数据开展编码类型转换(详细信息请参见《数据库管理员手册》第5章节)。服务器在处理客户端发送过来的之xml数据常常,数据中编码类型的声明(encoding
declaration)将给忽视。服务器在用xml数据发给客户端时,其中为无见面包含编码类型声明。

   
如果下二进制模式于客户端和服务器之间传送数据,数据库不见面指向传送的数开展编码类型转换。服务器在拍卖客户端发送过来的底xml数据经常,会保留xml数据被的编码类型声明,如果xml数据没有定义编码类型,则觉得其的编码类型是UTF-8。服务器在以xml数据发给客户端时,其中会蕴藏xml数据的编码类型声明。

   
如果xml数据的编码类型、客户端的编码类型以及服务器的编码类型相同,则在拍卖xml数据常常无轻并发谬误,推荐下项目UTF-8作为xml数据的编码类型、客户端的编码类型及服务器的编码类型。

6.12.3 访问XML类型的数据

勿可知于xml类型的列上创建索引,如何当查询中走访xml类型的数码,在第7.13节里生详实讲述。

 

 

6.13 数组

    PostgreSQL
允许表的排列的数据类型是易长之多维数组。数组的类可以是其它基本型、枚举类型、复合类型或者用户从定义类型(不过未克是地区)。

6.13.1 数组类型的宣示

      下面通过一个实例来说明如何运用频繁组,首先建表:

CREATE TABLE sal_emp (

    name            text,

    pay_by_quarter  integer[],

    schedule        text[][]

);

 

      
从地方的例证可以看看,一个数组类型是由此当屡组元素类型名后面长方括号([])来定义的。上面的吩咐创建了一个叫
sal_emp
的申,列name的档次是text,列pay_by_quarter的类别是一个二维整型数组,列schedule的品类是一个text类型的二维数组。 

 

       也得以建表时肯定地指定数组的尺寸,例如:

CREATE TABLE tictactoe (

    squares   integer[3]

);

 

      
在时下之落实着,系统非见面检讨数组中元素的个数是否超过它的大小,所以integer[3]和integer[]凡是相等价格的。
另外,系统啊非会见检讨数组的元素是否复合数组的维数要求,所以于概念数组时,多维数组是没有意思之,它同样于概念一个一维数组,例如,intger[]、integer[][]和intger[4]还相互是齐价格的。对为一维数组,它的素得以是一个简单易行的常量,也可以是一个频繁组,例如,3和{1,2}
都是数组intger[]的官方元素。

 

6.13.2 数组值输入

   一个数组常量的常见格式如下:

‘{ val1 delim val2 delim … }’

这里的 delim 是欠项目的分隔符(在项目对应的pg_type
记录里指定)对于PostgreSQL 提供的正经数据类里,除了box
类型的相间符是分号(;)外,所有其他品类的相间符都是逗号(,)。每个 val
可以是一个数组元素类型的常量,也得以是一个子数组。例如

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

 

 

    如果反复组元素为空值,用null或NULL来表示。

 

    下面是另外一些实例:

 

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”}}’);

 

INSERT INTO sal_emp

    VALUES (‘Carol’,

    ‘{20000, 25000, 25000, 25000}’,

    ‘{{“breakfast”, “consulting”}, {“meeting”, null}}’);

 

INSERT INTO sal_emp

    VALUES (‘Carol’,

    ‘{20000, 25000, 25000, 25000}’,

    ‘{null, null}’);

 

    查询表sal_emp可以取下面的结果:

 

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}}

 Mike  | {20000,25000,25000,25000} |
{{breakfast,consulting},{meeting,NULL}}

 Alex  | {20000,25000,25000,25000} | {NULL,NULL}

(4 rows)

 

 

    也足以利用频繁盘造器的语法:

 

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’]]);

 

   
注意,如果反复组的元素呢是屡屡组,那么这数组的每个元素的维数和每个维的大小要同,否则系统会报错,例如:

(1)INSERT INTO sal_emp

    VALUES (‘Bill’,

    ‘{10000, 10000, 10000, 10000}’,

    ‘{{“meeting”, “lunch”}, {“meeting”}}’);

张冠李戴:  多维数组的每个元素的维数和每个维的尺寸要一律。

说明:{“meeting”, “lunch”}含有2个元素,{“meeting”}只有1个元素。

 

 

   
对于多维数组,要么它的有着因素都无是空值,要么它的兼具因素都是空值。如果有点元素呢空值,另外有要素不是空值,系统以会报错,例如:

(1)INSERT INTO sal_emp

    VALUES (‘Bill’,

    ‘{10000, 10000, 10000, 10000}’,

    ‘{{“meeting”, “lunch”}, null}’);

不当:  数组常量语法不正确: “{{“meeting”, “lunch”}, null}”。

征:{“meeting”,
“lunch”}不是空值,但另外一个元素是空值。如果拿{“meeting”,
“lunch”}也转成为null,则是一个法定的数组输入。

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语言编写的函数(无论是数据库内置的还是动态装载的)都得接受或返回任意的私自数据类型。函数的撰稿人应保证函数在使用地下数据类型的多寡常常得以健康地劳作。

 

故此经过语言编写的函数只能采用适用于该过程语言的非官方数据类型。当前,所有的进程语言都能应用应用
voidrecord
作为函数返回值的数据类型(如果函数用做触发器,它的返回值的类型必须是trigger)。PL/pgSQL
还支持anyelementanyarrayanynonarrayanyenum``作为函数的参数和返回值的数据类型

伪类型internal用来声明那些只能以数据库系统内被调用的函数,这些函数不能够当用户的SQL查询里让直调用。如果函数至少发生一个internal
类型的参数,SQL查询就无可知直接调用它。只有在函数除至少发生一个
internal``类型的参数的动静下,才能够拿函数的返回值的类型定义为internal类型,一定要是遵守就漫漫规则。

 

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

 

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