PostgreSQL 數(shù)據(jù)類型

本章節(jié)，我們將討論 PostgreSQL 的數(shù)據(jù)類型，數(shù)據(jù)類型是我們?cè)賱?chuàng)建表的時(shí)候?yàn)槊總€(gè)字段設(shè)置的。

設(shè)置數(shù)據(jù)類型的好處：

PostgreSQL提 供了豐富的數(shù)據(jù)類型。用戶可以使用 CREATE TYPE 命令在數(shù)據(jù)庫(kù)中創(chuàng)建新的數(shù)據(jù)類型。PostgreSQL 的數(shù)據(jù)類型有很多種，下面我們具體來(lái)說(shuō)明。

數(shù)值類型

數(shù)值類型由 2 字節(jié)、4 字節(jié)或 8 字節(jié)的整數(shù)以及 4 字節(jié)或 8 字節(jié)的浮點(diǎn)數(shù)和可選精度的十進(jìn)制數(shù)組成。

下表列出了可用的數(shù)值類型。

貨幣類型

money 類型存儲(chǔ)帶有固定小數(shù)精度的貨幣金額。

numeric、int 和 bigint 類型的值可以轉(zhuǎn)換為 money，不建議使用浮點(diǎn)數(shù)來(lái)處理處理貨幣類型，因?yàn)榇嬖谏崛脲e(cuò)誤的可能性。

字符類型

下表列出了 PostgreSQL 所支持的字符類型：

日期/時(shí)間類型

下表列出了 PostgreSQL 支持的日期和時(shí)間類型。

布爾類型

PostgreSQL 支持標(biāo)準(zhǔn)的 boolean 數(shù)據(jù)類型。

boolean 有"true"(真)或"false"(假)兩個(gè)狀態(tài)， 第三種"unknown"(未知)狀態(tài)，用 NULL 表示。

枚舉類型

枚舉類型是一個(gè)包含靜態(tài)和值的有序集合的數(shù)據(jù)類型。

PostgtesSQL中的枚舉類型類似于 C 語(yǔ)言中的 enum 類型。

與其他類型不同的是枚舉類型需要使用 CREATE TYPE 命令創(chuàng)建。

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

創(chuàng)建一周中的幾天，如下所示:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

就像其他類型一樣，一旦創(chuàng)建，枚舉類型可以用于表和函數(shù)定義。

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)

幾何類型

幾何數(shù)據(jù)類型表示二維的平面物體。

下表列出了 PostgreSQL 支持的幾何類型。

最基本的類型：點(diǎn)。它是其它類型的基礎(chǔ)。

網(wǎng)絡(luò)地址類型

PostgreSQL 提供用于存儲(chǔ) IPv4 、IPv6 、MAC 地址的數(shù)據(jù)類型。

用這些數(shù)據(jù)類型存儲(chǔ)網(wǎng)絡(luò)地址比用純文本類型好， 因?yàn)檫@些類型提供輸入錯(cuò)誤檢查和特殊的操作和功能。

在對(duì) inet 或 cidr 數(shù)據(jù)類型進(jìn)行排序的時(shí)候， IPv4 地址總是排在 IPv6 地址前面，包括那些封裝或者是映射在 IPv6 地址里的 IPv4 地址， 比如 ::10.2.3.4 或 ::ffff:10.4.3.2。

位串類型

位串就是一串 1 和 0 的字符串。它們可以用于存儲(chǔ)和直觀化位掩碼。 我們有兩種 SQL 位類型：bit(n) 和bit varying(n)， 這里的n是一個(gè)正整數(shù)。

bit 類型的數(shù)據(jù)必須準(zhǔn)確匹配長(zhǎng)度 n， 試圖存儲(chǔ)短些或者長(zhǎng)一些的數(shù)據(jù)都是錯(cuò)誤的。bit varying 類型數(shù)據(jù)是最長(zhǎng) n 的變長(zhǎng)類型；更長(zhǎng)的串會(huì)被拒絕。 寫(xiě)一個(gè)沒(méi)有長(zhǎng)度的bit 等效于 bit(1)， 沒(méi)有長(zhǎng)度的 bit varying 意思是沒(méi)有長(zhǎng)度限制。

文本搜索類型

全文檢索即通過(guò)自然語(yǔ)言文檔的集合來(lái)找到那些匹配一個(gè)查詢的檢索。

PostgreSQL 提供了兩種數(shù)據(jù)類型用于支持全文檢索：

UUID 類型

uuid 數(shù)據(jù)類型用來(lái)存儲(chǔ) RFC 4122，ISO/IEF 9834-8:2005 以及相關(guān)標(biāo)準(zhǔn)定義的通用唯一標(biāo)識(shí)符（UUID）。 （一些系統(tǒng)認(rèn)為這個(gè)數(shù)據(jù)類型為全球唯一標(biāo)識(shí)符，或GUID。） 這個(gè)標(biāo)識(shí)符是一個(gè)由算法產(chǎn)生的 128 位標(biāo)識(shí)符，使它不可能在已知使用相同算法的模塊中和其他方式產(chǎn)生的標(biāo)識(shí)符相同。 因此，對(duì)分布式系統(tǒng)而言，這種標(biāo)識(shí)符比序列能更好的提供唯一性保證，因?yàn)樾蛄兄荒茉趩我粩?shù)據(jù)庫(kù)中保證唯一。

UUID 被寫(xiě)成一個(gè)小寫(xiě)十六進(jìn)制數(shù)字的序列，由分字符分成幾組， 特別是一組8位數(shù)字+3組4位數(shù)字+一組12位數(shù)字，總共 32 個(gè)數(shù)字代表 128 位， 一個(gè)這種標(biāo)準(zhǔn)的 UUID 例子如下：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML 類型

xml 數(shù)據(jù)類型可以用于存儲(chǔ)XML數(shù)據(jù)。 將 XML 數(shù)據(jù)存到 text 類型中的優(yōu)勢(shì)在于它能夠?yàn)榻Y(jié)構(gòu)良好性來(lái)檢查輸入值， 并且還支持函數(shù)對(duì)其進(jìn)行類型安全性檢查。 要使用這個(gè)數(shù)據(jù)類型，編譯時(shí)必須使用 configure --with-libxml。

xml 可以存儲(chǔ)由XML標(biāo)準(zhǔn)定義的格式良好的"文檔"， 以及由 XML 標(biāo)準(zhǔn)中的 XMLDecl? content 定義的"內(nèi)容"片段， 大致上，這意味著內(nèi)容片段可以有多個(gè)頂級(jí)元素或字符節(jié)點(diǎn)。 xmlvalue IS DOCUMENT 表達(dá)式可以用來(lái)判斷一個(gè)特定的 xml 值是一個(gè)完整的文件還是內(nèi)容片段。

創(chuàng)建XML值

使用函數(shù) xmlparse: 來(lái)從字符數(shù)據(jù)產(chǎn)生 xml 類型的值：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

JSON 類型

json 數(shù)據(jù)類型可以用來(lái)存儲(chǔ) JSON（JavaScript Object Notation）數(shù)據(jù)， 這樣的數(shù)據(jù)也可以存儲(chǔ)為 text，但是 json 數(shù)據(jù)類型更有利于檢查每個(gè)存儲(chǔ)的數(shù)值是可用的 JSON 值。

此外還有相關(guān)的函數(shù)來(lái)處理 json 數(shù)據(jù)：

數(shù)組類型

PostgreSQL 允許將字段定義成變長(zhǎng)的多維數(shù)組。

數(shù)組類型可以是任何基本類型或用戶定義類型，枚舉類型或復(fù)合類型。

聲明數(shù)組

創(chuàng)建表的時(shí)候，我們可以聲明數(shù)組，方式如下：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter 為一位整型數(shù)組、schedule 為二維文本類型數(shù)組。

我們也可以使用 "ARRAY" 關(guān)鍵字，如下所示：

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

插入值

插入值使用花括號(hào) {}，元素在 {} 使用逗號(hào)隔開(kāi)：

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"}}');

訪問(wèn)數(shù)組

現(xiàn)在我們可以在這個(gè)表上運(yùn)行一些查詢。

首先，我們演示如何訪問(wèn)數(shù)組的一個(gè)元素。 這個(gè)查詢檢索在第二季度薪水變化的雇員名：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

數(shù)組的下標(biāo)數(shù)字是寫(xiě)在方括弧內(nèi)的。

修改數(shù)組

我們可以對(duì)數(shù)組的值進(jìn)行修改：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用 ARRAY 構(gòu)造器語(yǔ)法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

數(shù)組中檢索

要搜索一個(gè)數(shù)組中的數(shù)值，你必須檢查該數(shù)組的每一個(gè)值。

比如：

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;

另外，你可以用下面的語(yǔ)句找出數(shù)組中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

或者，可以使用 generate_subscripts 函數(shù)。例如：

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

復(fù)合類型

復(fù)合類型表示一行或者一條記錄的結(jié)構(gòu)； 它實(shí)際上只是一個(gè)字段名和它們的數(shù)據(jù)類型的列表。PostgreSQL 允許像簡(jiǎn)單數(shù)據(jù)類型那樣使用復(fù)合類型。比如，一個(gè)表的某個(gè)字段可以聲明為一個(gè)復(fù)合類型。

聲明復(fù)合類型

下面是兩個(gè)定義復(fù)合類型的簡(jiǎn)單例子：

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

語(yǔ)法類似于 CREATE TABLE，只是這里只可以聲明字段名字和類型。

定義了類型，我們就可以用它創(chuàng)建表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

復(fù)合類型值輸入

要以文本常量書(shū)寫(xiě)復(fù)合類型值，在圓括弧里包圍字段值并且用逗號(hào)分隔他們。 你可以在任何字段值周圍放上雙引號(hào)，如果值本身包含逗號(hào)或者圓括弧， 你必須用雙引號(hào)括起。

復(fù)合類型常量的一般格式如下：

'( val1 , val2 , ... )'

一個(gè)例子是:

'("fuzzy dice",42,1.99)'

訪問(wèn)復(fù)合類型

要訪問(wèn)復(fù)合類型字段的一個(gè)域，我們寫(xiě)出一個(gè)點(diǎn)以及域的名字， 非常類似從一個(gè)表名字里選出一個(gè)字段。實(shí)際上，因?yàn)閷?shí)在太像從表名字中選取字段， 所以我們經(jīng)常需要用圓括弧來(lái)避免分析器混淆。比如，你可能需要從on_hand 例子表中選取一些子域，像下面這樣：

SELECT item.name FROM on_hand WHERE item.price > 9.99;

這樣將不能工作，因?yàn)楦鶕?jù) SQL 語(yǔ)法，item是從一個(gè)表名字選取的， 而不是一個(gè)字段名字。你必須像下面這樣寫(xiě)：

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者如果你也需要使用表名字(比如，在一個(gè)多表查詢里)，那么這么寫(xiě)：

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

現(xiàn)在圓括弧對(duì)象正確地解析為一個(gè)指向item字段的引用，然后就可以從中選取子域。

范圍類型

范圍數(shù)據(jù)類型代表著某一元素類型在一定范圍內(nèi)的值。

例如，timestamp 范圍可能被用于代表一間會(huì)議室被預(yù)定的時(shí)間范圍。

PostgreSQL 內(nèi)置的范圍類型有：

• int4range — integer的范圍
• int8range —bigint的范圍
• numrange —numeric的范圍
• tsrange —timestamp without time zone的范圍
• tstzrange —timestamp with time zone的范圍
• daterange —date的范圍

此外，你可以定義你自己的范圍類型。

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重疊
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上邊界
SELECT upper(int8range(15, 25));

-- 計(jì)算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范圍是否為空
SELECT isempty(numrange(1, 5));

范圍值的輸入必須遵循下面的格式：

(下邊界,上邊界)
(下邊界,上邊界]
[下邊界,上邊界)
[下邊界,上邊界]
空

圓括號(hào)或者方括號(hào)顯示下邊界和上邊界是不包含的還是包含的。注意最后的格式是 空，代表著一個(gè)空的范圍（一個(gè)不含有值的范圍）。

-- 包括3，不包括7，并且包括二者之間的所有點(diǎn)
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之間所有點(diǎn)
SELECT '(3,7)'::int4range;

-- 只包括單一值4
SELECT '[4,4]'::int4range;

-- 不包括點(diǎn)（被標(biāo)準(zhǔn)化為‘空’）
SELECT '[4,4)'::int4range;

對(duì)象標(biāo)識(shí)符類型

PostgreSQL 在內(nèi)部使用對(duì)象標(biāo)識(shí)符(OID)作為各種系統(tǒng)表的主鍵。

同時(shí)，系統(tǒng)不會(huì)給用戶創(chuàng)建的表增加一個(gè) OID 系統(tǒng)字段(除非在建表時(shí)聲明了WITH OIDS 或者配置參數(shù)default_with_oids設(shè)置為開(kāi)啟)。oid 類型代表一個(gè)對(duì)象標(biāo)識(shí)符。除此以外 oid 還有幾個(gè)別名：regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, 和regdictionary。

偽類型

PostgreSQL類型系統(tǒng)包含一系列特殊用途的條目， 它們按照類別來(lái)說(shuō)叫做偽類型。偽類型不能作為字段的數(shù)據(jù)類型， 但是它可以用于聲明一個(gè)函數(shù)的參數(shù)或者結(jié)果類型。 偽類型在一個(gè)函數(shù)不只是簡(jiǎn)單地接受并返回某種SQL 數(shù)據(jù)類型的情況下很有用。

下表列出了所有的偽類型：