PostgreSQL 資料類型

資料類型是在建立表的時候為每個字段設定的。

設定資料類型的好處：PostgreSQL提 供了豐富的資料類型。使用者可以使用 CREATE TYPE 指令在資料庫中建立新的資料類型。

數值類型

數值類型由 2 位元組、4 位元組或 8 位元組的整數以及 4 位元組或 8 位元組的浮點數和可選精度的十進制數組成。

下表列出了可用的數值類型。

名字	存儲長度	描述	範圍
smallint	2 位元組	小範圍整數	-32768 到 +32767
integer	4 位元組	常用的整數	-2147483648 到 [phone]
bigint	8 位元組	大範圍整數	-9223372036854775808 到 +9223372036854775807
decimal	可變長	使用者指定的精度，精确	小數點前 131072 位；小數點後 16383 位
numeric
real		可變精度，不精确	6 位十進制數字精度
double precision			15 位十進制數字精度
smallserial		自增的小範圍整數	1 到 32767
serial		自增整數	1 到 2147483647
bigserial		自增的大範圍整數	1 到 9223372036854775807

貨币類型

money 類型存儲帶有固定小數精度的貨币金額。

numeric、int 和 bigint 類型的值可以轉換為 money，不建議使用浮點數來處理處理貨币類型，因為存在舍入錯誤的可能性。

	存儲容量
money		貨币金額	-92233720368547758.08 到 +92233720368547758.07

字元類型

下表列出了 PostgreSQL 所支援的字元類型：

序号	名字 & 描述
1	character varying(n), varchar(n) 變長，有長度限制
2	character(n), char(n) f定長,不足補空白
3	text 變長，無長度限制

日期/時間類型

下表列出了 PostgreSQL 支援的日期和時間類型。

	存儲空間		最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]		日期和時間(無時區)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone		日期和時間，有時區
date		隻用于日期		5874897 AD	1 天
time [ (p) ] [ without time zone ]		隻用于一日内時間	00:00:00	24:00:00
time [ (p) ] with time zone	12 位元組	隻用于一日内時間，帶時區	00:00:00+1459	24:00:00-1459
interval [ fields ] [ (p) ]		時間間隔	-178000000 年	178000000 年

布爾類型

PostgreSQL 支援标準的 boolean 資料類型。

boolean 有"true"(真)或"false"(假)兩個狀态， 第三種"unknown"(未知)狀态，用 NULL 表示。

名稱	存儲格式
boolean	1 位元組	true/false

枚舉類型

枚舉類型是一個包含靜态和值的有序集合的資料類型。

PostgtesSQL中的枚舉類型類似于 C 語言中的 enum 類型。

與其他類型不同的是枚舉類型需要使用 CREATE TYPE 指令建立。

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

建立一周中的幾天，如下所示:

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

就像其他類型一樣，一旦建立，枚舉類型可以用于表和函數定義。

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)
           

幾何類型

幾何資料類型表示二維的平面物體。

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

最基本的類型：點。它是其它類型的基礎。

		說明	表現形式
point	16 位元組	平面中的點	(x,y)
line	32 位元組	(無窮)直線(未完全實作)	((x1,y1),(x2,y2))
lseg		(有限)線段
box		矩形
path	16+16n 位元組	閉合路徑(與多邊形類似)	((x1,y1),...)
		開放路徑	[(x1,y1),...]
polygon	40+16n 位元組	多邊形(與閉合路徑相似)
circle	24 位元組	圓	<(x,y),r> (圓心和半徑)

網絡位址類型

PostgreSQL 提供用于存儲 IPv4 、IPv6 、MAC 位址的資料類型。

用這些資料類型存儲網絡位址比用純文字類型好， 因為這些類型提供輸入錯誤檢查和特殊的操作和功能。

cidr	7 或 19 位元組	IPv4 或 IPv6 網絡
inet		IPv4 或 IPv6 主機和網絡
macaddr	6 位元組	MAC 位址

在對 inet 或 cidr 資料類型進行排序的時候， IPv4 位址總是排在 IPv6 位址前面，包括那些封裝或者是映射在 IPv6 位址裡的 IPv4 位址， 比如 ::10.2.3.4 或 ::ffff:10.4.3.2。

位串類型

位串就是一串 1 和 0 的字元串。它們可以用于存儲和直覺化位掩碼。 我們有兩種 SQL 位類型：bit(n) 和bit varying(n)， 這裡的n是一個正整數。

bit 類型的資料必須準确比對長度 n， 試圖存儲短些或者長一些的資料都是錯誤的。bit varying 類型資料是最長 n 的變長類型；更長的串會被拒絕。 寫一個沒有長度的bit 等效于 bit(1)， 沒有長度的 bit varying 意思是沒有長度限制。

文本搜尋類型

全文檢索即通過自然語言文檔的集合來找到那些比對一個查詢的檢索。

PostgreSQL 提供了兩種資料類型用于支援全文檢索：

	tsvector tsvector 的值是一個無重複值的 lexemes 排序清單， 即一些同一個詞的不同變種的标準化。
	tsquery tsquery 存儲用于檢索的詞彙，并且使用布爾操作符 &(AND)，|(OR)和!(NOT) 來組合它們，括号用來強調操作符的分組。

UUID 類型

uuid 資料類型用來存儲 RFC 4122，ISO/IEF 9834-8:2005 以及相關标準定義的通用唯一辨別符（UUID）。 （一些系統認為這個資料類型為全球唯一辨別符，或GUID。） 這個辨別符是一個由算法産生的 128 位辨別符，使它不可能在已知使用相同算法的子產品中和其他方式産生的辨別符相同。 是以，對分布式系統而言，這種辨別符比序列能更好的提供唯一性保證，因為序列隻能在單一資料庫中保證唯一。

UUID 被寫成一個小寫十六進制數字的序列，由分字元分成幾組， 特别是一組8位數字+3組4位數字+一組12位數字，總共 32 個數字代表 128 位， 一個這種标準的 UUID 例子如下：

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

XML 類型

xml 資料類型可以用于存儲XML資料。 将 XML 資料存到 text 類型中的優勢在于它能夠為結構良好性來檢查輸入值， 并且還支援函數對其進行類型安全性檢查。 要使用這個資料類型，編譯時必須使用 configure --with-libxml。

xml 可以存儲由XML标準定義的格式良好的"文檔"， 以及由 XML 标準中的 XMLDecl? content 定義的"内容"片段， 大緻上，這意味着内容片段可以有多個頂級元素或字元節點。 xmlvalue IS DOCUMENT 表達式可以用來判斷一個特定的 xml 值是一個完整的檔案還是内容片段。

建立XML值

使用函數 xmlparse: 來從字元資料産生 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 資料類型可以用來存儲 JSON（JavaScript Object Notation）資料， 這樣的資料也可以存儲為 text，但是 json 資料類型更有利于檢查每個存儲的數值是可用的 JSON 值。

此外還有相關的函數來處理 json 資料：

執行個體	執行個體結果
array_to_json('{{1,5},{99,100}}'::int[])	[[1,5],[99,100]]
row_to_json(row(1,'foo'))	{"f1":1,"f2":"foo"}

數組類型

PostgreSQL 允許将字段定義成變長的多元數組。

數組類型可以是任何基本類型或使用者定義類型，枚舉類型或複合類型。

聲明數組

建立表的時候，我們可以聲明數組，方式如下：

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

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

我們也可以使用 "ARRAY" 關鍵字，如下所示：

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

插入值

插入值使用花括号 {}，元素在 {} 使用逗号隔開：

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

通路數組

現在我們可以在這個表上運作一些查詢。

首先，我們示範如何通路數組的一個元素。 這個查詢檢索在第二季度薪水變化的雇員名：

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

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

數組的下标數字是寫在方括弧内的。

修改數組

我們可以對數組的值進行修改：

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

或者使用 ARRAY 構造器文法：

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

數組中檢索

要搜尋一個數組中的數值，你必須檢查該數組的每一個值。

比如：

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;
           

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

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

或者，可以使用 generate_subscripts 函數。例如：

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;
           

複合類型

複合類型表示一行或者一條記錄的結構； 它實際上隻是一個字段名和它們的資料類型的清單。PostgreSQL 允許像簡單資料類型那樣使用複合類型。比如，一個表的某個字段可以聲明為一個複合類型。

聲明複合類型

下面是兩個定義複合類型的簡單例子：

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

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

文法類似于 CREATE TABLE，隻是這裡隻可以聲明字段名字和類型。

定義了類型，我們就可以用它建立表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

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

複合類型值輸入

要以文本常量書寫複合類型值，在圓括弧裡包圍字段值并且用逗号分隔他們。 你可以在任何字段值周圍放上雙引号，如果值本身包含逗号或者圓括弧， 你必須用雙引号括起。

複合類型常量的一般格式如下：

'( val1 , val2 , ... )'
           

一個例子是:

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

通路複合類型

要通路複合類型字段的一個域，我們寫出一個點以及域的名字， 非常類似從一個表名字裡選出一個字段。實際上，因為實在太像從表名字中選取字段， 是以我們經常需要用圓括弧來避免分析器混淆。比如，你可能需要從on_hand 例子表中選取一些子域，像下面這樣：

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

這樣将不能工作，因為根據 SQL 文法，item是從一個表名字選取的， 而不是一個字段名字。你必須像下面這樣寫：

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

或者如果你也需要使用表名字(比如，在一個多表查詢裡)，那麼這麼寫：

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

現在圓括弧對象正确地解析為一個指向item字段的引用，然後就可以從中選取子域。

範圍類型

範圍資料類型代表着某一進制素類型在一定範圍内的值。

例如，timestamp 範圍可能被用于代表一間會議室被預定的時間範圍。

PostgreSQL 内置的範圍類型有：

• 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));

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

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

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

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

圓括号或者方括号顯示下邊界和上邊界是不包含的還是包含的。注意最後的格式是 空，代表着一個空的範圍（一個不含有值的範圍）。

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

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

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

-- 不包括點（被标準化為‘空’）
SELECT '[4,4)'::int4range;
           

對象辨別符類型

PostgreSQL 在内部使用對象辨別符(OID)作為各種系統表的主鍵。

	引用		數值例子
oid	任意	數字化的對象辨別符	564182
regproc	pg_proc	函數名字	sum
regprocedure		帶參數類型的函數	sum(int4)
regoper	pg_operator	操作符名	+
regoperator		帶參數類型的操作符	*(integer,integer) 或 -(NONE,integer)
regclass	pg_class	關系名	pg_type
regtype		資料類型名
regconfig	pg_ts_config	文本搜尋配置	english
regdictionary	pg_ts_dict	文本搜尋字典	simple

僞類型

any	表示一個函數接受任何輸入資料類型。
anyelement	表示一個函數接受任何資料類型。
anyarray	表示一個函數接受任意數組資料類型。
anynonarray	表示一個函數接受任意非數組資料類型。
anyenum	表示一個函數接受任意枚舉資料類型。
anyrange	表示一個函數接受任意範圍資料類型。
cstring	表示一個函數接受或者傳回一個空結尾的 C 字元串。
internal	表示一個函數接受或者傳回一種伺服器内部的資料類型。
language_handler	一個過程語言調用處理器聲明為傳回language_handler。
fdw_handler	一個外部資料封裝器聲明為傳回fdw_handler。
record	辨別一個函數傳回一個未聲明的行類型。
trigger	一個觸發器函數聲明為傳回trigger。
void	表示一個函數不傳回數值。
opaque	一個已經過時的類型，以前用于所有上面這些用途。