PostgreSQL 中的常用的数据类型

1.CREATE TYPE 命令增加新的数据类型

2.数据类型

名字	别名	描述
bigint	int8	有符号 8 字节整数
bigserial	serial8	自增 8 字节整数
bit [ (n) ]		定长位串
bit varying [ (n) ]	varbit	变长位串
boolean	bool	逻辑布尔值(真/假)
box		平面中的矩形
bytea		二进制数据("字节数组")
character varying [ (n) ]	varchar [ (n) ]	变长字符串
character [ (n) ]	char [ (n) ]	定长字符串
cidr		IPv4 或 IPv6 网络地址
circle		平面中的圆
date		日历日期(年, 月, 日)
double precision	float8	双精度浮点数字
inet		IPv4 或 IPv6 网络地址
integer	int, int4	有符号 4 字节整数
interval [ (p) ]		时间间隔
line		平面中的无限长直线
lseg		平面中的线段
macaddr		MAC 地址
money		货币金额
numeric [ (p, s) ]	decimal [ (p, s) ]	可选精度的准确数字
path		平面中的几何路径
point		平面中的点
polygon		平面中的封闭几何路径
real	float4	单精度浮点数
smallint	int2	有符号 2 字节整数
serial	serial4	自增 4 字节整数
text		变长字符串
time [ (p) ] [ without time zone ]		一天中的时间
time [ (p) ] with time zone	timetz	一天里的时间，包括时区
timestamp [ (p) ] [ without time zone ]		日期和时间
timestamp [ (p) ] with time zone	timestamptz	日期和时间，包括时区

3.数值类型

名字	存储空间	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围的整数	-9223372036854775808 到 9223372036854775807
decimal	变长	用户声明精度，精确	无限制
numeric	变长	用户声明精度，精确	无限制
real	4 字节	变精度，不精确	6 位十进制数字精度
double precision	8 字节	变精度，不精确	15 位十进制数字精度
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	大范围的自增整数	1 到 9223372036854775807

3. 整数类型
smallint, integer, bigint 类型存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。

常用的类型是 integer ，因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint 。而只有在 integer 的范围不够的时候才使用 bigint ，因为前者绝对快得多。

bigint 类型可能不是在所有平台上都运转正确，因为它依赖编译器对八字节整数的支持。在那些没有八字节整数支持的机器上，bigint 的作用和 integer 一样(但是仍然占据八字节存储)。不过，我们目前还没听说过有这样的平台。

SQL 只声明了整数类型 integer(或 int) 和 smallint 。类型 bigint 和类型别名 int2, int4, int8 都是扩展，并且也在许多其它 SQL 数据库系统中使用。

4.任意精度数值
numeric 类型可以存储最多 1000 位精度的数字并且准确地进行计算。特别建议将它用于货币金额和其它要求精确计算的场合。不过，numeric 类型上的算术运算比整数类型要慢很多。

术语：一个 numeric 类型的标度(scale)是小数部分的位数，精度(precision)是全部数据位的数目，也就是小数点两边的位数总和。因此数字 23.5141 的精度为 6 而标度为 4 。你可以认为整数的标度为零。

numeric 字段的最大精度和最大标度都是可以配置的。要声明一个字段的类型为 numeric ，你可以用下面的语法：

NUMERIC(precision, scale)
精度必须为正数，标度可以为零或者正数。另外，

NUMERIC(precision)
选择了标度为 0 。不带任何精度与标度的声明

NUMERIC
则创建一个可以存储一个直到实现精度上限的任意精度和标度的数值，一个这样类型的字段将不会把输入数值转化成任何特定的标度，而带有标度声明的 numeric 字段将把输入值转化为该标度。SQL 标准要求缺省的标度是 0(也就是转化成整数精度)。如果你关心移植性，那你最好总是明确声明精度和标度。

如果一个要存储的数值的标度比字段声明的标度高，那么系统将尝试圆整(四舍五入)该数值到指定的小数位。然后，如果小数点左边的数据位数超过了声明的精度减去声明的标度，那么将抛出一个错误。

numeric 类型的数据值在物理上是不带任何前导或者后缀零的形式存储的。因此，字段上声明的精度和标度都是最大值，而不是固定分配的。在这个方面，numeric 类型更类似于 varchar(n) 而不是 char(n) 。实际存储是每四个十进制位两个字节，然后在整个数据上加上八个字节的额外开销。

除了普通的数字值之外，numeric 类型允许用特殊值 NaN 表示"不是一个数字"。任何在 NaN 上面的操作都生成另外一个 NaN 。如果在 SQL 命令里把这些值当作一个常量写，你必须在其周围放上单引号，比如 UPDATE table SET x = 'NaN' 。在输入时，字符串 NaN 是大小写无关的。

类型 decimal 和 numeric 是等效的。两种类型都是 SQL 标准。

5.浮点数类型
数据类型 real 和 double precision 是不精确的、变精度的数字类型。
如果你要求精确的计算(比如计算货币金额)，应使用 numeric 类型。

如果你想用这些类型做任何重要的复杂计算，尤其是那些你对范围情况(无穷/下溢)严重依赖的事情，那你应该仔细评诂你的实现。

拿两个浮点数值进行相等性比较可能像、也可能不像你想像那样运转。

在大多数平台上，real 类型的范围是至少 1E-37 到 1E+37 ，精度至少是 6 位小数。double precision 的范围通常是 1E-307 到 1E+308 ，精度是至少 15 位数字。太大或者太小的数值都会导致错误。如果输入数据的精度太高，那么将会发生园整。太接近零的数字，如果无法与零值的表现形式相区分就会产生下溢错误。

除了普通的数字值之外，浮点类型还有几个特殊值：

Infinity
-Infinity
NaN

这些值分别表示 IEEE 754 特殊值"正无穷大"、"负无穷大"、"不是一个数字"。在不遵循 IEEE 754 浮点算术的机器上，这些值的含义可能不是预期的。如果在 SQL 命令里把这些数值当作常量写，你必须在它们周围放上单引号，像这样：UPDATE table SET x = 'Infinity' 。输入时，这些值是以大小写无关的方式识别的。

PostgreSQL 还支持 SQL 标准表示法 float 和 float(p) 用于声明非精确的数值类型。其中的 p 声明以二进制位表示的最低可接受精度。在选取 real 类型的时候，PostgreSQL 接受 float(1) 到 float(24)，在选取 double precision 的时候，接受 float(25) 到 float(53) 。在允许范围之外的 p 值将导致一个错误。没有声明精度的 float 将被当作 double precision 。

【注意】PostgreSQL 7.4以前，在 float(p) 里面的精度会被当作是这么多位数的十进制位。到 7.4 已经被修改成与 SQL 标准匹配，标准声明这个精度是以二进制位度量的。假设 real 和 double precision 分别有 24 和 53 个二进制位的位数对 IEEE 标准的浮点实现来说是正确的。在非 IEEE 平台上，这个数值可能略有偏差，但是为了简化，我们在所有平台上都用了同样的 p 值范围。

6.序列号类型
serial 和 bigserial 类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。类似其它一些数据库中的 AUTO_INCREMENT 属性。在目前的实现中，下面一个语句：

CREATE TABLE tablename (
    colname SERIAL
);
等价于声明下面几个语句：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
因此，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器读取。应用了一个 NOT NULL 约束以确保 NULL 不会被插入。在大多数情况下你可能还希望附加一个 UNIQUE 或 PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动的。最后，将序列发生器"从属于"那个字段，这样当该字段或表被删除的时候也一并删除它。

【注意】PostgreSQL 7.3以前，serial 隐含 UNIQUE 。但现在不再如此。如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。

要在 serial字段中插入序列中的下一个数值，主要是要注意 serial字段应该赋予缺省值。我们可以通过在 INSERT 语句中把该字段排除在字段列表之外来实现，也可以通过使用 DEFAULT 关键字来实现。

类型名 serial 和 serial4 是等效的：两者都创建 integer 字段。类型名 bigserial 和 serial8 也一样，只不过它创建一个 bigint 字段。如果你预计在表的生存期中使用的标识数目可能超过 231 个，那么你应该使用 bigserial 。

一个 serial 类型创建的序列在所属的字段被删除的时候自动删除。你可以只删除序列而不删除字段，不过这将删除该字段的缺省值表达式。