PostgreSQL学习手册(常用数据类型)

一、数值类型：

    下面是PostgreSQL所支持的数值类型的列表和简单说明：

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

    该篇博客是对PostgreSQL官方文档中“数据类型”章节的简单归纳，这里之所以用一篇独立的博客来专门介绍，不仅是为了系统学习，也便于今后需要时的快速查阅。