PostgreSQL教程(四):数据类型详解


一、数值类型:

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

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所支持的字符类型的列表和简单说明:

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所支持的日期/时间类型的列表和简单说明:

1. 日期/时间输入:
    任何日期或者时间的文本输入均需要由单引号包围,就象一个文本字符串一样。
    1). 日期:
    以下为合法的日期格式列表:

2). 时间:
    以下为合法的时间格式列表:

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



相关阅读:
【Mac与iPhone互动】怎么在苹果Mac 10.10上接打电话?
win10系统检测未签名驱动程序方法教程
微软公布Win10 SDK Build 10586开发工具更新内容详情
Yii2框架dropDownList下拉菜单用法实例分析
jQuery.lazyload+masonry改良图片瀑布流代码
朋友网关于QQ相关的PHP代码(研究QQ的绝佳资料)
PHP下SSL加密解密、验证、签名方法(很简单)
php隐藏IP地址后两位显示为星号的方法
WIN10系统Microsoft Edge不能用内置管理员打开怎么办?
ThinkPHP中Common/common.php文件常用函数功能分析
21个值得收藏的Javascript技巧
Jquery全选与反选点击执行一次的解决方案
PHP中new static()与new self()的区别异同分析
汉字转拼音缩写示例代码(Silverlight和.NET 将汉字转换成为拼音)
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 CMS SQL jQuery C# C++ java Android IOS oracle MongoDB PostgreSQL SQLite 交通频道 G4722 G1875 G215 G569 G421 G6733 G7577 G8906 G1235 G4916 G7291 G1953 G245 G662 G1570 G6285 G719 G1836 G1346 G4781 G4908 G289 G6781 G9290 G7358 G1928 G1815 G325 G132 G4901 G6012 G6290 G7131 G5367 G184 G151 G5303 G1136 G6481 G7028 G575 G1744 G7660 G7693 G2344 G4937 G1234 G1814 G6252 G1492 G253 G2926 G883 G9275 G1231 G556 G241 G1306 G7646 G8103 G600 G1858 G9678 G6160 G7156 G825 G1125 G7249 G1809 G1350 G432 G9466 G7067 G785 G6404 G4663 G7008 G150 G823 G1514 G7529 G1201 G2353 G205 G7629 G9409 G6147 G677 G390 G8016 G9239 G456 G828 G8045 G491 G7145 G397 G7012 G1021 G6482 G2322 G7264 G1301 G9247 G96 G1294 G7133 G4824 G7005 G1653 G5307 G1213 G822 G4837 G1422 G411 G6227 G1571 G359 G1882 G6074 G7678 G21 G7077 G1272 G8918 G9645 G461 G1254 G1846 G8021 G7303 G1104 G76 G82 G621 G218 G8533 G2341 G8543 G555 G8013 G4802 G1364 G1153 G1342 G1861 G8905 G590 G4780 G668 G9261 G1304 G1638 G1395 G2914 G8003 G7158 G1833 G1873 G8128 G1856 G1841 G8709 G7346 G4612 G2103 G835 G8712 G381 G7240 G8932 G507 G29 G4054 G6273 G6752 G426 G211 G9473 G7119 G2333 G1567 G6153 G360 G4011 G5301 G7648 G8010 G8015 G6706 G614 G423 G8557 G9465 G72 G6018 G8901 G7030 G123

丹东 云霄 辽中 德阳 克拉玛依 惠山 招远 昭通 铁岭西 延吉西 军粮城北 定西 晋中 许昌东 郫县 诏安 七台河 高碑店东 南昌 延安 敦化 铜陵北 嵩明 鲘门 扬中 龙里北 舟山 洛阳 运城北 鞍山 西昌 邵阳北 绍兴 白山 三明 肇东 陵水 衡山西 嘉善 宜都 泰兴 泉州 汉口 东胜西 昌图西 锦州南 安阳东 怀化 黄南 亚龙湾 扬州 温州 南翔北 福安 金山北 永川东 安达 曲阜东 郑州西 天门 绍兴北 涪陵北 阳泉北 三亚 葫芦岛北 徐州 阳江 辽源 新泰 阿坝 孝感北 三穗 金寨 保山 高安 安阳 牟平 西双版纳 信阳 繁昌西 哈尔滨北 达州 新余 沈阳南 四平 扶余北 伊宁 郴州西 济源 水家湖 民权北 福鼎 如皋 奉化 全州南 安庆 太姥山 武汉 乐清 皮口 武昌 茂名 邯郸 资阳 马鞍山 三水南 泰安 包头东 衡阳东 南丰 仙桃西 安吉 罗源 山海关 平湖 惠州 资阳北 淄博 丹阳 莱州 巴东 关岭 盐城 锦州 格尔木 益阳 大英东 吉林 湛江 临安 襄汾西 渑池南 当涂东 辽阳 徐水 贺州 韶关 光明城 邯郸东 普安县 南江口 铜川 五龙背东 张家港 烟台南 萍乡北 青堆 长乐 江门 台州 衡水 湘潭北 闽清北 高邑西 盖州西 石柱县 潮汕 肇庆 泰康 邵东 湖州 余姚 平凉 宜宾 增城 沧州 都匀 防城港 鹰潭北 海东西 福田 余姚北 岳池 广州北 南安 蓬莱 瓦房店西 李石寨 葛店南 海安 无锡东 上饶 通辽 四会 桂林西 砀山南 兰州 滨海 龙口 绅坊 莱西 石林西 深圳 大连北 成都 上海西 孝感 杏树屯 德清 嘉兴

Copyright © 2016 phpStudy |