常用數(shù)據(jù)類型

1、一、數(shù)值類型:下面是PostgreSQL所支持的數(shù)值類型的列表和簡單說明:名字存儲空間描述范圍smallint2字節(jié)小范圍整數(shù)-32768到+32767integer4字節(jié)常用的整數(shù)-2147483648到+2147483647bigint8字節(jié)大范圍的整數(shù)-9223372036854775808到9223372036854775807decimal變長用戶聲明精度，精確無限制numeric變長用戶聲明精度，精確無限制real4字節(jié)變精度，不精確6位十進制數(shù)字精度double8字節(jié)變精度，不精確15位十進制數(shù)字精度serial4字節(jié)自增整數(shù)1到+2147483647bigserial8字節(jié)大范

2、圍的自增整數(shù)1到92233720368547758071. 整數(shù)類型:類型smallint、integer和bigint存儲各種范圍的全部是數(shù)字的數(shù)，也就是沒有小數(shù)部分的數(shù)字。試圖存儲超出范圍以外的數(shù)值將導(dǎo)致一個錯誤。常用的類型是integer,因為它提供了在范圍、存儲空間和性能之間的最佳平衡。一般只有在磁盤空間緊張的時候才使用smallint。而只有在integer的范圍不夠的時候才使用bigint，因為前者(integer)絕對快得多。2任意精度數(shù)值：類型numeric可以存儲最多1000位精度的數(shù)字并且準(zhǔn)確地進行計算。因此非常適合用于貨幣金額和其它要求計算準(zhǔn)確的數(shù)量。不過，numeric

3、類型上的算術(shù)運算比整數(shù)類型或者浮點數(shù)類型要慢很多。numeric字段的最大精度和最大比例都是可以配置的。要聲明一個類型為numeric的字段，你可以用下面的語法：NUMERIC(precision,scale)比如數(shù)字23.5141的精度為6,而刻度為4。在目前的PostgreSQL版本中，decimal和numeric是等效的。3. 浮點數(shù)類型：數(shù)據(jù)類型real和double是不準(zhǔn)確的、犧牲精度的數(shù)字類型。不準(zhǔn)確意味著一些數(shù)值不能準(zhǔn)確地轉(zhuǎn)換成內(nèi)部格式并且是以近似的形式存儲的，因此存儲后再把數(shù)據(jù)打印出來可能顯示一些缺失。4. Serial(序號)類型：serial和bigserial類型不是真

4、正的類型，只是為在表中設(shè)置唯一標(biāo)識做的概念上的便利。CREATETABLEtablename(colnameSERIAL);等價于CREATESEQUENCEtablename_colname_seq;CREATETABLEtablename(colnameintegerDEFAULTnextval('tablename_colname_seq')NOTNULL);這樣，我們就創(chuàng)建了一個整數(shù)字段并且把它的缺省數(shù)值安排為從一個序列發(fā)生器取值。應(yīng)用了一個NOTNULL約束以確?？罩挡粫徊迦?。在大多數(shù)情況下你可能還希望附加一個UNIQUE或者PRIMARYKEY約束避免意外地插入重

5、復(fù)的數(shù)值，但這個不是自動發(fā)生的。因此，如果你希望一個序列字段有一個唯一約束或者一個主鍵，那么你現(xiàn)在必須聲明，就像其它數(shù)據(jù)類型一樣。除的。因此，如果你想用同一個序列發(fā)生器同時給幾個字段提供數(shù)據(jù)，那么就應(yīng)該以獨立對象的方式創(chuàng)建該序列發(fā)生器。二、字符類型：下面是PostgreSQL所支持的字符類型的列表和簡單說明：名字描述varchar(n)變長，有長度限制char(n)定長，不足補空白text變長，無長度限制SQL定義了兩種基本的字符類型，varchar(n)和char(n)，這里的n是一個正整數(shù)。兩種類型都可以存儲最多n個字符長的字串，試圖存儲更長的字串到這些類型的字段里會產(chǎn)生一個錯誤，除非超出

6、長度的字符都是空白，這種情況下該字串將被截斷為最大長度。如果沒有長度聲明，char等于char(1),而varchar則可以接受任何長度的字串。MyTest=>CREATETABLEtesttable(first_colvarchar(2);CREATETABLEMyTest=>INSERTINTOtesttableVALUES('333');-插入字符串的長度，超過其字段定義的長度，因此報錯。ERROR:valuetoolongfortypecharactervarying(2)-插入字符串中，超出字段定義長度的部分是空格，因此可以插入，但是空白符被截斷。MyTe

7、st=>INSERTINTOtesttableVALUES('33');INSERT01MyTest=>SELECT*FROMtesttable;first_col33(1row)這里需要注意的是，如果是將數(shù)值轉(zhuǎn)換成char(n)或者varchar(n),那么超長的數(shù)值將被截斷成n個字符，而不會拋出錯誤。MyTest=>select1234:varchar(2);varchar12(1row)最后需要提示的是，這三種類型之間沒有性能差別，只不過是在使用char類型時增加了存儲尺寸。雖然在某些其它的數(shù)據(jù)庫系統(tǒng)里,char(n)有一定的性能優(yōu)勢，但在Postgre

8、SQL里沒有。在大多數(shù)情況下，應(yīng)該使用text或者varchar。三、日期/時間類型：下面是PostgreSQL所支持的日期/時間類型的列表和簡單說明:名字存儲空間描述最低值最高值分辨率timestamp無時區(qū)8字節(jié)包括日期和時間4713BC5874897AD1毫秒/14位timestamp含時區(qū)8字節(jié)日期和時間，帶時區(qū)4713BC5874897AD1毫秒/14位interval12字節(jié)時間間隔-178000000年178000000年1毫秒/14位date4字節(jié)只用于日期4713BC32767AD1天time無時區(qū)8字節(jié)只用于日內(nèi)時間00:00:0024:00:001毫秒/14位1.日期/時

9、間輸入：任何日期或者時間的文本輸入均需要由單引號包圍，就象一個文本字符串一樣。1).日期：以下為合法的日期格式列表：例子描述January8,1999在任何datestyle輸入模式下都無歧義1999-01-08ISO-8601格式，任何方式下都是1999年1月8號，(建議格式)1/8/1999歧義，在MDY下是1月8號；在DMY模式下讀做8月1日1/18/1999在MDY模式下讀做1月18日，其它模式下被拒絕01/02/03MDY模式下的2003年1月2日；DMY模式下的2003年2月1日；YMD模式下的2001年2月3日1999-Jan-08任何模式下都是1月8日J(rèn)an-08-1999任何

10、模式下都是1月8日08-Jan-1999任何模式下都是1月8日99-Jan-08在YMD模式下是1月8日，否則錯誤08-Jan-991月8日，除了在YMD模式下是錯誤的之外Jan-08-991月8日，除了在YMD模式下是錯誤的之外19990108ISO-8601;任何模式下都是1999年1月8日990108ISO-8601;任何模式下都是1999年1月8日2).時間：以下為合法的時間格式列表:例子描述04:05:06.789ISO860104:05:06ISO860104:05ISO8601040506ISO860104:05AM與04:05樣；AM不影響數(shù)值04:05PM與16:05樣；輸入

11、小時數(shù)必須=1204:05:06.789-8ISO860104:05:06-08:00ISO860104:05-08:00ISO8601040506-08ISO86013).時間戳：時間戳類型的有效輸入由一個日期和時間的聯(lián)接組成，后面跟著一個可選的時區(qū)。因此，1999-01-0804:05:06和1999-01-0804:05:06-8:00都是有效的數(shù)值。2.示例：1).在插入數(shù)據(jù)之前先查看datestyle系統(tǒng)變量的值:MyTest=>showdatestyle;DateStyleISO,YMD(1row)2) .創(chuàng)建包含日期、時間和時間戳類型的示例表：MyTest=>CREA

12、TETABLEtesttable(idinteger,date_coldate,time_coltime,timestamp_coltimestamp);CREATETABLE3) .插入數(shù)據(jù)：MyTest=>INSERTINTOtesttable(id,date_col)VALUES(1,DATE'01/02/03');-datestyle為YMDINSERT01MyTest=>SELECTid,date_colFROMtesttable;id|date_col1 |2001-02-03(1row)MyTest=>setdatestyle=MDY;SETMy

13、Test=>INSERTINTOtesttable(id,date_col)VALUES(2,DATE'01/02/03');-datestyle為MDYINSERT01MyTest=>SELECTid,date_colFROMtesttable;id|date_col1 |2001-02-032 |2003-01-02MyTest=>INSERTINTOtesttable(id,time_col)VALUES(3,TIME'10:20:00)-插入時間。INSERT01MyTest=>SELECTid,time_colFROMtesttable

14、WHEREtime_colISNOTNULL;id|time_col3 |10:20:00(1row)MyTest=>INSERTINTOtesttable(id,timestamp_col)VALUES(4,DATE'01/02/03');INSERT01MyTest=>INSERTINTOtesttable(id,timestamp_col)VALUES(5,TIMESTAMP'01/02/0310:20:00');INSERT01MyTest=>SELECTid,timestamp_colFROMtesttableWHEREtimesta

15、mp_colISNOTNULL;id|timestamp_col+4 |2003-01-0200:00:005 |2003-01-0210:20:00(2rows)四、布爾類型:PostgreSQL支持標(biāo)準(zhǔn)的SQLboolean數(shù)據(jù)類型。boolean只能有兩個狀態(tài)之一：真(True)或假(False)。該類型占用1個字節(jié)。"真"值的有效文本值是：TRUE't''true''y''yes''1'而對于"假"而言，你可以使用下面這些：FALSE'false'&#

16、39;n''no''0'見如下使用方式：MyTest=>CREATETABLEtesttable(aboolean,btext);CREATETABLEMyTest=>INSERTINTOtesttableVALUES(TRUE,'sicest');INSERT01MyTest=>INSERTINTOtesttableVALUES(FALSE,'nonest');INSERT01MyTest=>SELECT*FROMtesttable;a|bt|sicestf|nonest(2rows)MyTest

17、=>SELECT*FROMtesttableWHEREa;a|bt|sicest(1row)MyTest=>SELECT*FROMtesttableWHEREa=true;a|bt|sicest(1row)五、位串類型：位串就是一串1和0的字串。它們可以用于存儲和視覺化位掩碼。我們有兩種類型的SQL位類型：bit(n)和bitvarying(n);這里的n是一個正整數(shù)。bit類型的數(shù)據(jù)必須準(zhǔn)確匹配長度n;試圖存儲短些或者長一些的數(shù)據(jù)都是錯誤的。類型bitvarying數(shù)據(jù)是最長n的變長類型；更長的串會被拒絕。寫一個沒有長度的bit等效于bit(1)，沒有長度的bitvarying相

18、當(dāng)于沒有長度限制。針對該類型，最后需要提醒的是，如果我們明確地把一個位串值轉(zhuǎn)換成bit(n)，那么它的右邊將被截斷或者在右邊補齊零，直到剛好n位，而不會拋出任何錯誤。類似地，如果我們明確地把一個位串?dāng)?shù)值轉(zhuǎn)換成bitvarying(n)，如果它超過n位，那么它的右邊將被截斷。見如下具體使用方式：MyTest=>CREATETABLEtesttable(abit,bbitvarying(5);CREATETABLEMyTest=>INSERTINTOtesttableVALUES(B'101',B'00');INSERT01MyTest=>INSE

19、RTINTOtesttableVALUES(B'10',B'101');ERROR:bitstringlength2doesnotmatchtypebit(3)MyTest=>INSERTINTOtesttableVALUES(B'10':bit(3),B'101');INSERT01MyTest=>SELECT*FROMtesttable;a|b101|00100|101(2rows)MyTest=>SELECTB'11':bit(3);bit110(1row)六、數(shù)組：1. 數(shù)組類型聲明：1)

20、 .創(chuàng)建字段含有數(shù)組類型的表。CREATETABLEsal_emp(nametext,pay_by_quarterinteger-還可以定義為integer£4_7或integerARRAY4);2) .插入數(shù)組數(shù)據(jù)：MyTest=#INSERTINTOsal_empVALUES('Bill','11000,12000,13000,14000');INSERT01MyTest=#INSERTINTOsal_empVALUES(Carol：ARRAY21000,22000,23000,24000);INSERT01MyTest=#SELECT*FROMs

21、al_emp;name|pay_by_quarter+Bill|11000,12000,13000,14000Carol|21000,22000,23000,24000(2rows)2. 訪問數(shù)組：和其他語言一樣，PostgreSQL中數(shù)組也是通過下標(biāo)數(shù)字(寫在方括弧內(nèi))的方式進行訪問，只是PostgreSQL中數(shù)組元素的下標(biāo)是從1開始n結(jié)束。MyTest=#SELECTpay_by_quarter3FROMsal_emp;pay_by_quarter1300023000(2rows)MyTest=#SELECTnameFROMsal_empWHEREpay_by_quarter1<&g

22、t;pay_by_quarter2;nameBillCarol(2rows)PostgreSQL中還提供了訪問數(shù)組范圍的功能，即ARRAY腳標(biāo)下界:腳標(biāo)上界。MyTest=#SELECTname,pay_by_quarter1:3FROMsal_emp;name|pay_by_quarter+Bill|11000,12000,13000Carol|21000,22000,23000(2rows)3.修改數(shù)組：1) .代替全部數(shù)組值：-UPDATEsal_empSETpay_by_quarter=ARRAY25000,25000,27000,27000WHEREname='Carol&#

23、39;也可以。MyTest=#UPDATEsal_empSETpay_by_quarter='31000,32000,33000,34000'WHEREname='Carol'UPDATE1MyTest=#SELECT*FROMsal_emp;name|pay_by_quarter+Bill|11000,12000,13000,14000Carol|31000,32000,33000,34000(2rows)2) .更新數(shù)組中某一元素：MyTest=#UPDATEsal_empSETpay_by_quarter4=15000WHEREname='Bill

24、'UPDATE1MyTest=#SELECT*FROMsal_emp;name|pay_by_quarter+Carol|31000,32000,33000,34000Bill|11000,12000,13000,15000(2rows)3) .更新數(shù)組某一范圍的元素：MyTest=#UPDATEsal_empSETpay_by_quarter1:2='37000,37000'WHEREname='Carol'UPDATE1MyTest=#SELECT*FROMsal_emp;name|pay_by_quarter+Bill|11000,12000,13

25、000,15000Carol|37000,37000,33000,34000(2rows)4) .直接賦值擴大數(shù)組：MyTest=#UPDATEsal_empSETpay_by_quarter5=45000WHEREname='Bill'UPDATE1MyTest=#SELECT*FROMsa_emp;name|pay_by_quarter+Carol|37000,37000,33000,34000Bill|11000,12000,13000,15000,45000(2rows)4. 在數(shù)組中檢索：1) .最簡單直接的方法：SELECT*FROMsal_empWHEREpay_

26、by_quarter1=10000ORpay_by_quarter2=10000ORpay_by_quarter3=10000ORpay_by_quarter4=10000;2) .更加有效的方法：SELECT*FROMsal_empWHERE10000=ANY(pay_by_quarter);-數(shù)組元素中有任何一個等于10000,where條件將成立。SELECT*FROMsal_empWHERE10000=ALL(pay_by_quarter);-只有當(dāng)數(shù)組中所有的元素都等于0000時，where條件才成立。七、復(fù)合類型：PostgreSQL中復(fù)合類型有些類似于C語言中的結(jié)構(gòu)體，也可以被視

27、為Oracle中的記錄類型，但是還是感覺復(fù)合類型這個命名比較貼切。它實際上只是一個字段名和它們的數(shù)據(jù)類型的列表。PostgreSQL允許像簡單數(shù)據(jù)類型那樣使用復(fù)合類型。比如，表字段可以聲明為一個復(fù)合類型。1.聲明復(fù)合類型：下面是兩個簡單的聲明示例：CREATETYPEcomplexAS(rdouble,idouble);CREATETYPEinventory_itemAS(nametext,supplier_idinteger,pricenumeric);和聲明一個數(shù)據(jù)表相比，聲明類型時需要加AS關(guān)鍵字，同時在聲明TYPE時不能定義任何約束。下面我們看一下如何在表中指定復(fù)合類型的字段，如：CR

28、EATETABLEon_hand(iteminventory_item,countinteger);最后需要指出的是，在創(chuàng)建表的時候，PostgreSQL也會自動創(chuàng)建一個與該表對應(yīng)的復(fù)合類型，名字與表字相同，即表示該表的復(fù)合類型。2. 復(fù)合類型值輸入：我們可以使用文本常量的方式表示復(fù)合類型值，即在圓括號里包圍字段值并且用逗號分隔它們。你也可以將任何字段值用雙引號括起，如果值本身包含逗號或者圓括號，那么就用雙引號括起，對于上面的inventory_item復(fù)合類型的輸入如下：'("fuzzydice",42,1.99)'如果希望類型中的某個字段為NULL,只需在其對應(yīng)的位置不予輸入即可，如下面的輸入中price字段的值為NULL,'("fuzzydice",42,)'如果只是需要一個空字串，而非NULL，寫一對雙引號，如：'("",42,)'