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能够储存最多一千位精度的数字并且精确地展开计算。由此相当适合用于货币金额和别的须要测算标准的数额。可是,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
    );
  
 这样,大家就创立了三个整数字段并且把它的缺省数值安插为从三个队列爆发器取值。应用了3个NOT
NULL约束以保证空值不会被插入。在多数情况下您只怕还盼望附加三个UNIQUE大概PPAJEROIMAEvoqueY
KEY约束制止不测地插入重复的数值,但这一个不是半自动发出的。由此,假使你指望贰个行列字段有一个唯一约束依旧1个主键,那么你未来必须证明,就如别的数据类型一样。
  
 还需求其余表明的是,三个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=> INSE奥迪Q7T 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. 日期/时间输入:
  
 任何日期或然时间的文书输入均须求由单引号包围,就象3个文本字符串一样。
    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). 时间戳:
  
 时间戳类型的得力输入由3个日期和岁月的交接组成,前边随着一个可选的时区。由此,一九九七-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  

    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)。该品种占用一个字节。
    “真”值的实惠文本值是:
    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是2个正整数。bit类型的数目必须精确匹配长度n;
试图存款和储蓄短些或然长一些的数码都以破绽百出的。类型bit
varying数据是最长n的变长类型;更长的串会被拒绝。写3个不曾长度的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中还提供了走访数组范围的职能,即A安德拉RAY[脚标下界:脚标上界]。
    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); –数组成分中有其它一个等于一千0,where条件将建立。
    SELECT * FROM sal_emp WHERE 10000
ALL (pay_by_quarter); –唯有当数组中有所的因素都优秀一千0时,where条件才确立。

七 、复合类型:

  
 PostgreSQL中复合类型有个别类似于C语言中的结构体,也可以被视为Oracle中的记录类型,可是仍旧感到复合类型这几个命名比较确切。它其实只是1个字段名和它们的数据类型的列表。PostgreSQL允许像简单数据类型那样采纳复合类型。比如,表字段能够证明为二个复合类型。
    1. 宣称复合类型:
    上边是多个简易的扬言示例:
    CREATE TYPE complex AS (
        r double,
        i double
    );   
    CREATE TYPE inventory_item AS (
        name           text,
        supplier_id   integer,
        price            numeric
    );
  
 和证明一(Wissu)个数据表相比较,评释类型时索要加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表明式,假如内部的字段数量超越叁个,那么首要字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前边出现的字段名周围加圆括号,

    –但是在等号左侧的表达式里引用同2个字段时却须求圆括号。
    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官方文书档案中“数据类型”章节的简易总结,那里之所以用一篇独立的博客来尤其介绍,不仅是为了系统学习,也方便以后亟需时的敏捷查看。

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