PostgreSQL學習手冊(常用資料型別)

一、數值型別：

    下面是PostgreSQL所支援的數值型別的列表和簡單說明：

名字	儲存空間	描述	範圍
smallint	2 位元組	小範圍整數	-32768 到 +32767
integer	4 位元組	常用的整數	-2147483648 到 +2147483647
bigint	8 位元組	大範圍的整數	-9223372036854775808 到 9223372036854775807
decimal	變長	使用者宣告精度，精確	無限制
numeric	變長	使用者宣告精度，精確	無限制
real	4 位元組	變精度，不精確	6 位十進位制數字精度
double	8 位元組	變精度，不精確	15 位十進位制數字精度
serial	4 位元組	自增整數	1 到 +2147483647
bigserial	8 位元組	大範圍的自增整數	1 到 9223372036854775807

   1. 整數型別：
    型別smallint、integer和bigint儲存各種範圍的全部是數字的數，也就是沒有小數部分的數字。試圖儲存超出範圍以外的數值將導致一個 錯誤。常用的型別是integer，因為它提供了在範圍、儲存空間和效能之間的最佳平衡。一般只有在磁碟空間緊張的時候才使用smallint。而只有在 integer的範圍不夠的時候才使用bigint，因為前者(integer)絕對快得多。

    2. 任意精度數值：
    型別numeric可以儲存最多1000位精度的數字並且準確地進行計算。因此非常適合用於貨幣金額和其它要求計算準確的數量。不過，numeric型別上的算術運算比整數型別或者浮點數型別要慢很多。
    numeric欄位的最大精度和最大比例都是可以配置的。要宣告一個型別為numeric的欄位，你可以用下面的語法：
    NUMERIC(precision,scale)
    比如數字23.5141的精度為6，而刻度為4。
    在目前的PostgreSQL版本中，decimal和numeric是等效的。
   
    3. 浮點數型別：
    資料型別real和double是不準確的、犧牲精度的數字型別。不準確意味著一些數值不能準確地轉換成內部格式並且是以近似的形式儲存的，因此儲存後再把資料列印出來可能顯示一些缺失。
    
   4. Serial(序號)型別：
    serial和bigserial型別不是真正的型別，只是為在表中設定唯一標識做的概念上的便利。
    CREATE TABLE tablename (
        colname SERIAL
    );
    等價於
    CREATE SEQUENCE tablename_colname_seq;
    CREATE TABLE tablename(
        colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
    );
    這樣，我們就建立了一個整數字段並且把它的預設數值安排為從一個序列發生器取值。應用了一個NOT NULL約束以確保空值不會被插入。在大多數情況下你可能還希望附加一個UNIQUE或者PRIMARY KEY約束避免意外地插入重複的數值，但這個不是自動發生的。因此，如果你希望一個序列欄位有一個唯一約束或者一個主鍵，那麼你現在必須宣告，就像其它數 據型別一樣。
    還需要另外說明的是，一個serial型別建立的序列在其所屬欄位被刪除時，該序列也將被自動刪除，但是其它情況下是不會被刪除的。因此，如果你想用同一個序列發生器同時給幾個欄位提供資料，那麼就應該以獨立物件的方式建立該序列發生器。

二、字元型別：

    下面是PostgreSQL所支援的字元型別的列表和簡單說明：

名字	描述
varchar(n)	變長，有長度限制
char(n)	定長,不足補空白
text	變長，無長度限制

    SQL 定義了兩種基本的字元型別，varchar(n)和char(n)，這裡的n是一個正整數。兩種型別都可以儲存最多n個字元長的字串，試圖儲存更長的字串 到這些型別的欄位裡會產生一個錯誤，除非超出長度的字元都是空白，這種情況下該字串將被截斷為最大長度。如果沒有長度宣告，char等於char(1)， 而varchar則可以接受任何長度的字串。
    MyTest=> CREATE TABLE testtable(first_col varchar(2));
    CREATE TABLE
    MyTest=> INSERT INTO testtable VALUES('333');   --插入字串的長度，超過其欄位定義的長度，因此報錯。
    ERROR:  value too long for type character varying(2)
    --插入字串中，超出欄位定義長度的部分是空格，因此可以插入，但是空白符被截斷。
    MyTest=> INSERT INTO testtable VALUES('33 ');   
    INSERT 0 1
    MyTest=> SELECT * FROM testtable;
     first_col
    -----------
     33
    (1 row)
    這裡需要注意的是，如果是將數值轉換成char(n)或者varchar(n)，那麼超長的數值將被截斷成n個字元，而不會丟擲錯誤。
    MyTest=> select 1234::varchar(2);
     varchar
    ---------
     12
    (1 row)
    最後需要提示的是，這三種型別之間沒有效能差別，只不過是在使用char型別時增加了儲存尺寸。雖然在某些其它的資料庫系統裡，char(n)有一定的效能優勢，但在PostgreSQL裡沒有。在大多數情況下，應該使用text或者varchar。
   
三、日期/時間型別：

    下面是PostgreSQL所支援的日期/時間型別的列表和簡單說明：

名字	儲存空間	描述	最低值	最高值	解析度
timestamp[無時區]	8位元組	包括日期和時間	4713 BC	5874897AD	1毫秒/14位
timestamp[含時區]	8位元組	日期和時間，帶時區	4713 BC	5874897AD	1毫秒/14位
interval	12位元組	時間間隔	-178000000年	178000000年	1毫秒/14位
date	4位元組	只用於日期	4713 BC	32767AD	1天
time[無時區]	8位元組	只用於一日內時間	00:00:00	24:00:00	1毫秒/14位

    1. 日期/時間輸入:
    任何日期或者時間的文字輸入均需要由單引號包圍，就象一個文字字串一樣。
    1). 日期:
    以下為合法的日期格式列表：

例子	描述
January 8, 1999	在任何datestyle輸入模式下都無歧義
1999-01-08	ISO-8601格式，任何方式下都是1999年1月8號，(建議格式)
1/8/1999	歧義，在MDY下是1月8號；在 DMY模式下讀做8月1日
1/18/1999	在MDY模式下讀做1月18日，其它模式下被拒絕
01/02/03	MDY模式下的2003年1月2日；DMY模式下的2003年2月1日；YMD 模式下的2001年2月3日
1999-Jan-08	任何模式下都是1月8日
Jan-08-1999	任何模式下都是1月8日
08-Jan-1999	任何模式下都是1月8日
99-Jan-08	在YMD模式下是1月8日，否則錯誤
08-Jan-99	1月8日，除了在YMD模式下是錯誤的之外
Jan-08-99	1月8日，除了在YMD模式下是錯誤的之外
19990108	ISO-8601; 任何模式下都是1999年1月8日
990108	ISO-8601; 任何模式下都是1999年1月8日

    2). 時間：
    以下為合法的時間格式列表：

例子	描述
04:05:06.789	ISO 8601
04:05:06	ISO 8601
04:05	ISO 8601
040506	ISO 8601
04:05 AM	與04:05一樣；AM不影響數值
04:05 PM	與16:05一樣；輸入小時數必須 <= 12
04:05:06.789-8	ISO 8601
04:05:06-08:00	ISO 8601
04:05-08:00	ISO 8601
040506-08	ISO 8601

    3). 時間戳：
    時間戳型別的有效輸入由一個日期和時間的聯接組成，後面跟著一個可選的時區。因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的數值。
  
   2. 示例：
    1). 在插入資料之前先檢視datestyle系統變數的值：
    MyTest=> show datestyle;
     DateStyle
    -----------
     ISO, YMD
    (1 row)
    2). 建立包含日期、時間和時間戳型別的示例表：
    MyTest=> CREATE TABLE testtable (id integer, date_col date, time_col time, timestamp_col timestamp);
    CREATE TABLE
    3). 插入資料：
    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');  --插入時間。
    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支援標準的SQL boolean資料型別。boolean只能有兩個狀態之一：真(True)或 假(False)。該型別佔用1個位元組。
    "真"值的有效文字值是：
    TRUE
    't'
    'true'
    'y'
    'yes'
    '1'
    而對於"假"而言，你可以使用下面這些：
    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的字串。它們可以用於儲存和視覺化位掩碼。我們有兩種型別的SQL位型別：bit(n)和bit varying(n); 這裡的n是一個正整數。bit型別的資料必須準確匹配長度n; 試圖儲存短些或者長一些的資料都是錯誤的。型別bit varying資料是最長n的變長型別；更長的串會被拒絕。寫一個沒有長度的bit等效於bit(1)，沒有長度的bit varying相當於沒有長度限制。
    針對該型別，最後需要提醒的是，如果我們明確地把一個位串值轉換成bit(n)，那麼它的右邊將被截斷或者在右邊補齊零，直到剛好n位，而不會丟擲任何錯 誤。類似地，如果我們明確地把一個位串數值轉換成bit varying(n)，如果它超過n位，那麼它的右邊將被截斷。 見如下具體使用方式：    
    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)

六、陣列：

    1. 陣列型別宣告：
    1). 建立欄位含有陣列型別的表。
    CREATE TABLE sal_emp (
        name            text,
        pay_by_quarter  integer[] --還可以定義為integer[4]或integer ARRAY[4]
    );
    2). 插入陣列資料：
    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. 訪問陣列：
    和其他語言一樣，PostgreSQL中陣列也是通過下標數字(寫在方括弧內)的方式進行訪問，只是PostgreSQL中陣列元素的下標是從1開始n結束。
    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中還提供了訪問陣列範圍的功能，即ARRAY[腳標下界:腳標上界]。
    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. 修改陣列：
    1). 代替全部陣列值：
    --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). 更新陣列中某一元素：
    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). 更新陣列某一範圍的元素：
    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). 直接賦值擴大陣列：
    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. 在陣列中檢索：
    1). 最簡單直接的方法：
    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); --陣列元素中有任何一個等於10000，where條件將成立。
    SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); --只有當陣列中所有的元素都等於10000時，where條件才成立。

七、複合型別：

    PostgreSQL 中複合型別有些類似於C語言中的結構體，也可以被視為Oracle中的記錄型別，但是還是感覺複合型別這個命名比較貼切。它實際上只是一個欄位名和它們的 資料型別的列表。PostgreSQL允許像簡單資料型別那樣使用複合型別。比如，表欄位可以宣告為一個複合型別。
    1. 宣告覆合型別:
    下面是兩個簡單的宣告示例：
    CREATE TYPE complex AS (
        r double,
        i double
    );  
    CREATE TYPE inventory_item AS (
        name           text,
        supplier_id   integer,
        price            numeric
    );
    和宣告一個資料表相比，宣告型別時需要加AS關鍵字，同時在宣告TYPE時不能定義任何約束。下面我們看一下如何在表中指定複合型別的欄位，如：
    CREATE TABLE on_hand (
        item      inventory_item,
        count    integer
    );
    最後需要指出的是，在建立表的時候，PostgreSQL也會自動建立一個與該表對應的複合型別，名字與表字相同，即表示該表的複合型別。
   
    2. 複合型別值輸入：
    我們可以使用文字常量的方式表示複合型別值，即在圓括號裡包圍欄位值並且用逗號分隔它們。你也可以將任何欄位值用雙引號括起，如果值本身包含逗號或者圓括號，那麼就用雙引號括起，對於上面的inventory_item複合型別的輸入如下：
    '("fuzzy dice",42,1.99)'
    如果希望型別中的某個欄位為NULL，只需在其對應的位置不予輸入即可，如下面的輸入中price欄位的值為NULL，
    '("fuzzy dice",42,)'
    如果只是需要一個空字串，而非NULL，寫一對雙引號，如：
    '("",42,)'
    在更多的場合中PostgreSQL推薦使用ROW表示式來構建複合型別值，使用該種方式相對簡單，無需考慮更多標識字元問題，如：
    ROW('fuzzy dice', 42, 1.99)
    ROW('', 42, NULL)
    注：對於ROW表示式，如果裡面的欄位數量超過1個，那麼關鍵字ROW就可以省略，因此以上形式可以簡化為：
    ('fuzzy dice', 42, 1.99)
    ('', 42, NULL)
   
    3. 訪問複合型別：
    訪問複合型別中的欄位和訪問資料表中的欄位在形式上極為相似，只是為了對二者加以區分，PostgreSQL設定在訪問複合型別中的欄位時，型別部分需要用圓括號括起，以避免混淆，如：
    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;
   
    4. 修改複合型別：
    見如下幾個示例：
    --直接插入複合型別的資料，這裡是通過ROW表示式來完成的。
    INSERT INTO on_hand(item) VALUES(ROW("fuzzy dice",42,1.99));
    --在更新操作中，也是可以通過ROW表示式來完成。
    UPDATE on_hand SET item = ROW("fuzzy dice",42,1.99) WHERE count = 0;
    --在更新複合型別中的一個欄位時，我們不能在SET後面出現的欄位名周圍加圓括號，
    --但是在等號右邊的表示式裡引用同一個欄位時卻需要圓括號。
    UPDATE on_hand SET item.price = (item).price + 1 WHERE count = 0;
    --可以在插入中，直接插入複合型別中欄位。
    INSERT INTO on_hand (item.supplier_id, item.price) VALUES(100, 2.2);