PostgreSQL - 資料型別

---

---

在本章中，我們將討論 PostgreSQL 中使用的資料型別。在建立表時，對於每一列，您需要指定一個數據型別，即您希望在表字段中儲存哪種資料。

這帶來了以下好處：

• 一致性 - 對相同資料型別的列執行操作會產生一致的結果，並且通常速度最快。

• 驗證 - 正確使用資料型別意味著對資料進行格式驗證，並拒絕超出資料類型範圍的資料。

• 緊湊性 - 由於一列可以儲存單一型別的值，因此以緊湊的方式儲存。

• 效能 - 正確使用資料型別可以實現最高效的資料儲存。儲存的值可以快速處理，從而提高效能。

PostgreSQL 支援廣泛的資料型別集。此外，使用者可以使用 CREATE TYPE SQL 命令建立自己的自定義資料型別。PostgreSQL 中的資料型別分為不同的類別。下面將討論這些類別。

數值型別

數值型別包括兩位元組、四位元組和八位元組整數、四位元組和八位元組浮點數以及可選精度的十進位制數。下表列出了可用的型別。

貨幣型別

money 型別以固定的分數精度儲存貨幣金額。numeric、int 和 bigint 資料型別的值可以轉換為 money。由於可能存在舍入誤差，因此不建議使用浮點數處理貨幣。

字元型別

下表列出了 PostgreSQL 中可用的通用字元型別。

二進位制資料型別

bytea 資料型別允許儲存二進位制字串，如下表所示。

日期/時間型別

PostgreSQL 支援完整的 SQL 日期和時間型別集，如下表所示。日期根據公曆計算。這裡，所有型別的解析度為1 微秒 / 14 位數，除了date型別，其解析度為天。

布林型別

PostgreSQL 提供標準的 SQL 型別布林值。布林資料型別可以具有true、false兩種狀態，以及第三種狀態unknown，它由 SQL NULL 值表示。

列舉型別

列舉 (enum) 型別是包含一組靜態有序值的資料型別。它們等效於許多程式語言中支援的 enum 型別。

與其他型別不同，列舉型別需要使用 CREATE TYPE 命令建立。此型別用於儲存一組靜態有序的值。例如指南針方向，即 NORTH、SOUTH、EAST 和 WEST，或一週中的日期，如下所示：

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

建立後，列舉型別可以像任何其他型別一樣使用。

幾何型別

幾何資料型別表示二維空間物件。最基本型別點是所有其他型別的基礎。

網路地址型別

PostgreSQL 提供資料型別來儲存 IPv4、IPv6 和 MAC 地址。最好使用這些型別而不是純文字型別來儲存網路地址，因為這些型別提供了輸入錯誤檢查以及專門的運算子和函式。

位字串型別

位字串型別用於儲存位掩碼。它們要麼是 0 要麼是 1。有兩種 SQL 位型別：bit(n) 和 bit varying(n)，其中 n 是一個正整數。

文字搜尋型別

此型別支援全文搜尋，即搜尋自然語言文件集合以找到最匹配查詢的文件的活動。為此，有兩種資料型別：

UUID 型別

UUID（通用唯一識別符號）寫成一系列小寫十六進位制數字，分成幾組並用連字元分隔，具體來說是一組八位數字，後跟三組四位數字，然後是一組 12 位數字，總共 32 位數字，表示 128 位。

UUID 的示例為：550e8400-e29b-41d4-a716-446655440000

XML 型別

XML 資料型別可用於儲存 XML 資料。要儲存 XML 資料，首先必須使用以下函式 xmlparse 建立 XML 值：

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

JSON 型別

json 資料型別可用於儲存 JSON（JavaScript 物件表示法）資料。此類資料也可以儲存為text，但json資料型別的優勢在於它可以檢查每個儲存的值是否為有效的 JSON 值。還提供了相關的支援函式，可以直接用於處理 JSON 資料型別，如下所示。

陣列型別

PostgreSQL 提供了將表的一列定義為可變長度多維陣列的機會。可以建立任何內建或使用者定義的基本型別、列舉型別或複合型別的陣列。

陣列宣告

陣列型別可以宣告為

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

或使用關鍵字“ARRAY”宣告為

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

插入值

陣列值可以作為文字常量插入，將元素值括在花括號中並用逗號分隔。示例如下所示：

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’); 

訪問陣列

訪問陣列的示例如下所示。以下命令將選擇儲蓄在第二季度比第四季度多的使用者。

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

修改陣列

修改陣列的一個示例如下所示。

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

或者使用 ARRAY 表示式語法 -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

陣列搜尋

陣列搜尋的一個示例如下所示。

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

如果已知陣列的大小，則可以使用上面給出的搜尋方法。否則，以下示例顯示了在大小未知時如何搜尋。

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

複合型別

此型別表示欄位名稱及其資料型別的列表，即表中一行或記錄的結構。

複合型別的宣告

以下示例顯示如何聲明覆合型別

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

此資料型別可用於如下建立表中 -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

複合值輸入

複合值可以作為文字常量插入，將欄位值括在括號中並用逗號分隔。示例如下所示 -

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

這對上面定義的 inventory_item 有效。只要表示式中有多個欄位，ROW 關鍵字實際上就是可選的。

訪問複合型別

要訪問複合列的欄位，請使用點後跟欄位名稱，就像從表名中選擇欄位一樣。例如，要從我們的 on_hand 示例表中選擇一些子欄位，查詢將如下所示 -

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;

範圍型別

範圍型別表示使用資料範圍的資料型別。範圍型別可以是離散範圍（例如，所有整數 1 到 10）或連續範圍（例如，上午 10:00 到上午 11:00 之間的任何時間點）。

可用的內建範圍型別包括以下範圍 -

• int4range - 整數範圍

• int8range - 大整數範圍

• numrange - 數值範圍

• tsrange - 無時區時間戳範圍

• tstzrange - 有時區時間戳範圍

• daterange - 日期範圍

可以建立自定義範圍型別以使新的範圍型別可用，例如使用 inet 型別作為基類的 IP 地址範圍，或使用 float 資料型別作為基類的浮點範圍。

範圍型別分別使用 [ ] 和 ( ) 字元支援包含和排除範圍邊界。例如 '[4,9)' 表示從 4 開始（包括 4）到 9（不包括 9）的所有整數。

物件識別符號型別

物件識別符號 (OID) 在 PostgreSQL 內部用作各種系統表的主鍵。如果指定了 WITH OIDS 或啟用了 default_with_oids 配置變數，則在這種情況下，OID 將新增到使用者建立的表中。下表列出了幾個別名型別。OID 別名型別除了專門的輸入和輸出例程外沒有自己的操作。

偽型別

PostgreSQL 型別系統包含許多特殊用途的條目，這些條目統稱為偽型別。偽型別不能用作列資料型別，但可以用作函式的引數或結果型別。

下表列出了現有的偽型別。