PostgreSQL的数据类型

1.1 数据类型

PostgreSQL 有着丰富的数据类型可⽤。 ⽤户可以使⽤ CREATE TYPE 命令为 PostgreSQL 增加新的数据类型。

Table1-1显⽰了所有内置的普通数据类型。 在\"别名\"列⾥列出的⼤多数可选名字都是因历史原因 PostgreSQL 在内部使⽤的名字。另外，还有⼀些内部使⽤的或者废弃的类型也可以⽤，但没有在这⾥列出。Table1-1： 数据类型名字bigintbigserialbit [ (n) ]bit varying [ (n) ]booleanboxbyteacharacter varying [ (n) ]character [ (n) ]cidrcircledatedouble precisioninetintegerinterval [ (p) ]linelsegmacaddrmoneynumeric [ (p, s) ]pathpointpolygonrealsmallintserialtexttime [ (p) ] [ without time zone ]time [ (p) ] with time zone

timestamp [ (p) ] [ without time zone ]

别名

int8serial8

描述

有符号 8 字节整数⾃增⼋字节整数定长位串变长位串

逻辑布尔量 （真/假）平⾯中的长⽅形

⼆进制数据（\"字节数组\"）变长字符串定长字符串

IPv4 或者 IPv6 ⽹络地址平⾯中的圆

⽇历⽇期（年，⽉，⽇）双精度浮点数字IPv4 或者 IPv6 ⽹络地址四字节长有符号整数时间间隔

平⾯中的⽆限长直线平⾯中的线段MAC 地址货币⾦额

可选精度的准确数字平⾯中的⼏何路径平⾯中的点

平⾯中的封闭⼏何路径单精度浮点数有符号两字节整数⾃增四字节整数变长字符串⼀天⾥的时间

⼀天⾥的时间，包括时区⽇期和时间⽇期和时间

varbitbool

varchar [ (n) ]char [ (n) ]

float8

int，int4

decimal [ (p, s) ]

float4int2serial4

timetz

timestamp [ (p) ] with time zone

timestamptz

兼容性: 下列类型（或者那样拼写的）是SQL声明的： bit，bit varying，boolean， char，character，character

varying，varchar，date， double precision，integer， interval，numeric，decimal， real，smallint，time （包括有时区和⽆时区

的）， timestamp （包括有时区和⽆时区的）。

每种数据类型都有⼀个由其输⼊和输出函数决定的外部表现形式。 许多内建的类型有明显的格式。不过，许多类型要么是 PostgreSQL 所特有的，⽐如⼏何路径，要么可能是有⼏种不同的格式，⽐如⽇期和时间类型。 有些输⼊和输出函数是不可逆的。也就是说，输出函数的输出结果和原始的输⼊⽐较的时候可能丢失精度。

在创建pgsql数据库表时，如果要给主键设置数据类型可以参考上表数据名称。例如，在创建表sl_log_exception时，主键id设置数据类型为serial，创建完成查看表结构主键id，会显⽰索引。

1.2 数值类型

数值类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度⼩数组成。 Table 1-2 列出了所有可⽤类型。Table 1-2. 数值类型名字smallintintegerbigintdecimalnumericrealdouble precisionserialbigserial存储空间描述2 字节4 字节8 字节变长变长4 字节8 字节4 字节8 字节

⼩范围整数常⽤的整数⼤范围的整数

范围

-32768 到 +32767

-2147483648 到 +2147483647

-9223372036854775808 到 9223372036854775807

⽤户声明精度，精确⽆限制⽤户声明精度，精确⽆限制变精度，不精确变精度，不精确⾃增整数

⼤范围的⾃增整数

6 位⼗进制数字精度15 位⼗进制数字精度1 到 +2147483647

1 到 9223372036854775807

1.2.1. 整数类型

类型 smallint，integer，和 bigint 存储各种范围的全部是数字的数，也就是没有⼩数部分的数字。 如果存储超出范围以外的数值将导致错误。常⽤的类型是 integer，因为它提供了在范围，存储空间， 和性能之间的最佳平衡。⼀般只有在磁盘空间紧张的时候才使⽤ smallint。⽽只有在 integer 的范围不够的时候才使⽤ bigint。

bigint 类型可能不是在所有平台上都运转正确， 因为它依赖编译器对⼋字节整数的⽀持。在那些没有这样⽀持的机器上， bigint 的作⽤

和 integer ⼀样（但是仍然占据⼋字节存储）。

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

1.2.2. 任意精度数值

类型 numeric 可以存储最多1000位精度的数字并且准确地进⾏计算。 我们特别建议将它⽤于货币⾦额和其它要求计算准确的数量。不过，numeric 类型上的算术运算⽐整数类型或者浮点数类型要慢很多。

numeric 字段的最⼤精度和最⼤⽐例都是可以配置的。要声明⼀个类型为 numeric 的字段，你可以⽤下⾯的语法NUMERIC(precision, scale)

精度必须为正数，⽐例可以为零或者正数。 另外，

NUMERIC(precision)

选择了 0 为⽐例。不带任何精度或者⽐例声明

NUMERIC

则创建⼀个可以存储⼀个直到实现精度上限的任意精度和⽐例的数值， ⼀个这样类型的字段将不会把输⼊数值转化成任何特定的⽐例， ⽽带有⽐例声明的 numeric 字段将把输⼊值转化为该⽐例。（SQL标准要求缺省的⽐例是 0，也就是转化成整数精度。）

如果⼀个要存储的数值的⽐例⽐字段声明的⽐例⾼， 那么系统将尝试圆整（四舍五⼊）该数值到指定的⼩数位。 然后，如果⼩数点左边的数据位数超过了声明的精度减去声明的⽐例， 那么会抛出⼀个错误。

数值数据值物理上是不带任何前导或者后缀零的形式存储的。 因此，字段上声明的精度和⽐例都是最⼤值，⽽不是固定分配的。（在这个⽅⾯，numeric 类型更类似于 varchar(n)， ⽽不像 char(n)。） 实际存储是每四个⼗进制位两个字节，然后在整个数据上加上⼋个字节的额外开销。除了普通的数字值之外，numeric 类型允许特殊值 NaN， 表⽰\"不是⼀个数字\"。任何在 NaN 上⾯的操作都⽣成另外⼀个 NaN。 如果在 SQL 命令⾥把这些值当作⼀个常量写，你必须在其周围放上单引号，⽐如 UPDATE table SET x = 'NaN'。在输⼊时，字串 NaN当作⼤⼩写⽆关看待。类型 decimal 和 numeric 是等效的。 两种类型都是SQL标准。

1.2.3. 浮点数类型

数据类型 real 和 double precision 是不准确的，变精度的数字类型。 实际上，这些类型是 IEEE 标准 754 ⼆进制浮点数算术（分别对应单和双精度）的⼀般实现， 外加下层处理器，操作系统和编译器对它的⽀持。

不准确意味着⼀些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储然后把数据再打印出来可能显⽰⼀些缺失。 处理这些错误以及这些错误是如何在计算中传播的属于数学和计算机科学的⼀个完整的分⽀， 这⾥的讨论仅限于如下⼏点：

如果你要求准确的计算（⽐如计算货币⾦额），应使⽤ numeric 类型。

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

拿两个浮点数值进⾏相等性⽐较可能象，也可能不象想像那样运转。

通常，real 类型的范围是⾄少 -1E+37 到 +1E+37， 精度⾄少是 6 位⼩数。double precision 类型通常有 -1E+308 到 +1E+308 的范围，精度是⾄少 15 位数字。太⼤或者太⼩的数值都会导致错误。 除了普通的数字值之外，浮点类型还有⼏个特殊值：

Infinity-InfinityNaN

这些值分别表⽰ 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 值范围。

1.2.4. Serial（序号）类型

serial 和 bigserial 类型不是真正的类型， 只是为在表中设置唯⼀标识做的概念上的便利。（类似其它⼀些数据库中的 AUTO_INCREMENT 属

性）。下⾯⼀句话：

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 约束避免意外地插⼊重复的数值，但这个不是⾃动发⽣的。

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

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

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

⼀个 serial 类型创建的序列在所属的字段被删除的时候⾃动删除，其它情况下是不会被删除的。 （这⼀点在 PostgreSQL 版本 7.3 之前可不是真的。请注意，这种⾃动删除的关联在通过重载 7.3 以前的数据库转储的时候可不会⾃动发⽣； 那样的转储⽂件不包含需要建⽴这种关联关系的信息。） 另外，这样的序列和字段之间的依赖性只在 serial 字段本⾝上有； 如果任何其它字段引⽤了序列（可能是⼿⼯调⽤ nextval 函数）， 那么，如果这个序列被删除了，它们就会被破坏。我们认为这样使⽤ serial 字段是⼀种不好的形式。