第三章. 數據類型
內容
數值類型
貨幣類型
字符類型
日期/時間類型
布爾類型
幾何類型
IP 版本 4 網絡和主機類型
描述 Postgres 內建的可用數據類型．
Postgres 有著豐富的數據類型可用．用戶可以使用 DEFINE TYPE 命令為 Postgres增加新的數據類型．
在數據類型這部分，隨后的各節將討論 SQL 標準的兼容性，移植問題和使用問題．一些 Postgres 類型直接與 SQL92-兼容類型相對應．其它一些 SQL92 語法定義的數據類型直接映射為Postgres 內部的數據類型．許多內建的數據類型有明確的外部格式．但是，有一些數據類型或者是Postgres特有的，如開放和閉合路徑，或者是有幾種可能格式的類型，如日期和時間類型．
表 3-1. Postgres數據類型
Postgres 類型 SQL92 或 SQL3 類型 描述
bool boolean 邏輯布爾量 （真/假）
box 二維平面中的方形盒
char(n) character(n) 定長字符串
cidr IP v4網絡或主機地址
circle 二維平面中的圓
date date 日歷日期（不帶時間）
decimal decimal(p,s) p <= 9，s = 0 的準確數字
float4 float(p), p < 7 精度為p的浮點數
float8 float(p), 7 <= p < 16 雙精度浮點數
inet IP v4網絡或主機地址
int2 smallint 兩字節長帶符號整數
int4 int, integer 四字節長帶符號整數
int8 八字節長帶符號整數
interval interval 通用的時間間隔
line 二維平面中的直線（無限長）
lseg 二維平面中的線段
money decimal(9,2) 美國風格的貨幣類型
numeric numeric(p,s) p == 9，s = 0的準確數字
path 二平面的開放的或封閉的幾何路徑
point 二維平面中的點
polygon 二維平面中的封閉幾何路徑
serial 用于索引和/或交叉索引的獨一無二的標識符
time time 一天里的時間
timetz time with time zone 一天里的時間，包括時區
timestamp timestamp with time zone 日期/時間
varchar(n) character varying(n) 變長的字符串
注意： cidr 和 inet 用于處理任何IP類型數據，但目前只能處理ipv4的數據．在以后的版本里我們將將所有對ipv4的支持增加到對ipv6的支持中．
表 3-2. Postgres 函數常量
Postgres 函數 SQL92 常量 描述
getpgusername() current_user 當前會話的用戶名
date('now') current_date 當前事務的日期
time('now') current_time 當前事務的時間
timestamp('now') current_timestamp 當前事務的日期和時間
Postgres 擁有開發 ORDBMS 應用的首要特性．除了符合SQL3規范外，我們還支持很大一部分 SQL92 規范．盡管我們盡可能地遵循 SQL92 規范，但該規范里有一些方面欠缺考慮，在后繼的規范中不可能繼續存在．Postgres不會為這些特性花費太多的時間；因為這些方面主要發生在很少使用或語意含混的場合，典型的用戶很少能碰到它們。
絕大多數與基本類型（如：整數和浮點數）對應的輸入輸出函數都會做錯誤檢查．出于改善執行性能的考慮，一些操作符和函數（如加法和乘法）并不做運行時的錯誤檢查．因而在一些系統上的對某些數據類型的數字操作會導致輕微的數值溢出或下溢。
要注意的是一些輸入輸出函數是不可逆的．也就是說，一個輸出函數的輸出結果與輸入的數據相比可能會丟失精度。
注意：浮點數可以保持該類型的最高固有精度（一般來說,雙精度是15位，4字節浮點數是6位）．其他依賴浮點的數據類型（如，幾何類型）也有相似精度．
數值類型
數值類型由2或4字節的整數以及4或8字節的浮點數和固定精度小數組成。
表 3-3. Postgres 數值類型
數值類型 存儲空間 描述 范圍
decimal 變長 用戶聲明精度 ~8000 位（數字）
float4 4 bytes 變精度 6 位數字
float8 8 bytes 變精度 15 位數字
int2 2 bytes 固定精度 -32768 到 +32767
int4 4 bytes 常用的固定精度數 -2147483648 到 +2147483647
int8 8 bytes 極大范圍的固定精度數 +/- > 18 位數字
numeric variable 用戶聲明精度 無限制
serial 4 bytes 標識或交叉索引 0 到 +2147483647
數值類型對應有一套完整的數學操作符和函數．相關信息請參考 數字操作符 和 數學函數。
int8 類型因為是要靠編譯器來支持的，因而可能無法在所有平臺上得到支持。
Serial（序列）類型
serial 類型是 Postgres 用其他現有的類型構造出來的一種特殊的類型．典型的應用是創建表的唯一標識，在當前的實現中，下面一句話：
CREATE TABLE tablename (colname SERIAL);
等價于下面幾句話:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename
(colname INT4 DEFAULT nextval('tablename_colname_seq');
CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
注意
創建為serial 類型的隱含序列號在刪除表時不會自動刪除
在刪除一個包含 serial 類型的表的時候，隱含的支持 serial 的序列號不會被自動刪除。因此下面順序執行的命令將是無效的：
CREATE TABLE tablename (colname SERIAL);
DROP TABLE tablename;
CREATE TABLE tablename (colname SERIAL);
除非顯式地使用 DROP SEQUENCE 命令，序列號不會被刪掉而一直在數據庫里面。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
貨幣類型
過時的類型：money （貨幣）現在已經過時，用numeric 或 decimal 取代它。
money 類型支持US-類的固定小數點位置的貨幣數字．如果編譯Postgres 時使用了 USE_LOCALE 編譯選項，貨幣類型將使用為locale(7)定義的貨幣習慣．
表 3-4. Postgres 貨幣類型
貨幣類型 存儲空間 描述 范圍
money 4 字節 固定精度 -21474836.48 到 +21474836.47
numeric 最終將取代貨幣類型。因而應該優先使用。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
字符類型
SQL92 定義了兩種基本的字符類型： char 和 varchar． Postgres 支持這些類型，并且還支持更通用的 text 類型，該類型不象varchar 一樣必須要定義一個數據域的上限．
表 3-5. Postgres 字符類型
字符類型 存儲空間 評價 描述
char 1 字節 SQL92-兼容 單字符
char(n) (4+n) 字節 SQL92-兼容 定長,不足補空白
text (4+x) 字節 最優 變長
varchar(n) (4+n) 字節 SQL92-兼容 變長,有局限
另外還有一種定長字符類型． name 類型，該類型只有一個用途，就是提供給Postgres 一個特別的類型用于內部名字．該類型通常不是給一般用戶使用的．該類型長度當前定為32字符長，但可以使用 NAMEDATALEN 重新定義．這個（變量）是在編譯的時候設置的，在以后的版本可能會改變．
表 3-6. Postgres 特殊字符類型
字符類型 存儲空間 描述
name 32 字節 32字節內部類型
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
日期/時間類型
PostgreSQL 支持 SQL 中所有的日期和時間類型。
表 3-7. Postgres 日期/時間類型
類型 描述 存儲 最早 最晚 精度
timestamp 用于包含日期和時間的數據 8 bytes 4713 BC AD 1465001 1 microsec / 14 digits
interval 用于時間間隔 12 bytes -178000000 years 178000000 years 1 mircosecond
date 用于只包含日期的數據 4 bytes 4713 BC 32767 AD 1 day
time 用于只包含時刻的數據 4 bytes 00:00:00.00 23:59:59.99 1 microsecond
注意：為了保證和早期 PostgreSQL 版本的兼容，我們還繼續提供 datetime（等效于timestamp）和timespan（等效于interval）。不過對這些類型的支持現在局限于進行一個隱含的轉換，轉換成timestamp 和 interval。類型abstime和 reltime 是低分辨率類型，它們被用于系統內部。我們不鼓勵你在新的應用里面使用這些類型，同時我們支持合適的時候把舊應用中對應的類型轉換成目前上面指明的。因為這些舊類型的部分或全部在未來的版本里可能消失。
日期/時間輸入
日期和時間的輸入幾乎可以是任何合理的格式，包括ISO-8601，SQL-兼容的，傳統 Postgres 的和其他形式的。日期輸入里的月份和日子輸入可以是模糊的，因此存在一個設置用來表明具體應該如何解釋。命令 SET DateStyle TO 'US' 或 SET DateStyle TO 'NonEuropean' 表示設置為“月份在日子前面”，而命令 SET DateStyle TO 'European' 表示設置為“日子在月份前面”。缺省是 ISO 風格，但是缺省值可以在編譯或者運行時改變。
參閱 日期/時間支持 獲取關于日期/時間輸入的準確的分析規則和可識別的時區的信息。
記住任何日期或者時間輸入需要被單引號包圍，就象一個文本字符串一樣。
date（日期）
下表是 date 類型可能的輸入方式。
表 3-8. PostgreSQL 日期輸入
例子 描述
January 8, 1999 無模糊
1999-01-08 ISO-8601 格式，建議方式
1/8/1999 US；在 European 模式下讀做八月一日
8/1/1999 European；在 US 模式下讀做八月一日
1/18/1999 US；在任何模式下都讀做一月十八日
1999.008 年和年里的第幾天
19990108 ISO-8601 年，月，日
990108 ISO-8601 年，月，日
1999.008 年和年里的第幾天
99008 年和年里的第幾天
January 8, 99 BC 公元前99年
表 3-9. PostgreSQL 月份縮寫
月份 縮寫
April（四月） Apr
August（八月） Aug
December（十二月） Dec
February（二月） Feb
January（一月） Jan
July（七月） Jul
June（六月） Jun
March（三月） Mar
November（十一月） Nov
October（十月） Oct
September（九月） Sep, Sept
注意： 五月（May）沒有縮寫，原因是顯而易見的。
表 3-10. PostgreSQL 星期縮寫
星期 縮寫
Sunday（星期天） Sun
Monday（星期一） Mon
Tuesday（星期二） Tue, Tues
Wednesday（星期三） Wed, Weds
Thursday（星期四） Thu, Thur, Thurs
Friday（星期五） Fri
Saturday（星期六） Sat
time（時間）
下面是有效的 time （時間）輸入
表 3-11. PostgreSQL 時間輸入
例子 描述
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
z 與 00:00:00 一樣
zulu 與 00:00:00 一樣
allballs 與 00:00:00 一樣
帶時區時間
這個類型是 SQL92 定義的，但是該定義顯示出非常基本的不足，使得這個類型幾乎無用。在多數情況下，date，time 和 timestamp 的組合應該能提供任何應用所需要的日期/時間功能的全部范圍。
帶時區時間 接受所有對 time 類型也合法的輸入，附加一個合法的時區，如下：
表 3-12. Postgres 帶時區時間輸入
例子 描述
04:05:06.789-8 ISO-8601
04:05:06-08:00 ISO-8601
04:05-08:00 ISO-8601
040506-08 ISO-8601
參考 Postgres 時區輸入 獲取時區的更多例子。
timestamp（時標）
有效的 timestamp 類型的輸入包含一個日期和一個時間的連接，后面跟著可選的 AD 或 BC，再后面跟著可選的時區。（參閱下面。）因此
1999-01-08 04:05:06 -8:00
是一個有效的 timestamp 值，它是 ISO-兼容的。另外，已經廣泛使用的格式
January 8 04:05:06 1999 PST
也是支持的。
表 3-13. PostgreSQL 時區輸入
時區 描述
PST 太平洋標準時間（Pacific Standard Time）
-8:00 ISO-8601 與 PST 的偏移
-800 ISO-8601 與 PST 的偏移
-8 ISO-8601 與 PST 的偏移
interval（時間間隔）
interval 可以用下面語法聲明：
Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
這里：Quantity 是 ...，-1，0，1，2，...；Unit 是 second，minute，hour，day，week，month，year，decade，century，millenium，或者這些單位的縮寫或復數；Direction 可以是 ago 或者為空。
特殊值
下面的SQL-兼容的函數可以用于對應的數據類型的日期或時間輸入：CURRENT_DATE，CURRENT_TIME，CURRENT_TIMESTAMP。
PostgreSQL 為方便期間同樣還支持幾個特殊常量。
表 3-14. PostgresSQL 日期/時間特殊常量
常量 描述
current 當前事務時間, (使用時)deferred
epoch 1970-01-01 00:00:00+00 (Unix 系統零時)
infinity 比任何有效時間晚
-infinity 比任何有效時間早
invalid 非法輸入
now 當前事務時間
today 今日午夜
tomorrow 明日午夜
yesterday 昨日午夜
'now' 在該值被插入時解釋，'current' 在每次檢索該值時被解釋。所以你可能會在大多數應用里使用 'now'。（當然你真的想用 CURRENT_TIMESTAMP 的話也行，它等效于 'now'。）
日期/時間輸出
使用 SET DateStyle，輸出格式可以設成四種風格之一： ISO-8601， SQL （Ingres），傳統的 Postgres，和 German 風格。缺省是 ISO 格式。
表 3-15. PostgreSQL 日期/時間輸出風格
風格描述 描述 例子
'ISO' ISO-8601 標準 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
日期和時間風格的輸出當然只是對應上面例子的日期或者時間部分。
SQL 風格有歐洲和非歐洲(US)變種， 決定月份后面是日期還是正相反．（參閱上面日期/時間輸入部分，看看這個設置是如何影響對輸入值的解釋。）
表 3-16. PostgreSQL 日期順序傳統
風格 描述 例子
European 日/月/年 17/12/1997 15:37:16.00 MET
US 月/日/年 12/17/1997 07:37:16.00 PST
interval 的輸出看起來象輸入格式，只是象 week 或 century 這樣的單位被轉換成年和日。在 ISO 模式下輸出看起來象
[ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
有幾種方法可以影響日期/時間類型的輸出：
直接在postmaster啟動時用于后端的環境變量 PGDATESTYLE。
會話開始時用于 libpg 的環境變量 PGDATESTYLE。
SQL 命令 SET DATESTYLE。
時區
Postgres 在典型應用中盡可能與SQL92 的定義相兼容．但 SQL92 標準在日期和時間類型和功能上有一些奇怪的混淆．兩個顯而易見的問題是：
date（日期）類型與時區沒有聯系，而 time（時間）類型卻有或可以有．
缺省的時區用一個整數常量表示與GMT/UTC的偏移（時差）．
然而，現實世界的時區只有在與時間和日期都關聯時才有意義，因為時間偏移量（時差）可能因為實行類似夏時制這樣的制度而在一年里有所變化．
為了克服這些困難， Postgres 只將日期和時間類型（同時包含日期和時間）與時區相關聯，并且假設任何類型的當地時間只包含日期或時間．另外，時區的支持從操作系統的時區功能中引入，這樣就可以處理夏時制或其他可知的因素．
Postgres 從操作系統獲得介于1902年和2038年的日期的時區支持（近乎是典型的Unix類系統的日期限制）．在這個范圍之外的日期都假設為用全球統一時間（Universal Coordinated Time，UTC）聲明和使用。
在系統內部,所有日期和時間都是用全球統一時間(UTC)格式存儲，也就是通常所說的格林威治時間（GMT）．時間在發給客戶前端前由數據庫服務器轉換成本地時間，因而缺省的時區是服務器的時區．
有幾種影響時區特性的方法：
直接在postmaster啟動時后端使用TZ環境變量作為缺省時區．
客戶端使用libpq時用 PGTZ 環境變量將時區信息傳遞給后端．
SQL 命令 SET TIME ZONE 可以給會話設置時區．
如果使用了非法的時區，時區變為GMT（在大多數系統上）。
注意：如果設置了編譯選項 USE_AUSTRALIAN_RULES，那么 EST 代表澳大利亞東部標準時間，（ Australia Eastern Std Time）與UTC有 +10:00 小時的偏移量。
內部
Postgres 用 Julian 記日法用于所有日期/時間計算。如果假設一年的長度是365.2425天時，這個方法可以很精確地預計/計算從4713BC（公元前4713年）到很久的未來的任意一天的日期。
19世紀以前的日期傳統（歷法）對一些趣味讀物有意義，但是在我們這里好象沒有充分的理由把它們編碼入日期/時間控制器里面去。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
布爾類型
Postgres 支持用 bool 作為 SQL3 布爾數據類型． bool只能有兩個狀態之一： '真'('True') 或 '假'('False')．第三種狀態，'未知'('Unknow')，在 SQL3中沒有建議，因而也沒有實現；NULL是一個有效的替代品．bool可用于任何布爾表達式，并且布爾表達式的結果也是布爾類型兼容的．
bool 使用1字節存儲空間．
表 3-17. Postgres 布爾類型
狀態 輸出 輸入
True 't' TRUE, 't', 'true', 'y', 'yes', '1'
False 'f' FALSE, 'f', 'false', 'n', 'no', '0'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
幾何類型
幾何類型表示二維的平面物體．最基本的類型：點，是其他類型的基礎．
表 3-18. Postgres 幾何類型
幾何類型 存儲空間 表達 描述
point 16 bytes (x,y) 空間中一點
line 32 bytes ((x1,y1),(x2,y2)) (無窮)直線
lseg 32 bytes ((x1,y1),(x2,y2)) 線段
box 32 bytes ((x1,y1),(x2,y2)) 長方形
path 4+32n bytes ((x1,y1),...) 閉合路徑(與多邊形類似)
path 4+32n bytes [(x1,y1),...] 開放路徑
polygon 4+32n bytes ((x1,y1),...) 多邊形(與閉合路徑相似)
circle 24 bytes <(x,y),r> 圓(圓心和半徑)
一系列豐富的函數和操作符可用來進行各種幾何計算，如度量，轉換，旋轉和計算相交等。
Point（點）
點是集合類型的基本二維構造單位。
用下面語法描述 point：
( x , y )
x , y
這里的參數是
x
是用一個浮點數表示的點的 x 坐標。
y
y 是用一個浮點數表示的點的 y 坐標。
線段
線段(lseg)是用一對點來代表的．
lseg 用下面語法聲明：
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
這里的參數是
(x1,y1), (x2,y2)
是線段的端點。
Box（方）
方是用一對對角點來表示的。
box 用下面語法聲明：
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
這里的參數是
(x1,y1), (x2,y2)
是一對對角點。
方的輸出使用第一種語法．在輸入時將按先左下角后右上角的順序重新排列．你也可以輸入其他的一對對角點．但輸入時將按照先左下角后右上角的順序重排并存儲．
Path（路徑）
路徑由一系列連接的點組成．路徑可能是"開放"的，也就是第一個點和最后一個點沒有連接，也可能是"閉合"的，這時第一個和最后一個點連接起來．我們提供了函數 popen(p) 和 pclose(p)來強制路徑是開放的還是閉合的，可以用函數 isopen(p) 和 isclosed(p) 來在查詢中選擇是那種．
path 用下面語法聲明：
( ( x1 , y1 ) , ... , ( xn , yn ) )
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
這里的參數是
(x,y)
組成路徑的線段的端點。前面的（"["）表明一個開放的路徑，而前面的（"("）表明一個閉合的路徑。
路徑的輸出使用第一種語法輸出．注意 Postgres v6.1以前的版本路徑的格式是由一個圓括號開始――一個"關閉標志"，一個點的計數然后后面是點的列表，最后是一個圓括號結束．有一個內建的函數upgradepath 可用于從6.1前版本輸出和重載路徑，將其轉換成新的。
Polygon（多邊形）
多邊形由一系列點代表．多邊形可以認為與閉合路徑一樣，但是存儲方式不一樣而且有自己的一套支持過程/函數．
polygon 用下列語法聲明：
( ( x1 , y1 ) , ... , ( xn , yn ) )
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
這里的參數是
(x,y)
組成多邊形邊界的線段的端點。
多邊形輸出使用第一種語法．要注意在 Postgres 版本v6.1前，多邊形的格式是一個圓括號開頭，后面是點的x坐標列表，然后是點的y坐標列表，最后是一個圓括號結束．用內建的函數upgradepoly 可用于從6.1前版本輸出和重載多邊形，將其轉換成新的。
Circle（圓）
圓由一個圓心和一個半徑代表．
circle 用下面語法表示：
< ( x , y ) , r >
( ( x , y ) , r )
( x , y ) , r
x , y , r
這里的參數是
(x,y)
圓心
r
圓的半徑
圓的輸出用第一個格式．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
IP 版本 4 網絡和主機地址
cidr 類型用于存儲用 CIDR（Classless Inter-Domain Routing）符號聲明的網絡． inet 類型用一種形式簡單的變種來表示簡單的主機TCP/IP地址，并以此來存儲以 CIDR 符號表示的主機和網絡。
表 3-19. PostgresIP V4 類型
IPV4 類型 存儲空間 描述 范圍
cidr 變長 CIDR 網絡 有效的 IPV4 CIDR 字塊
inet 變長 網絡和主機 有效的 IPV4 CIDR 字塊
CIDR
cidr 類型聲明一個 CIDR 網絡． 說明一個無類（classless）的網絡的格式是 "x.x.x.x/y" 這里 "x.x.x.x" 網絡地址 而 "/y" 網絡掩碼中1的個數．如果 "/y" 部分沒有指明，那么掩碼部分用舊的有類的網絡分類假設進行計算，但要求輸入的數據已經包括了確定掩碼的所需的所有8進制位．
下面是些例子：
表 3-20. PostgresIP 類型舉例
CIDR 輸入 CIDR 顯示
192.168.1 192.168.1/24
192.168 192.168.0/24
128.1 128.1/16
128 128.0/16
128.1.2 128.1.2/24
10.1.2 10.1.2/24
10.1 10.1/16
10 10/8
inet
inet 類型設計用來在一個數據域里存放所有主機信息，包括其所在的CIDR風格的子網．要注意如果你想正確存放CIDR網絡，你應該使用cidr 類型．inet 類型類似cidr 類型，只是它的主機部分可以是非零．可以用函數從數據域中將各種元素提取出來．
該函數的輸入格式是 x.x.x.x/y 這里 x.x.x.x 是互聯網主機， y 是網絡掩碼的位數．如果 /y 部分未填，當作/32．輸出時，如果 /y 部分為 /32，將不會打印出來．只要不填"/y"部分，該類型可以直接作為主機類型使用．（？？This allows the type to be used as a straight host type by just leaving of the bits part. ）
--------------------------------------------------------------------------------
