Chapter 8. 資料類型

Table of Contents
8.1. 數值類型
8.1.1. 整數類型
8.1.2. 任意精度數值
8.1.3. 浮點數類型
8.1.4. Serial(序號)類型
8.2. 貨幣類型
8.3. 字元類型
8.4. 二進制資料類型
8.5. 日期/時間類型
8.5.1. 日期/時間輸入
8.5.2. 日期/時間輸出
8.5.3. 時區
8.5.4. 內部
8.6. 布爾類型
8.7. 幾何類型
8.7.1. Point(點)
8.7.2. 線段
8.7.3. Box(方)
8.7.4. Path(路徑)
8.7.5. Polygon(多邊形)
8.7.6. Circle(圓)
8.8. 網絡地址資料類型
8.8.1. inet
8.8.2. cidr
8.8.3. inetcidr 對比
8.8.4. macaddr
8.9. 位串類型
8.10. 數組
8.10.1. 數組類型的聲明
8.10.2. 數組值輸入
8.10.3. 訪問數組
8.10.4. 修改數組
8.10.5. 在數組中檢索
8.10.6. 數組輸入和輸出語法
8.11. 復合類型
8.11.1. 聲明復合類型
8.11.2. 復合類型值輸入
8.11.3. 訪問復合類型
8.11.4. 修改復合類型
8.11.5. 復合類型輸入和輸出語法
8.12. 對像標識符類型
8.13. 偽類型

PostgreSQL 有著豐富的資料類型可用。 用戶可以使用 CREATE TYPE命令為 PostgreSQL 增加新的資料類型。

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

Table 8-1. 資料類型

名字別名描述
bigintint8有符號 8 字元整數
bigserialserial8自增八字元整數
bit [ (n) ] 定長位串
bit varying [ (n) ]varbit變長位串
booleanbool邏輯布爾量 (真/假)
box 平面中的長方形
bytea 二進制資料("字元數組"
character varying [ (n) ]varchar [ (n) ] 變長字元串
character [ (n) ] char [ (n) ] 定長字元串
cidr IPv4 或者 IPv6 網絡地址
circle 平面中的圓
date 日曆日期(年,月,日)
double precisionfloat8雙精度浮點數字
inet IPv4 或者 IPv6 網絡地址
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 zone ] 日期和時間
timestamp [ (p) ] with time zonetimestamptz日期和時間

相容性: 下列類型(或者那樣拼寫的)是SQL聲明的: bitbit varyingbooleancharcharactercharacter varyingvarchardatedouble precisionintegerintervalnumericdecimalrealsmallinttime (包括有時區和無時區的), timestamp (包括有時區和無時區的)。

每種資料類型都有一個由其輸入和輸出函數決定的外部資料表現形式。 許多內建的類型有明顯的格式。不過,許多類型要麼是 PostgreSQL 所特有的,比如幾何路徑,要麼可能是有幾種不同的格式,比如日期和時間類型。 有些輸入和輸出函數是不可逆的。也就是說,輸出函數的輸出結果和原始的輸入比較的時候可能丟失精度。

8.1. 數值類型

數值類型由2、4或8字元的整數以及4或8字元的浮點數和可選精度小數組成。 Table 8-2 列出了所有可用類型。

Table 8-2. 數值類型

名字儲存空間描述範圍
smallint2 字元小範圍整數-32768 到 +32767
integer4 字元常用的整數-2147483648 到 +2147483647
bigint8 字元大範圍的整數-9223372036854775808 到 9223372036854775807
decimal變長用戶聲明精度,精確無限制
numeric變長用戶聲明精度,精確無限制
real4 字元變精度,不精確6 位十進制數字精度
double precision8 字元變精度,不精確15 位十進制數字精度
serial4 字元自增整數1 到 +2147483647
bigserial8 字元大範圍的自增整數1 到 9223372036854775807

數值類型常量的語法在 Section 4.1.2 裡描述。 數值類型對應有一套完整的數學操作符和函數。相關訊息請參考 Chapter 9。 下面的幾節詳細描述這些類型。

8.1.1. 整數類型

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

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

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

SQL只聲明了整數類型 integer(或int)和 smallint。類型 bigint,和類型名 int2int4,和 int8 都是擴展, 也在許多其它 SQL 資料庫系統中使用。

8.1.2. 任意精度數值

類型 numeric 可以儲存最多1000位精度的數字並且準確地進行計算。 我們特別建議將它用於貨幣金額和其它要求計算準確的數量。不過,numeric 類型上的算術運算比整數類型或者我們下一節描述的浮點數類型要慢很多。

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

numeric 字串的最大精度和最大比例都是可以配置的。要聲明一個類型為 numeric 的字串,您可以用下面的語法

NUMERIC(precision, scale)

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

NUMERIC(precision)

選擇了 0 為比例。不帶任何精度或者比例聲明

NUMERIC

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

如果一個要儲存的數值的比例比字串聲明的比例高, 那麼系統將嘗試圓整該數值到指定的小數位。 然後,如果小數點左邊的資料位數超過了聲明的精度減去聲明的比例, 那麼拋出一個錯誤。

數值資料值實際上是不帶任何前導或者後綴零的形式儲存的。 因此,字串上聲明的精度和比例都是最大值,而不是固定分配的。 (在這個方面,numeric 類型更類似於 varchar(n), 而不像 char(n)。)

除了普通的數字值之外,numeric 類型允許特殊值 NaN, 資料表示"不是一個數字"。任何在 NaN 上面的操作都生成另外一個 NaN。 如果在 SQL 命令裡把這些值當作一個常量寫,您必須在其周圍放上單引號,比如 UPDATE table SET x = 'NaN'。在輸入時,字串 NaN 當作大小寫無關看待。

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

8.1.3. 浮點數類型

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

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

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

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

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

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

除了普通的數字值之外,浮點類型還有幾個特殊值:

Infinity
-Infinity
NaN

這些值分別資料表示 IEEE 754 特殊值"正無窮大""負無窮大", 以及"不是一個數字"。(在不遵循 IEEE 754 浮點算術的機器上,這些值的含義可能不是預期的。) 如果在 SQL 命令裡把這些數值當作常量寫,您必須在它們周圍放上單引號, 像這樣 UPDATE table SET x = 'Infinity'。 輸入時,這些值是以大小寫無關的方式識別的。

PostgreSQL 還支援 SQL 標準資料表示法 floatfloat(p) 用於聲明非精確的數值類型。 在這裡,p 聲明以二進制位資料表示的最低可接受精度。 在選取 real 類型的時候,PostgreSQL 接受 float(1)float(24),在選取 double precision 的時候,接受 float(25)float(53)。在允許範圍之外的 p 值將導致一個錯誤。 沒有聲明精度的 float 將被當作是 double precision

注意: PostgreSQL 7.4 以前,在 float(p) 裡面的精度會被當作是這麼多位數的十進制位。到 7.4 已經被修改成與 SQL 標準匹配,標準聲明這個精度是以二進制位度量的。假設 realdouble precision 分別有 24 和 53 個二進制位的位數對 IEEE 標準的浮點實現來說是正確的。 在非 IEEE 平台上,這個數值可能略有偏差,但是為了簡化,我們在所有平台上都用了同樣的 p 值範圍。

8.1.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
);

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

注意: PostgreSQL 7.3 以前,serial 隱含 UNIQUE。但現在不再如此。 如果您希望一個序列字串有一個唯一約束或者一個主鍵,那麼您現在必須聲明,就像其它資料類型一樣。

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

類型名 serialserial4 是等效的: 兩個都建立 integer 字串。類型名 bigserialserial8 也一樣,只不過它建立一個 bigint 字串。 如果您預計在資料表的生存期中使用的標識數目超過 231 個,那麼您應該使用 bigserial

一個 serial 類型建立的序列在所屬的字串被刪除的時候自動刪除,其它情況下是不會被刪除的。 (這一點在 PostgreSQL 版本 7.3 之前可不是真的。請注意,這種自動刪除的關聯在透過重載 7.3 以前的資料庫轉儲的時候可不會自動發生; 那樣的轉儲文件不包含需要建立這種關聯關係的訊息。) 另外,這樣的序列和字串之間的依賴性只在 serial 字串本身上有; 如果任何其它字串引用了序列(可能是手工調用 nextval 函數), 那麼,如果這個序列被刪除了,它們就會被破壞。我們認為這樣使用 serial 字串是一種不好的形式;如果您想用同一個序列發生器給幾個字串喂資料,那麼還是把序列發生器作為獨立對像建立。