程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> PostgreSQL >> PostgreSQL數據類型

PostgreSQL數據類型

編輯：PostgreSQL

本章討論PostgreSQL數據類型。在創建表的同時，要求每列都要指定數據類型，即什麼樣的數據要存儲在表中的字段。

這使幾個好處：

一致性: 對相同的數據類型的列的操作給出了一致的結果，通常是最快的。
驗證: 正確使用的數據類型表示數據和拒絕的范圍之外的數據類型的數據格式驗證。
壓縮: 作為一列可以存儲單一類型的值，它被存儲在一個緊湊的方式。
性能: 適當地使用的數據類型給出最有效的數據存儲。存儲的值可以被快速處理，從而提高性能。

PostgreSQL支持一系列廣泛的數據類型。此外，用戶可以使用SQL命令CREATE TYPE創建自己的自定義數據類型。在PostgreSQL中有不同類別的數據類型。如下：

數值類型

數值類型由兩個字節，4字節和8字節的整數，4字節和8字節的浮點數和可選精度的小數。下表列出了可用的類型。

Name Storage Size Description Range smallint 2 bytes small-range integer -32768 to +32767 integer 4 bytes typical choice for integer -2147483648 to +2147483647 bigint 8 bytes large-range integer -9223372036854775808 to 9223372036854775807 decimal variable user-specified precision,exact up to 131072 digits before the decimal yiibai; up to 16383 digits after the decimal yiibai numeric variable user-specified precision,exact up to 131072 digits before the decimal yiibai; up to 16383 digits after the decimal yiibai real 4 bytes variable-precision,inexact 6 decimal digits precision double precision 8 bytes variable-precision,inexact 15 decimal digits precision smallserial 2 bytes small autoincrementing integer 1 to 32767 serial 4 bytes autoincrementing integer 1 to 2147483647 bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

貨幣類型

貨幣類型存儲的貨幣金額與一個固定的分數精度。可以轉換為金錢的數字，int和bigint數據類型的值。不推薦使用浮點數來處理金錢的潛力，由於捨入誤差。

Name Storage Size Description Range money 8 bytes currency amount -92233720368547758.08 to +92233720368547758.07

字符類型

下表列出了可在PostgreSQL通用字符類型。

名稱描述 character varying(n), varchar(n) variable-length with limit character(n), char(n) fixed-length, blank padded text variable unlimited length

二進制數據類型

bytea數據類型允許存儲二進制字符串，如下面的表格中說明。

Name Storage Size Description bytea 1 or 4 bytes plus the actual binary string variable-length binary string

日期/時間類型

PostgreSQL支持全套的SQL日期和時間類型，列於下表。根據公歷日期計算。在這裡，所有的類型有日期類型以外，其分辨率為day1微秒/14位的解析度。

Name Storage Size Description Low Value High Value timestamp [(p)] [without time zone ] 8 bytes both date and time (no time zone) 4713 BC 294276 AD timestamp [(p) ] with time zone 8 bytes both date and time, with time zone 4713 BC 294276 AD date 4 bytes date (no time of day) 4713 BC 5874897 AD time [ (p)] [ without time zone ] 8 bytes time of day (no date) 00:00:00 24:00:00 time [ (p)] with time zone 12 bytes times of day only, with time zone 00:00:00+1459 24:00:00-1459 interval [fields ] [(p) ] 12 bytes time interval -178000000 years 178000000 years

布爾類型

PostgreSQL提供了標准的SQL類型布爾值。布爾類型可以有幾種狀態：true，false，和第三狀態null，這是SQL空值表示。

Name Storage Size Description boolean 1 byte state of true or false

枚舉類型

枚舉（枚舉）類型的數據類型，包括靜態，有序設置的值。在許多編程語言支持枚舉類型，它們是相等。

Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered set of values, for example compass directions, i.e. NORTH, SOUTH, EAST, and WEST or days of the week as below:

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

枚舉一旦產生，它們可以像任何其他類型。

幾何類型

幾何數據類型表示二維空間對象。最根本的不同點是形成的所有其他類型的基礎。

Name Storage Size Representation Description yiibai 16 bytes Yiibai on a plane (x,y) line 32 bytes Infinite line (not fully implemented) ((x1,y1),(x2,y2)) lseg 32 bytes Finite line segment ((x1,y1),(x2,y2)) box 32 bytes Rectangular box ((x1,y1),(x2,y2)) path 16+16n bytes Closed path (similar to polygon) ((x1,y1),...) path 16+16n bytes Open path [(x1,y1),...] polygon 40+16n Polygon (similar to closed path) ((x1,y1),...) circle 24 bytes Circle <(x,y),r> (center yiibai and radius)

網絡地址類型

PostgreSQL提供的數據類型來存儲的IPv4，IPv6的地址和MAC地址。這是更好地使用這些類型，而不是純文本類型存儲網絡地址，因為這些類型提供輸入錯誤檢查和特殊的操作和函數。

Name Storage Size Description cidr 7 or 19 bytes IPv4 and IPv6 networks inet 7 or 19 bytes IPv4 and IPv6 hosts and networks macaddr 6 bytes MAC addresses

位串類型

位串類型用於存儲位掩碼。他們要麼是0或1。 SQL位類型有兩種：（n）的位而變位（n）的，其中n是一個正整數

文本搜索類型

這個類型支持全文檢索，這是通過自然語言文檔的集合的搜索，找到那些最符合查詢活動。這有兩種數據類型：

名稱描述 tsvector This is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as "lexemes". tsquery This stores lexemes that are to be searched for, and combines them honoring the Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be used to enforce grouping of the operators.

UUID類型

一個UUID（通用唯一標識符）寫成小寫的十六進制數字序列，由連字號，特別是一組8位數字，然後由三組4位數字，然後由一組12位數字分開幾組，總32位，128位代表。

一個UUID的例子是: 550e8400-e29b-41d4-a716-446655440000

XML Type

xml數據類型可以用來存儲XML數據。對於存儲XML數據，首先創建XML值函數XMLPARSE如下：

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對象符號）數據。這樣的數據也可以被存儲為文本，但json數據類型具有的優點是檢查每個存儲的值是否為有效的JSON值。也有相關的支持功能可以直接用來處理JSON數據類型，如下所示：

Example Example Result array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]] row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}

陣列/數組類型

PostgreSQL的機會定義為可變長度的多維數組的列一個表。任何內置或用戶定義的基本類型數組，枚舉類型，或者可以創建復合型。

DECLARATION OF ARRAYS

數組類型可以聲明為：

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';

或數組表達式語法：

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

尋找ARRAYS

搜索數組的一個例子如下所示。

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的。行關鍵字實際上是可選的表達式中，只要有一個以上的字段。

訪問復合類型

要訪問一個復合列的字段，字段名，使用點很像選擇字段從一個表名。例如，要選擇一些子字段,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 - Range of integer
int8range - Range of bigint
numrange - Range of numeric
tsrange - Range of timestamp without time zone
tstzrange - Range of timestamp with time zone
daterange - Range of date

可以創建自定義的范圍類型，做出新的類型的適用范圍，如使用int類型為基礎的IP地址范圍，或者使用浮點數據類型為基礎的浮動范圍。

范圍類型支持包容性和排他性的范圍邊界分別使用[]和（）個字符，例如： [4,9]'代表所有從包括4但不包括9的整數。

對象標識符類型

對象標識符（OID）內部使用PostgreSQL作為各種系統表的主鍵。 OIDS IfWITH指定或default_with_oids配置變量，只有在這樣的情況下啟用的OID被添加到用戶創建的表。下表列出了幾個別名類型。 OID別名類型有沒有自己的操作，除了專門的輸入和輸出過程。

Name References Description Value Example oid any numeric object identifier 564182 regproc pg_proc function name sum regprocedure pg_proc function with argument types sum(int4) regoper pg_operator operator name + regoperator pg_operator operator with argument types *(integer,integer) or -(NONE,integer) regclass pg_class relation name pg_type regtype pg_type data type name integer regconfig pg_ts_config text search configuration english regdictionary pg_ts_dict text search dictionary simple

偽類型

PostgreSQL類型系統包含了一些特殊用途的統稱為偽類型的項。一個偽類型不能被用作列的數據類型，但它可以用來聲明一個函數的參數或結果類型。下表列出了現有的偽類型。

名稱描述 any Indicates that a function accepts any input data type. anyelement Indicates that a function accepts any data type. anyarray Indicates that a function accepts any array data type. anynonarray Indicates that a function accepts any non-array data type. anyenum Indicates that a function accepts any enum data type. anyrange Indicates that a function accepts any range data type. cstring Indicates that a function accepts or returns a null-terminated C string. internal Indicates that a function accepts or returns a server-internal data type. language_handler A procedural language call handler is declared to return language_handler. fdw_handler A foreign-data wrapper handler is declared to return fdw_handler. record Identifies a function returning an unspecified row type. trigger A trigger function is declared to return trigger. void Indicates that a function returns no value.