PostgreSQL教程(四)：數(shù)據(jù)類型詳解
一、數(shù)值類型：
下面是PostgreSQL所支持的數(shù)值類型的列表和簡(jiǎn)單說(shuō)明：
1. 整數(shù)類型：
類型smallint、integer和bigint存儲(chǔ)各種范圍的全部是數(shù)字的數(shù)，也就是沒有小數(shù)部分的數(shù)字。試圖存儲(chǔ)超出范圍以外的數(shù)值將導(dǎo)致一個(gè)錯(cuò)誤。常用的類型是integer，因?yàn)樗峁┝嗽诜秶⒋鎯?chǔ)空間和性能之間的最佳平衡。一般只有在磁盤空間緊張的時(shí)候才使用smallint。而只有在integer的范圍不夠的時(shí)候才使用bigint，因?yàn)榍罢?integer)絕對(duì)快得多。
2. 任意精度數(shù)值：
類型numeric可以存儲(chǔ)最多1000位精度的數(shù)字并且準(zhǔn)確地進(jìn)行計(jì)算。因此非常適合用于貨幣金額和其它要求計(jì)算準(zhǔn)確的數(shù)量。不過(guò)，numeric類型上的算術(shù)運(yùn)算比整數(shù)類型或者浮點(diǎn)數(shù)類型要慢很多。
numeric字段的最大精度和最大比例都是可以配置的。要聲明一個(gè)類型為numeric的字段，你可以用下面的語(yǔ)法：
NUMERIC(precision,scale)
比如數(shù)字23.5141的精度為6，而刻度為4。
在目前的PostgreSQL版本中，decimal和numeric是等效的。
3. 浮點(diǎn)數(shù)類型：
數(shù)據(jù)類型real和double是不準(zhǔn)確的、犧牲精度的數(shù)字類型。不準(zhǔn)確意味著一些數(shù)值不能準(zhǔn)確地轉(zhuǎn)換成內(nèi)部格式并且是以近似的形式存儲(chǔ)的，因此存儲(chǔ)后再把數(shù)據(jù)打印出來(lái)可能顯示一些缺失。
4. Serial(序號(hào))類型：
serial和bigserial類型不是真正的類型，只是為在表中設(shè)置唯一標(biāo)識(shí)做的概念上的便利。
CREATE TABLE tablename (
colname SERIAL
);
等價(jià)于
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);
這樣，我們就創(chuàng)建了一個(gè)整數(shù)字段并且把它的缺省數(shù)值安排為從一個(gè)序列發(fā)生器取值。應(yīng)用了一個(gè)NOT NULL約束以確?？罩挡粫?huì)被插入。在大多數(shù)情況下你可能還希望附加一個(gè)UNIQUE或者PRIMARY KEY約束避免意外地插入重復(fù)的數(shù)值，但這個(gè)不是自動(dòng)發(fā)生的。因此，如果你希望一個(gè)序列字段有一個(gè)唯一約束或者一個(gè)主鍵，那么你現(xiàn)在必須聲明，就像其它數(shù)據(jù)類型一樣。
還需要另外說(shuō)明的是，一個(gè)serial類型創(chuàng)建的序列在其所屬字段被刪除時(shí)，該序列也將被自動(dòng)刪除，但是其它情況下是不會(huì)被刪除的。因此，如果你想用同一個(gè)序列發(fā)生器同時(shí)給幾個(gè)字段提供數(shù)據(jù)，那么就應(yīng)該以獨(dú)立對(duì)象的方式創(chuàng)建該序列發(fā)生器。
二、字符類型：
下面是PostgreSQL所支持的字符類型的列表和簡(jiǎn)單說(shuō)明：
SQL 定義了兩種基本的字符類型，varchar(n)和char(n)，這里的n是一個(gè)正整數(shù)。兩種類型都可以存儲(chǔ)最多n個(gè)字符長(zhǎng)的字串，試圖存儲(chǔ)更長(zhǎng)的字串到這些類型的字段里會(huì)產(chǎn)生一個(gè)錯(cuò)誤，除非超出長(zhǎng)度的字符都是空白，這種情況下該字串將被截?cái)酁樽畲箝L(zhǎng)度。如果沒有長(zhǎng)度聲明，char等于char(1)，而varchar則可以接受任何長(zhǎng)度的字串。
MyTest=> CREATE TABLE testtable(first_col varchar(2));
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES('333'); --插入字符串的長(zhǎng)度，超過(guò)其字段定義的長(zhǎng)度，因此報(bào)錯(cuò)。
ERROR: value too long for type character varying(2)
--插入字符串中，超出字段定義長(zhǎng)度的部分是空格，因此可以插入，但是空白符被截?cái)唷?br /> MyTest=> INSERT INTO testtable VALUES('33 ');
INSERT 0 1
MyTest=> SELECT * FROM testtable;
first_col
-----------
33
(1 row)
這里需要注意的是，如果是將數(shù)值轉(zhuǎn)換成char(n)或者varchar(n)，那么超長(zhǎng)的數(shù)值將被截?cái)喑蒼個(gè)字符，而不會(huì)拋出錯(cuò)誤。
MyTest=> select 1234::varchar(2);
varchar
---------
12
(1 row)
最后需要提示的是，這三種類型之間沒有性能差別，只不過(guò)是在使用char類型時(shí)增加了存儲(chǔ)尺寸。雖然在某些其它的數(shù)據(jù)庫(kù)系統(tǒng)里，char(n)有一定的性能優(yōu)勢(shì)，但在PostgreSQL里沒有。在大多數(shù)情況下，應(yīng)該使用text或者varchar。
三、日期/時(shí)間類型：
下面是PostgreSQL所支持的日期/時(shí)間類型的列表和簡(jiǎn)單說(shuō)明：
1. 日期/時(shí)間輸入:
任何日期或者時(shí)間的文本輸入均需要由單引號(hào)包圍，就象一個(gè)文本字符串一樣。
1). 日期:
以下為合法的日期格式列表：
2). 時(shí)間：
以下為合法的時(shí)間格式列表：
3). 時(shí)間戳：
時(shí)間戳類型的有效輸入由一個(gè)日期和時(shí)間的聯(lián)接組成，后面跟著一個(gè)可選的時(shí)區(qū)。因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的數(shù)值。
2. 示例：
1). 在插入數(shù)據(jù)之前先查看datestyle系統(tǒng)變量的值：
MyTest=> show datestyle;
DateStyle
-----------
ISO, YMD
(1 row)
2). 創(chuàng)建包含日期、時(shí)間和時(shí)間戳類型的示例表：
MyTest=> CREATE TABLE testtable (id integer, date_col date, time_col time, timestamp_col timestamp);
CREATE TABLE
3). 插入數(shù)據(jù)：
MyTest=> INSERT INTO testtable(id,date_col) VALUES(1, DATE'01/02/03'); --datestyle為YMD
INSERT 0 1
MyTest=> SELECT id, date_col FROM testtable;
id | date_col
----+------------
1 | 2001-02-03
(1 row)
MyTest=> set datestyle = MDY;
SET
MyTest=> INSERT INTO testtable(id,date_col) VALUES(2, DATE'01/02/03'); --datestyle為MDY
INSERT 0 1
MyTest=> SELECT id,date_col FROM testtable;
id | date_col
----+------------
1 | 2001-02-03
2 | 2003-01-02
MyTest=> INSERT INTO testtable(id,time_col) VALUES(3, TIME'10:20:00'); --插入時(shí)間。
INSERT 0 1
MyTest=> SELECT id,time_col FROM testtable WHERE time_col IS NOT NULL;
id | time_col
----+----------
3 | 10:20:00
(1 row)
MyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(4, DATE'01/02/03');
INSERT 0 1
MyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(5, TIMESTAMP'01/02/03 10:20:00');
INSERT 0 1
MyTest=> SELECT id,timestamp_col FROM testtable WHERE timestamp_col IS NOT NULL;
id | timestamp_col
----+---------------------
4 | 2003-01-02 00:00:00
5 | 2003-01-02 10:20:00
(2 rows)
四、布爾類型：
PostgreSQL支持標(biāo)準(zhǔn)的SQL boolean數(shù)據(jù)類型。boolean只能有兩個(gè)狀態(tài)之一：真(True)或 假(False)。該類型占用1個(gè)字節(jié)。
"真"值的有效文本值是：
TRUE
't'
'true'
'y'
'yes'
'1'
而對(duì)于"假"而言，你可以使用下面這些：
FALSE
'f'
'false'
'n'
'no'
'0'
見如下使用方式：
MyTest=> CREATE TABLE testtable (a boolean, b text);
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES(TRUE, 'sic est');
INSERT 0 1
MyTest=> INSERT INTO testtable VALUES(FALSE, 'non est');
INSERT 0 1
MyTest=> SELECT * FROM testtable;
a | b
---+---------
t | sic est
f | non est
(2 rows)
MyTest=> SELECT * FROM testtable WHERE a;
a | b
---+---------
t | sic est
(1 row)
MyTest=> SELECT * FROM testtable WHERE a = true;
a | b
---+---------
t | sic est
(1 row)
五、位串類型：
位串就是一串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ì)被拒絕。寫一個(gè)沒有長(zhǎng)度的bit等效于bit(1)，沒有長(zhǎng)度的bit varying相當(dāng)于沒有長(zhǎng)度限制。
針對(duì)該類型，最后需要提醒的是，如果我們明確地把一個(gè)位串值轉(zhuǎn)換成bit(n)，那么它的右邊將被截?cái)嗷蛘咴谟疫呇a(bǔ)齊零，直到剛好n位，而不會(huì)拋出任何錯(cuò)誤。類似地，如果我們明確地把一個(gè)位串?dāng)?shù)值轉(zhuǎn)換成bit varying(n)，如果它超過(guò)n位，那么它的右邊將被截?cái)唷?見如下具體使用方式：
MyTest=> CREATE TABLE testtable (a bit(3), b bit varying(5));
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES (B'101', B'00');
INSERT 0 1
MyTest=> INSERT INTO testtable VALUES (B'10', B'101');
ERROR: bit string length 2 does not match type bit(3)
MyTest=> INSERT INTO testtable VALUES (B'10'::bit(3), B'101');
INSERT 0 1
MyTest=> SELECT * FROM testtable;
a | b
-----+-----
101 | 00
100 | 101
(2 rows)
MyTest=> SELECT B'11'::bit(3);
bit
-----
110
(1 row)
六、數(shù)組：
1. 數(shù)組類型聲明：
1). 創(chuàng)建字段含有數(shù)組類型的表。
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[] --還可以定義為integer[4]或integer ARRAY[4]
);
2). 插入數(shù)組數(shù)據(jù)：
MyTest=# INSERT INTO sal_emp VALUES ('Bill', '{11000, 12000, 13000, 14000}');
INSERT 0 1
MyTest=# INSERT INTO sal_emp VALUES ('Carol', ARRAY[21000, 22000, 23000, 24000]);
INSERT 0 1
MyTest=# SELECT * FROM sal_emp;
name | pay_by_quarter
--------+---------------------------
Bill | {11000,12000,13000,14000}
Carol | {21000,22000,23000,24000}
(2 rows)
2. 訪問(wèn)數(shù)組：
和其他語(yǔ)言一樣，PostgreSQL中數(shù)組也是通過(guò)下標(biāo)數(shù)字(寫在方括弧內(nèi))的方式進(jìn)行訪問(wèn)，只是PostgreSQL中數(shù)組元素的下標(biāo)是從1開始n結(jié)束。
MyTest=# SELECT pay_by_quarter[3] FROM sal_emp;
pay_by_quarter
----------------
13000
23000
(2 rows)
MyTest=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
------
Bill
Carol
(2 rows)
PostgreSQL中還提供了訪問(wèn)數(shù)組范圍的功能，即ARRAY[腳標(biāo)下界:腳標(biāo)上界]。
MyTest=# SELECT name,pay_by_quarter[1:3] FROM sal_emp;
name | pay_by_quarter
--------+---------------------
Bill | {11000,12000,13000}
Carol | {21000,22000,23000}
(2 rows)
3. 修改數(shù)組：
1). 代替全部數(shù)組值：
--UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; 也可以。
MyTest=# UPDATE sal_emp SET pay_by_quarter = '{31000,32000,33000,34000}' WHERE name = 'Carol';
UPDATE 1
MyTest=# SELECT * FROM sal_emp;
name | pay_by_quarter
--------+---------------------------
Bill | {11000,12000,13000,14000}
Carol | {31000,32000,33000,34000}
(2 rows)
2). 更新數(shù)組中某一元素：
MyTest=# UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill';
UPDATE 1
MyTest=# SELECT * FROM sal_emp;
name | pay_by_quarter
--------+---------------------------
Carol | {31000,32000,33000,34000}
Bill | {11000,12000,13000,15000}
(2 rows)
3). 更新數(shù)組某一范圍的元素：
MyTest=# UPDATE sal_emp SET pay_by_quarter[1:2] = '{37000,37000}' WHERE name = 'Carol';
UPDATE 1
MyTest=# SELECT * FROM sal_emp;
name | pay_by_quarter
--------+---------------------------
Bill | {11000,12000,13000,15000}
Carol | {37000,37000,33000,34000}
(2 rows)
4). 直接賦值擴(kuò)大數(shù)組：
MyTest=# UPDATE sal_emp SET pay_by_quarter[5] = 45000 WHERE name = 'Bill';
UPDATE 1
MyTest=# SELECT * FROM sal_emp;
name | pay_by_quarter
--------+---------------------------------
Carol | {37000,37000,33000,34000}
Bill | {11000,12000,13000,15000,45000}
(2 rows)
4. 在數(shù)組中檢索：
1). 最簡(jiǎn)單直接的方法：
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;
2). 更加有效的方法：
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); --數(shù)組元素中有任何一個(gè)等于10000，where條件將成立。
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); --只有當(dāng)數(shù)組中所有的元素都等于10000時(shí)，where條件才成立。
七、復(fù)合類型：
PostgreSQL中復(fù)合類型有些類似于C語(yǔ)言中的結(jié)構(gòu)體，也可以被視為Oracle中的記錄類型，但是還是感覺復(fù)合類型這個(gè)命名比較貼切。它實(shí)際上只是一個(gè)字段名和它們的數(shù)據(jù)類型的列表。PostgreSQL允許像簡(jiǎn)單數(shù)據(jù)類型那樣使用復(fù)合類型。比如，表字段可以聲明為一個(gè)復(fù)合類型。
1. 聲明復(fù)合類型:
下面是兩個(gè)簡(jiǎn)單的聲明示例：
CREATE TYPE complex AS (
r double,
i double
);
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
和聲明一個(gè)數(shù)據(jù)表相比，聲明類型時(shí)需要加AS關(guān)鍵字，同時(shí)在聲明TYPE時(shí)不能定義任何約束。下面我們看一下如何在表中指定復(fù)合類型的字段，如：
CREATE TABLE on_hand (
item inventory_item,
count integer
);
最后需要指出的是，在創(chuàng)建表的時(shí)候，PostgreSQL也會(huì)自動(dòng)創(chuàng)建一個(gè)與該表對(duì)應(yīng)的復(fù)合類型，名字與表字相同，即表示該表的復(fù)合類型。
2. 復(fù)合類型值輸入：
我們可以使用文本常量的方式表示復(fù)合類型值，即在圓括號(hào)里包圍字段值并且用逗號(hào)分隔它們。你也可以將任何字段值用雙引號(hào)括起，如果值本身包含逗號(hào)或者圓括號(hào)，那么就用雙引號(hào)括起，對(duì)于上面的inventory_item復(fù)合類型的輸入如下：
'("fuzzy dice",42,1.99)'
如果希望類型中的某個(gè)字段為NULL，只需在其對(duì)應(yīng)的位置不予輸入即可，如下面的輸入中price字段的值為NULL，
'("fuzzy dice",42,)'
如果只是需要一個(gè)空字串，而非NULL，寫一對(duì)雙引號(hào)，如：
'("",42,)'
在更多的場(chǎng)合中PostgreSQL推薦使用ROW表達(dá)式來(lái)構(gòu)建復(fù)合類型值，使用該種方式相對(duì)簡(jiǎn)單，無(wú)需考慮更多標(biāo)識(shí)字符問(wèn)題，如：
ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)
注：對(duì)于ROW表達(dá)式，如果里面的字段數(shù)量超過(guò)1個(gè)，那么關(guān)鍵字ROW就可以省略，因此以上形式可以簡(jiǎn)化為：
('fuzzy dice', 42, 1.99)
('', 42, NULL)
3. 訪問(wèn)復(fù)合類型：
訪問(wèn)復(fù)合類型中的字段和訪問(wèn)數(shù)據(jù)表中的字段在形式上極為相似，只是為了對(duì)二者加以區(qū)分，PostgreSQL設(shè)定在訪問(wèn)復(fù)合類型中的字段時(shí)，類型部分需要用圓括號(hào)括起，以避免混淆，如：
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
如果在查詢中也需要用到表名，那么表名和類型名都需要被圓括號(hào)括起，如：
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
4. 修改復(fù)合類型：
見如下幾個(gè)示例：
--直接插入復(fù)合類型的數(shù)據(jù)，這里是通過(guò)ROW表達(dá)式來(lái)完成的。
INSERT INTO on_hand(item) VALUES(ROW("fuzzy dice",42,1.99));
--在更新操作中，也是可以通過(guò)ROW表達(dá)式來(lái)完成。
UPDATE on_hand SET item = ROW("fuzzy dice",42,1.99) WHERE count = 0;
--在更新復(fù)合類型中的一個(gè)字段時(shí)，我們不能在SET后面出現(xiàn)的字段名周圍加圓括號(hào)，
--但是在等號(hào)右邊的表達(dá)式里引用同一個(gè)字段時(shí)卻需要圓括號(hào)。
UPDATE on_hand SET item.price = (item).price + 1 WHERE count = 0;
--可以在插入中，直接插入復(fù)合類型中字段。
INSERT INTO on_hand (item.supplier_id, item.price) VALUES(100, 2.2);
該篇博客是對(duì)PostgreSQL官方文檔中“數(shù)據(jù)類型”章節(jié)的簡(jiǎn)單歸納，這里之所以用一篇獨(dú)立的博客來(lái)專門介紹，不僅是為了系統(tǒng)學(xué)習(xí)，也便于今后需要時(shí)的快速查閱。
相關(guān)文章
postgresql insert into select無(wú)法使用并行查詢的解決
這篇文章主要介紹了postgresql insert into select無(wú)法使用并行查詢的解決，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01
這篇文章主要介紹了PostgreSQL LIKE 大小寫實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。 一起跟隨小編過(guò)來(lái)看看吧2021-01-01
CentOS中運(yùn)行PostgreSQL需要修改的內(nèi)核參數(shù)及配置腳本分享
這篇文章主要介紹了CentOS中運(yùn)行PostgreSQL需要修改的內(nèi)核參數(shù)及配置腳本分享,本文從系統(tǒng)資源限制類和內(nèi)存參數(shù)優(yōu)化類來(lái)進(jìn)行說(shuō)明,需要的朋友可以參考下2014-07-07
在docker上部署postgreSQL主從的超詳細(xì)步驟
使用Docker能夠更加高效地部署和管理應(yīng)用程序,提高開發(fā)和運(yùn)維的效率,下面這篇文章主要給大家介紹了關(guān)于在docker上部署postgreSQL主從的超詳細(xì)步驟,文中通過(guò)代碼及圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-08-08
PostgreSQL游標(biāo)與索引選擇實(shí)例詳細(xì)介紹
這篇文章主要介紹了PostgreSQL游標(biāo)與索引選擇優(yōu)化案例，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧2022-09-09
在postgresql中結(jié)束掉正在執(zhí)行的SQL語(yǔ)句操作
這篇文章主要介紹了在postgresql中結(jié)束掉正在執(zhí)行的SQL語(yǔ)句操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
postgresql如何查詢重復(fù)計(jì)數(shù)及去重查詢
這篇文章主要介紹了postgresql如何查詢重復(fù)計(jì)數(shù)及去重查詢問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-11-11
今天我們來(lái)看在Mac OS上安裝PostgreSQL的教程,這里我們通過(guò)brew包管理器來(lái)安裝,所以首先我們會(huì)講解brew的安裝配置:2016-06-06
淺談PostgreSQL 11 新特性之默認(rèn)分區(qū)
這篇文章主要介紹了淺談PostgreSQL 11 新特性之默認(rèn)分區(qū)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
這篇文章主要介紹了PostgreSQL11修改wal-segsize的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01