Chapter 5. 資料類型

Table of Contents
5.1. 數值類型
5.1.1. 整數類型
5.1.2. 任意精度資料
5.1.3. 浮點數類型
5.1.4. Serial(序列)類型
5.2. 貨幣類型
5.3. 字元類型
5.4. 二進位字串
5.5. 日期/時間類型
5.5.1. 日期/時間輸入
5.5.2. 日期/時間輸出
5.5.3. 時區
5.5.4. 內部
5.6. 布林類型
5.7. 幾何類型
5.7.1. Point(點)
5.7.2. 線段
5.7.3. Box(方)
5.7.4. Path(路徑)
5.7.5. Polygon(多邊形)
5.7.6. Circle(圓)
5.8. 網路位址資料類型
5.8.1. inet
5.8.2. cidr
5.8.3. inetcidr 對比
5.8.4. macaddr
5.9. 位串類型
5.10. 物件標識符類型
5.11. 偽類型
5.12. 陣列

PostgreSQL 有著丰富的資料類型可用. 使用者可以使用 CREATE TYPE命令為 PostgreSQL 增加新的資料類型.

Table 5-1 顯示了所有包含在標準版本裡的 普通資料類型.在"別名"列裡列出的大多數可選名字 都是因歷史原因 PostgreSQL 在內部使用的 名字.另外,還有一些內部使用的或者廢棄的類型也可以用, 但沒有在這裡列出.

Table 5-1. 資料類型

類型名字別名描述
bigintint8有符號 8 字節整數
bigserialserial8自增八字節整數
bit 定長位串
bit varying(n)varbit(n)變長位串
booleanbool邏輯布林量 (真/假)
box 二維平面中的長方形
bytea 二進位資料
character(n)char(n)定長字串
character varying(n)varchar(n)變長字串
cidr IP 網路位址
circle 二維平面中的圓
date 日歷日期(年,月,日)
double precisionfloat8雙精度浮點數位
inet IP 網路或主機位址
integerintint4四字節長有符號整數
interval(p) 通用的時間間隔
line 二維平面中的無限長直線(沒實現)
lseg 二維平面中的線段
macaddr MAC 位址
money 貨幣金額
numeric [ (p, s) ]decimal [ (p, s) ]可選精度的準確數位
path 二維平面的開放的或封閉的幾何路徑
point 二維平面中的點
polygon 二維平面中的封閉幾何路徑
realfloat4單精度浮點數
smallintint2有符號兩字節整數
serialserial4自增四字節整數
text 變長字串
time [ (p) ] [ without time zone ] 一天裡的時間
time [ (p) ] with time zonetimetz一天裡的時間,包括時區
timestamp [ (p) ] without time zonetimestamp日期和時間
timestamp [ (p) ] [ with time zone ]timestamptz日期和時間

兼容性: 下列類型(或者那樣拼字的)是SQL宣告的: bitbit varyingbooleancharcharactercharacter varyingvarchardatedouble precisionintegerintervalnumericdecimalrealsmallinttimetimestamp (包括有時區和無時區的).

每種資料類型都有一個由其輸入和輸出函數決定的外部表現形式. 許多內建的類型有明顯的格式.不過,許多類型要麼是 PostgreSQL 所特有的,比如開放和 閉合路徑,要麼是有幾種不同的格式可能,比如日期和時間類型. 絕大多數與基本類型(如:整數和浮點數)對應的輸入輸出函數都會做錯誤檢查. 出於改善執行性能的考慮, 有些輸入和輸出函數是不可逆的.也就是說,輸出函數的輸出結果和 原始的輸入比較的時候可能丟失精度.

一些運算子和函數(如加法和乘法)並不做運行時的錯誤檢查. 因而在一些系統上的對某些資料類型的數位操 作會導致輕微的數值溢出或下溢。

5.1. 數值類型

數值類型由2 ,4或8字節的整數以及4或8字節的浮點數和固定精度小陣列成。 Table 5-2 列出了所有可用類型。

Table 5-2. 數值類型

數值類型儲存空間描述範圍
smallint2 字節小範圍固定精度-32768 到 +32767
integer4 字節常用的固定精度數-2147483648 到 +2147483647
bigint8 字節大範圍的固定精度數位-9223372036854775808 到 9223372036854775807
decimal變長使用者宣告精度,精確無限制
numeric變長使用者宣告精度,精確無限制
real4 字節變精度,不精確6 位十進制數位精度
double precision8 字節變精度,不精度15 位十進制數位精度
serial4 字節自增整數1 到 +2147483647
bigserial8 字節大範圍的自增整數1 到 9223372036854775807

數值類型常數的語法在 Section 1.1.2 裡描述. 數值類型對應有一套完整的數學運算子和函數.相關資訊請參考 Chapter 6。 下面的幾節詳細描述這些類型.

5.1.1. 整數類型

類型 smallintintegerbigint 儲存各種範圍的全部是數位的數,也就是沒有小數部分的數位. 試圖儲存超出範圍以外的數值將導致一個錯誤.

常用的類型是 integer,因為它提供了在範圍,儲存空間, 和性能之間的最佳平衡.一般只有在硬碟空間緊張的時候才使用 smallint.而只有在 integer 的範圍不夠的時候 才使用 bigint,因為前者絕對快得多.

bigint 類型可能不是在所有平台上都運轉正確, 因為它依賴編譯器對八字節整數的支援.在那些沒有這樣支援的機器上, bigint 的作用和 integer 一樣(但是仍然占據 八字節儲存).不過,我們還不知道任何有這樣的情況的平台.

SQL只宣告了整數類型 integer(或int)和 smallint.類型 bigint,和類型名 int2int4,和 int8 都是擴展, 也在許多其它 RDBMS 產品中使用.

注意: 如果你有一個有索引的 smallint 或者 bigint 欄位,那麼你可能在讓系統使用這些索引的時候碰到 麻煩.比如,象下面這樣形式的幾句

... WHERE smallint_column = 42

將不會使用索引,因為系統給常數 42 賦予了 integer 類型, 而目前PostgreSQL在涉及兩種資料類型的時候不能使用索引. 一個繞開的方法是使用單引號包圍常數,因此︰

... WHERE smallint_column = '42'

這樣將導致系統推遲類型解析並將給常數賦予正確的類型.

5.1.2. 任意精度資料

類型 numeric 可以儲存最多1,000位精度的數位 並且準確地進行計算. 我們特別建議將它 用於貨幣金額和其它要求計算準確的數量.不過,numeric 類型和我們下一節描述的浮點數類型比較起來要慢很多.

在隨後的內容裡,我們使用了下述術語︰ 一個 numeric比例是 到小數點右邊為止小數部分的位數, numeric精度是整個數位裡 全部資料位的數目,也就是小數點兩邊的資料數目. 因此數位 23.5141 的精度為6而比例為4. 你可以認為整數的比例為零.

numeric 類型的精度和比例都是可以配置的.要宣告一個類型為 numeric 的欄位,你可以用下面的語法

NUMERIC(precision, scale)

精度必須為正數,比例可以為零或者正數. 另外,

NUMERIC(precision)

選擇了 0 為比例.不帶任何精度或者比例宣告

NUMERIC

則建立一個可以儲存一個直到實現精度上限的任意精度和比例的數值, 一個這樣類型的欄位將不會把輸入數值轉化成任何特定的比例, 而帶有比例宣告的 numeric 欄位將把輸入值轉化為該比例. (SQL標準要求預設的比例是 0.也就是轉化成整數精度. 我們覺得這樣做有點沒用. 如果你關心移植性,那你最好總是明確宣告精度和比例.)

如果一個數值的精度或者比例比欄位宣告的精度或者比例高, 那麼系統將嘗試圓整該數值.如果該數值無法做這樣的圓整以滿足 宣告的限制,那麼拋出一個錯誤.

類型 decimalnumeric 是等效的. 兩種類型都是SQL標準.

5.1.3. 浮點數類型

資料類型 realdouble precision 是不準確的,變精度的數位類型. 實際上,這些類型是 IEEE 標準 754 二進位浮點數算術 (分別對應單和雙精度)的一般實現,加上下層處理器,操作系統和 編譯器對它的支援.

不準確意味著一些數值不能準確地轉換成內部格式並且是以近似的形式 儲存的,因此儲存然後把資料再列印出來可能顯示一些缺失. 處理這些錯誤以及這些錯誤是如何在計算中傳播的屬於數學和計算機 科學的一個完整的分支,我們不會在這裡進一步討論它,這裡的討論僅限 於如下幾點︰

  • 如果你要求準確的計算(比如計算貨幣金額),應使用 numeric 類型.

  • 如果你想用這些類型類型做任何重要的復雜計算,尤其是那些你 對範圍情況(無窮,下溢)嚴重依賴的事情,那你應該仔細評詁你的實現.

  • 拿兩個浮點數值進行相等性比較可能象,也可能不象想像那樣運轉.

通常,real 類型的範圍是至少 -1E+37 到 +1E+37, 精度至少是 6 位小數.double precision 類型通常有 -1E+308 到 +1E+308 的範圍,精度是至少 15 位數位.太大或者太小的 數值都會導致錯誤.如果輸入資料太高,那麼可能發生圓整.太接近 零的數位,如果無法與零值的表現形式相區分就會產生下溢錯.

5.1.4. Serial(序列)類型

serial 類型不是真正的類型, 只是為在表中設定唯一標識做的概念上的便利. 在目前的實現中,下面一句話:

CREATE TABLE tablename (
	colname SERIAL
);

等價於宣告下面幾句話:

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

因此,我們就建立了一個整數位段並且把它的預設數值安排為從一個 序列發生器取值.應用了一個 NOT NULL 約束以確保空值不會被 明確地插入。在大多數情況下你可能還希望附加一個 UNIQUE 或者 PRIMARY KEY 約束避免意外地插入重復的數值,但這個不是自動發生的。

要使用 serial 欄位插入序列的下一個數值到表中, 主要是要注意 serial 應該賦予預設值。我們可以通過 在 INSERT 語句中把該欄位排除在欄位清單之外來實現, 也可以通過使用 DEFAULT 來實現。

類型名 serialserial4 是等效的︰ 兩個都建立 integer 欄位.類型名 bigserialserial8 也一樣,只不過它建立一個 bigint 欄位.如果你預計在表的生存期中使用的標識數目 超過 231 個,那麼你應該使用 bigserial

一個 serial 類型建立的序列在所屬的欄位被刪除的時候 自動刪除,否則是不會被刪除的。(這一點在 PostgreSQL 版本 7.3 之前可不是真的。請注意,這種自動刪除的關聯在通過重載 7.3 以前的 資料庫轉儲的時候可不會自動發生﹔那樣的轉儲文件不包含需要建立這種關聯 關系的資訊。) 另外,這樣的序列和欄位之間的依賴性只在 serial 欄位本身上有﹔ 如果任何其它欄位參照了序列(可能是手動調用 nextval() 函數), 那麼,如果這個序列被刪除了,它們就會被破壞。我們認為這樣使用 serial 欄位是一種不好的形式。

注意: PostgreSQL 7.3 之前,serial 隱含 UNIQUE。但現在不再是自動隱含的了。如果你希望 一個序列欄位是 UNIQUE 或者是 PRIMARY KEY, 那麼現在你必須宣告它,和其他資料類型一樣。