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

1、名字存儲空間描述范圍smalli nt2字節(jié)小范圍整數(shù)-32768 到 +32767in teger4字節(jié)常用的整數(shù)-2147483648到 +2147483647bigi nt8字節(jié)大范圍的整數(shù)-9223372036854775808到 9223372036854775807decimal變長用戶聲明精度，精確無限制nu meric變長用戶聲明精度，精確無限制real4字節(jié)變精度，不精確6位十進制數(shù)字精度double8字節(jié)變精度，不精確15位十進制數(shù)字精度serial4字節(jié)自增整數(shù)1 到 +2147483647bigserial8字節(jié)大范圍的自增整數(shù)1 到 92233720368547758

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

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

4、在表中設(shè)置唯一標識做的概念上的便利。CREATE TABLE tablename (colname SERIAL);等價于CREATE SEQUENCE tablename_colname_seq;CREATE TABLE tablename(colname integer DEFAULT nextval ('tablename colname seq') NOT NULL);這樣，我們就創(chuàng)建了一個整數(shù)字段并且把它的缺省數(shù)值安排為從一個序列發(fā)生器取值。應(yīng)用了一個NOT NULL約束以確?？罩挡粫徊迦搿Ｔ诖蠖鄶?shù)情況下你可能還希望附加一個UNIQUE或者PRIMARY KEY約束

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=> CREATE TABLE testtable(first_col varchar(2);CREATE TABLEMyTest=> INSERT INTO testtable VALUES('333');-插入字符串的長度，超過其字段定義的長度，因此報錯。ERROR:value too long for type character varying(2)-插入字符串中，超岀字段定義長度的部分是空格，

7、因此可以插入，但是空白符被截斷。MyTest=> INSERT INTO testtable VALUES('33 ');INSERT 0 1MyTest=> SELECT * FROM testtable;first_col33(1 row)這里需要注意的是，如果是將數(shù)值轉(zhuǎn)換成char(n)或者varchar(n)，那么超長的數(shù)值將被截斷成n個字符，而不會拋出錯誤MyTest=> select 1234:varchar(2);varchar12(1 row)最后需要提示的是，這三種類型之間沒有性能差別，只不過是在使用char類型時增加了存儲尺寸。雖然在某些其

8、它的數(shù)據(jù)庫系統(tǒng)里，char(n)有一定的性能優(yōu)勢，但在PostgreSQL 里沒有。在大多數(shù)情況下，應(yīng)該使用text或者varchar 。三、日期/時間類型：下面是PostgreSQL 所支持的日期/時間類型的列表和簡單說明：名字存儲空間描述最低值最高值分辨率timestamp 無時區(qū)8字節(jié)包括日期和時間4713 BC5874897AD1毫秒/14位timestamp含時區(qū)8字節(jié)日期和時間，帶時區(qū)4713 BC5874897AD1毫秒/14位in terval12字節(jié)時間間隔-178000000年178000000年1毫秒/14位date4字節(jié)只用于日期4713 BC32767AD1天time

9、無時區(qū)8字節(jié)只用于一日內(nèi)時間00:00:0024:00:001毫秒/14位1.日期/時間輸入：任何日期或者時間的文本輸入均需要由單引號包圍，就象一個文本字符串一樣。1).日期：以下為合法的日期格式列表：例子描述Jan uary 8,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日；YM

10、D模式下的2001 年2月3日1999-Ja n-08任何模式下都是1月8日Jan-08-1999任何模式下都是1月8日08-Ja n-1999任何模式下都是1月8日99-Ja n-08在YMD模式下是1月8日，否則錯誤08-Ja n-991月8日，除了在 YMD模式下是錯誤的之外Jan-08-991月8日，除了在 YMD模式下是錯誤的之外19990108ISO-8601;任何模式下都是 1999年1月8日990108ISO-8601;任何模式下都是 1999年1月8日2) .時間：以下為合法的時間格式列表:例子描述04:05:06.789ISO 860104:05:06ISO 860104:

11、05ISO 8601040506ISO 860104:05 AM與04:05 一樣；AM不影響數(shù)值04:05 PM與16:05 一樣；輸入小時數(shù)必須<=1204:05:06.789-8ISO 860104:05:06-08:00ISO 860104:05-08:00ISO 8601040506-08ISO 86013).時間戳：時間戳類型的有效輸入由一個日期和時間的聯(lián)接組成，后面跟著一個可選的時區(qū)。因此，1999-01-08 04:05:06和 1999-01-08 04:05:06 -8:00都是有效的數(shù)值。MyTest=> show datestyleDateStyleISO,

12、 YMD(1 row)2) .創(chuàng)建包含日期、時間和時間戳類型的示例表：MyTest=> CREATE TABLE testtable (id integer, date_col date, time_col time, timestamp_col timestamp);CREATE TABLE3) .插入數(shù)據(jù)：MyTest=> INSERT INTO testtable(id,date_col) VALUES(1,DATE '01/02/03'); -datestyle 為 YMDINSERT 0 1MyTest=> SELECT id, date col F

13、ROM testtable;id | date_col+1 | 2001-02-03(1 row)MyTest=> set datestyle =MDY ;SETMyTest=> INSERT INTO testtable(id,date_col) VALUES(2,DATE '01/02/03'); -datestyle 為 MDYINSERT 0 1MyTest=> SELECT id,date col FROM testtable;id | date col+1 | 2001-02-032 | 2003-01-02MyTest=> INSERT I

14、NTO testtable(id,time col) VALUES(3,TIME '10:20:00');-插入時間INSERT 0 1MyTest=> SELECT id,time col FROM testtable WHERE time col IS NOT NULL;id | time col3 | 10:20:00(1 row)MyTest=> INSERT INTO testtable(id,timestamp col) VALUES(4,DATE '01/02/03');INSERT 0 1MyTest=> INSERT INTO

15、 testtable(id,timestamp_col) VALUES(5,TIMESTAMP '01/02/03 10:20:00');INSERT 0 1MyTest=> SELECT id,timestamp_col FROM testtable WHERE timestamp_col IS NOT NULL;id | timestamp_col+4 | 2003-01-02 00:00:005 | 2003-01-02 10:20:00(2 rows)四、布爾類型:PostgreSQL 支持標準的SQL boolean 數(shù)據(jù)類型。boolean 只能有兩個狀態(tài)之一

16、：真仃rue)或假(False)。該類型占用1個字 節(jié)。"真"值的有效文本值是：TRUE't''true'y'yes''1'而對于"假"而言，你可以使用下面這些：FALSE'f''false''n'| 'no''O'見如下使用方式：MyTest=> CREATE TABLE testtable (a boolean, b text);CREATE TABLEMyTest=> INSERT INTO t

17、esttable VALUES(TRUE, 'sic est');INSERT 0 1MyTest=> INSERT INTO testtable VALUES(FALSE, 'non est');INSERT 0 1MyTest=> SELECT * FROM testtable;a | bt | sic estf | non est(2 rows)MyTest=> SELECT * FROM testtable WHERE a;a | bt | sic est(1 row)MyTest=> SELECT * FROM testtabl

18、e WHERE a = true;a | bt | sic est(1 row)五、位串類型：位串就是一串1和0的字串。它們可以用于存儲和視覺化位掩碼。我們有兩種類型的SQL位類型：bit(n)和bit varying(n);這里的n是一個正整數(shù)。bit類型的數(shù)據(jù)必須準確匹配長度n;試圖存儲短些或者長一些的數(shù)據(jù)都是錯誤的。類型bit varying 數(shù)據(jù)MyTest=> CREATE TABLE testtable (a bit(3), b bit varying®);CREATE TABLEMyTest=> INSERT INTO testtable VALUES (B

19、'101',B'OO');INSERT 0 1MyTest=> 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 1MyTest=> SELECT * FROM testtable;a | b101

20、 | 00100|101(2 rows)MyTest=> SELECT B'11': bit (3);bit110(1 row)六、數(shù)組：1. 數(shù)組類型聲明：1) .創(chuàng)建字段含有數(shù)組類型的表。CREATE TABLE sal_emp (nametext,pay_by_quarter integer -還可以定義為 integer4 或 integer ARRAY4);2) .插入數(shù)組數(shù)據(jù)：MyTest=# INSERT INTO sal_emp VALUES ('Bill', '11000, 12000, 13000, 14000');IN

21、SERT 0 1MyTest=# INSERT INTO sal_emp VALUES ('Carol',ARRAY 21000, 22000, 23000, 24000);INSERT 0 1MyTest=# SELECT * FROM sal_emp;name | pay_by_quarter+Bill| 11000,12000,13000,14000Carol| 21000,22000,23000,24000(2 rows)2. 訪問數(shù)組：和其他語言一樣，PostgreSQL 中數(shù)組也是通過下標數(shù)字(寫在方括弧內(nèi))的方式進行訪問，只是 PostgreSQL 中數(shù)組元素的下

22、標 是從1開始n結(jié)束。MyTest=# SELECT pay_by_quarter3 FROM sal_emp;pay_by_quarter1300023000(2 rows)MyTest=# SELECT name FROM sal_emp WHERE pay_by_quarter1 <> pay_by_quarter2;nameBillCarol(2 rows)PostgreSQL 中還提供了訪問數(shù)組范圍的功能，即ARRAY腳標下界：腳標上界。MyTest=# SELECT name,pay_by_quarter1:3 FROM sal_emp;name | pay_by_qu

23、arter+Bill| 11000,12000,13000Carol| 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 1MyTest

24、=# SELECT * FROM sal_emp;name | pay_by_quarter+Bill| 11000,12000,13000,14000Carol| 31000,32000,33000,34000(2 rows)2) .更新數(shù)組中某一元素：MyTest=# UPDATE sal_emp SET pay_by_quarter4 = 15000 WHERE name = 'Bill'UPDATE 1MyTest=# SELECT * FROM sal_emp;name | pay_by_quarter+Carol| 31000,32000,33000,34000Bi

25、ll| 11000,12000,13000,15000(2 rows)3) .更新數(shù)組某一范圍的元素：MyTest=# UPDATE sal_emp SET pay_by_quarter1:2 = '37000,37000' WHERE name = 'Carol'UPDATE 1MyTest=# SELECT * FROM sal_emp;name | pay_by_quarter+Bill| 11000,12000,13000,15000Carol| 37000,37000,33000,34000(2 rows)4) .直接賦值擴大數(shù)組：MyTest=# U

26、PDATE sal_emp SET pay_by_quarter5 = 45000 WHERE name = 'Bill'UPDATE 1MyTest=# SELECT * FROM sal_emp;name |pay_by_quarter+Carol | 37000,37000,33000,34000Bill | 11000,12000,13000,15000,45000(2 rows)4. 在數(shù)組中檢索：1) .最簡單直接的方法：SELECT * FROM sal_emp WHERE pay_by_quarter1 = 10000 ORpay_by_quarter2 = 1

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

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

29、如：CREATE TABLE on_hand (iteminventory_itemcount integer）；最后需要指岀的是，在創(chuàng)建表的時候，PostgreSQL 也會自動創(chuàng)建一個與該表對應(yīng)的復(fù)合類型，名字與表字相同，即表示該表的復(fù)合類型。2. 復(fù)合類型值輸入：我們可以使用文本常量的方式表示復(fù)合類型值，即在圓括號里包圍字段值并且用逗號分隔它們。你也可以將任何字段值用雙引號'("fuzzy dice",42,1.99)'如果希望類型中的某個字段為NULL，只需在其對應(yīng)的位置不予輸入即可，如下面的輸入中price字段的值為NULL，'("fuzzy dice",42,)'如果只是需要一個空字串，而非NULL，寫一對雙引號，如：'("",42,)'在更多的場合中PostgreSQL推薦使用ROW表達式來構(gòu)建復(fù)合類型