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)