PostgreSQL數據類型

http://blog.csdn.net/neo_liu0000/article/details/6254086

?

?

第六章??數據類型

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標準中定義的：?bit，bit varying，boolean，char，character，character varying，varchar，date，?double precision，integer，interval，numeric，decimal，?real，smallint，time?（包括有時區和無時區的），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?整數類型

????類型?smallint、integer和?bigint?只能保存整數，也就是沒有小數部分的數字。如果試圖在一個整數類型中保存一個超過它能夠表示的值范圍的整數，數據庫將會報錯。

????常用的類型是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類型數據溢出。

細節:??precision為3, scale為3的數必須被四舍五入成小于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類型數據溢出。

細節:??precision為3, scale為3的數必須被四舍五入成小于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的數值。

?

????類型?decimal和numeric是等價的，兩種類型都是SQL標準定義的，SQL標準要求numeric的默認精度應該是0，PostgreSQL沒有執行這個規則，為了增強程序的移植性，最好同時指定numeric的精度和標度。

6.2.3?浮點類型

數據類型?real?和?double 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?標準中定義的類型float和float(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）

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

);

?

????上面的命令在表中創建了一個類型為無符號整數的列，該列與一個序列對象相關聯，這個序列對象的初始值是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?位

time、timestamp?和interval可以定義精度值p，這個精度值定義用來表示秒的小數位的個數，默認的情況下，沒有精度限制。對于timestamp和interval，p的取值范圍是0到6（實際的精度可能小于6）。對于time，p的取值范圍是0到10。

?

????類型time with time zone是SQL標準定義的，這個類型有些多余。在大多數情況下，date、time、timestamp without time zone?和?timestamp with time zone?的組合就能滿足任何應用需求。

????類型?abstime和reltime?是低分辨率時間類型，它們是數據庫內部使用的類型，在應用程序里面不應該使用這兩個類型。

6.5.1?日期/時間輸入

?????日期和時間可以用多種格式來表示，包括?ISO 8601、SQL標準中定義的格式等。對于一些格式，日期輸入里的月和天的表示可能會有歧義，如果參數DateStyle?被設置為?MDY，數據庫將按“月－日－年”的格式來解釋輸入的數據，DMY?表示“日－月－年”，而?YMD表示“年－月－日”。

?

????PostgreSQL在處理日期/時間輸入上比SQL標準要靈活得多。像一個文本字符串一樣，任何日期或時間的輸入都必須用單引號括起來。SQL標準定義的語法如下：

type?[ (p) ] 'value'

????對于time、timestamp和?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?或者?BC（AD/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 zone?和?timestamp with time zone?之間的進行轉換是通常假設?timestamp without time zone?數值的時區是參數timezone?指定的時區。可以用AT TIME ZONE?指定其它的時區。

6.5.1.4?時間間隔

????interval類型的數值可以用下面語法來定義：

[@]?quantity?unit?[quantity?unit...] [direction]

這里quantity?是一個數字（可能有符號），unit?是?microsecond、?millisecond、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?之間，默認是輸入的常量的精度。

?

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_DATE，CURRENT_TIME，CURRENT_TIMESTAMP，LOCALTIME，?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?的輸出格式和輸入格式類似，century?和?week?被轉換成年和日，而?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列出了所有可以識別的時區縮寫。

（3）POSXI風格的時區表示法，例如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?inet?與?cidr?對比

????inet?和cidr?類型之間的本質區別在于inet?地址中對應非網絡掩碼的二進制位中可以出現“1”，cidr?則不接受這種形式的地址。

????提示:?不喜歡inet或cidr值的輸出格式，可以使用函數host、text?和?abbrev。

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語言編寫的函數（無論是數據庫內置的還是動態裝載的）都可以接受或者返回任意的偽數據類型。函數的作者應該保證函數在使用偽數據類型的數據時可以正常地工作。

?

用過程語言編寫的函數只能使用適用于該過程語言的偽數據類型。當前，所有的過程語言都能使用使用?void和?record?作為函數返回值的數據類型（如果函數用做觸發器，它的返回值的類型必須是trigger）。PL/pgSQL?還支持anyelement、anyarray、anynonarray和anyenum作為函數的參數和返回值的數據類型。

偽類型internal用于聲明那些只能在數據庫系統內部被調用的函數，這些函數不能在用戶的SQL查詢里被直接調用。如果函數至少有一個internal?類型的參數，SQL查詢就不能直接調用它。只有在函數除至少有一個?internal類型的參數的情況下，才能將函數的返回值的類型定義為internal類型，一定要遵守這條規則。

?

anyelement、anyarray、anynonarray和anyenum又被叫做多態數據類型，使用這些數據類型的函數叫做多態函數。多態函數的參數的數據類型是不確定，實際的數據類型由傳遞給函數的參數的數據類型決定。anyelement表示任意數據類型，包括數組類型。anyarray表示任意數組類型。anyenum表示任意枚舉類型。anynonarray表示任意非數組類型。

?

如果一個函數的返回值的數據類型是多態類型，那么該函數至少有一個參數的類型是多態類型。