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

例子 描述
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

    下边是PostgreSQL所协助的数值类型的列表和简易表明:

 Oracle,   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)

④ 、布尔类型:

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

名字 描述
varchar(n) 变长,有长度限制
char(n) 定长,不足补空白
text 变长,无长度限制

    该篇博客是对PostgreSQL官方文档中“数据类型”章节的简要归纳,那里之所以用一篇独立的博客来尤其介绍,不仅是为着系统学习,也有益于未来内需时的连忙查看。

    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)    

    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); –数组元素中有任何一个也就是一千0,where条件将确立。
    SELECT * FROM sal_emp WHERE 10000
ALL (pay_by_quarter); –唯有当数组中颇具的成分都等于一千0时,where条件才建立。

  
 位串就是一串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)

六、数组:

    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)

例子 描述
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日

   1. 整数体系:
  
 类型smallint、integer和bigint存款和储蓄各样限制的百分之百是数字的数,也正是没有小数部分的数字。试图存款和储蓄超出范围以外的数值将导致一个不当。常用的门类是integer,因为它提供了在界定、存款和储蓄空间和性质之间的极品平衡。一般唯有在磁盘空间紧张的时候才使用smallint。而只有在integer的限定不够的时候才使用bigint,因为前端(integer)绝对快得多。 

转自:http://www.cnblogs.com/stephen-liu74/archive/2012/04/30/2293602.html

名字 存储空间 描述 最低值 最高值 分辨率
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位

    PostgreSQL扶助标准的SQL
boolean数据类型。boolean只可以有三个状态之一:真(True)或
假(False)。该品种占用一个字节。
    “真”值的实惠文本值是:
    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)
    
5、位串类型:

    下边是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

    3). 时间戳:
  
 时间戳类型的管用输入由1个日子和岁月的连接组成,前面跟着三个可选的时区。因而,1996-01-08
04:05:06和一九九六-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  

7、复合类型:

    1. 日期/时间输入:
  
 任何日期恐怕时间的文书输入均须求由单引号包围,就象三个文本字符串一样。
    1). 日期:
    以下为法定的日子格式列表:

② 、字符类型:

    2. 私行精度数值:
  
 类型numeric能够储存最多一千位精度的数字并且精确地拓展测算。由此非凡适合用于货币金额和任何供给测算标准的数量。可是,numeric类型上的算术运算比整数类型可能浮点数类型要慢很多。
  
 numeric字段的最大精度和最大比例都以足以布署的。要声澳优个类别为numeric的字段,你能够用上边包车型客车语法:
    NUMERIC(precision,scale)
    比如数字23.5141的精度为6,而刻度为4。
    在当前的PostgreSQL版本中,decimalnumeric是一模一样的。
    
    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也许PCR-VIMA奥迪Q5Y
KEY约束防止意内地插入重复的数值,但以此不是全自动发出的。由此,即便你希望一个队列字段有3个唯一约束依旧1个主键,那么你今后必须注明,就好像其余数据类型一样。
  
 还亟需此外表达的是,1个serial类型创立的行列在其所属字段被删除时,该种类也将被电动删除,可是任何情况下是不会被去除的。因而,借使您想用同一个行列产生器同时给多少个字段提供数据,那么就应有以单身对象的点子创制该系列发生器。 

  
 PostgreSQL中复合类型有些类似于C语言中的结构体,也得以被视为Oracle中的记录类型,不过还是感觉到复合类型那些命名比较妥善。它实际上只是2个字段名和它们的数据类型的列表。PostgreSQL允许像简单数据类型那样采取复合类型。比如,表字段能够注解为2个复合类型。
    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表明式,假使中间的字段数量超过3个,那么重庆大学字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);
  

壹 、数值类型:

    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中还提供了访问数组范围的成效,即ACRUISERRAY[脚标下界:脚标上界]。
    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)

    SQL
定义了三种基本的字符类型,varchar(n)和char(n),那里的n是1个正整数。三种档次都能够储存最多n个字符长的字串,试图存款和储蓄更长的字串到这几个品种的字段里会产生3个张冠李戴,除非超出长度的字符都以空白,那种状态下该字串将被截断为最大尺寸。借使没有长度声明,char等于char(1),而varchar则足以承受任何长度的字串。
    MyTest=> CREATE TABLE testtable(first_col varchar(2));
    CREATE TABLE
    MyTest=> INSE福特ExplorerT 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。
    
③ 、日期/时间项目:

相关文章