PostgreSQL数据类型（中⽂⼿册）
⼀、数据类型
PostgreSQL有着丰富的本地数据类型可⽤。⽤户可以使⽤命令为 PostgreSQL增加新的数据类型。
显⽰了所有内建的普通数据类型。⼤部分在“别名”列⾥列出的可选名字都是因历史原因被PostgreSQL在内部使⽤的名字。另外，还有⼀些内部使⽤的或者废弃的类型也可以⽤，但没有在这⾥列出。
表 8.1. 数据类型
名字别名描述
bigint int8有符号的8字节整数
bigserial serial8⾃动增长的8字节整数
bit [ (*n*) ]定长位串
bit varying [ (*n*) ]varbit [ (*n*) ]变长位串
boolean bool逻辑布尔值（真/假）
box平⾯上的普通⽅框
bytea⼆进制数据（“字节数组”）
character [ (*n*) ]char [ (*n*) ]定长字符串
character varying [ (*n*) ]varchar [ (*n*) ]变长字符串
cidr IPv4或IPv6⽹络地址
circle平⾯上的圆
date⽇历⽇期（年、⽉、⽇）
double precision float8双精度浮点数（8字节）
inet IPv4或IPv6主机地址
integer int, int4有符号4字节整数
interval [ *fields* ] [ (*p*) ]时间段
json⽂本 JSON 数据
jsonb⼆进制 JSON 数据，已分解
line平⾯上的⽆限长的线
lseg平⾯上的线段
macaddr MAC（Media Access Control）地址
macaddr8MAC（Media Access Control）地址（EUI-64格式）
money货币数量
numeric [ (*p*, *s*) ]decimal [ (*p*, *s*) ]可选择精度的精确数字
path平⾯上的⼏何路径
pg_lsn PostgreSQL⽇志序列号
point平⾯上的⼏何点
polygon平⾯上的封闭⼏何路径
real float4单精度浮点数（4字节）
smallint int2有符号2字节整数
smallserial serial2⾃动增长的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⽇期和时间，包括时区
tsquery⽂本搜索查询
tsvector⽂本搜索⽂档
txid_snapshot⽤户级别事务ID快照
uuid通⽤唯⼀标识码
xml XML数据
1.1 兼容性
下列类型（或者及其拼写）是SQL 指定的：bigint 、bit 、bit varying 、boolean 、char 、character
varying 、character 、varchar 、date 、double precision 、integer 、interval 、numeric 、decimal 、real 、smallint 、time （有时区或⽆时区）、timestamp （有时区或⽆时区）、xml 。
每种数据类型都有⼀个由其输⼊和输出函数决定的外部表现形式。许多内建的类型有明显的格式。不过，许多类型要么是PostgreSQL 所特有的（例如⼏何路径），要么可能是有⼏种不同的格式（例如⽇期和时间类型）。 有些输⼊和输出函数是不可逆的，即输出函数的结果和原始输⼊⽐较时可能丢失精度。
⼆、数字类型
数字类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度⼩数组成。列出了所有可⽤类型。
表 8.2. 数字类型
名字
存储尺⼨描述范围smallint
2字节⼩范围整数-32768 to +32767integer
4字节整数的典型选择-2147483648 to +2147483647bigint
8字节⼤范围整数-9223372036854775808 to +9223372036854775807decimal
可变⽤户指定精度，精确最⾼⼩数点前131072位，以及⼩数点后16383位numeric
可变⽤户指定精度，精确最⾼⼩数点前131072位，以及⼩数点后16383位real
4字节可变精度，不精确6位⼗进制精度double precision 8字节可变精度，不精确15位⼗进制精度smallserial 2字节⾃动增加的⼩整数1到32767serial
4字节⾃动增加的整数1到2147483647bigserial 8字节⾃动增长的⼤整数1到9223372036854775807 数字类型常量的语法在⾥描述。数字类型有⼀整套对应的数学操作符和函数。相关信息请参考 。下⾯的⼏节详细描述这些类型。2.1 整数类型 类型smallint 、integer 和bigint 存储各种范围的全部是数字的数，也就是没有⼩数部分的数字。试图存储超出范围以外的值将导致⼀个错误。 常⽤的类型是integer ，因为它提供了在范围、存储空间和性能之间的最佳平衡。⼀般只有在磁盘空间紧张的时候才使⽤ smallint 类型。⽽只有在integer 的范围不够的时候才使⽤bigint 。 SQL 只声明了整数类型integer （或int ）、smallint 和bigint 。类型int2、int4和int8都是扩展，也在许多其它SQL 数据库系统中使⽤。2.2 任意精度数字
类型numeric 可以存储⾮常多位的数字。我们特别建议将它⽤于货币⾦额和其它要求计算准确的数量。numeric 值的计算在可能的情况下会得到准确的结果，例如加法、减法、乘法。不过，numeric 类型上的算术运算⽐整数类型或者下⼀节描述的浮点数类型要慢很多。 在随后的内容⾥，我们使⽤了下述术语：⼀个numeric 的precision （精度）是整个数中有效位的总数，也就是⼩数点两边的位
数。numeric 的scale （刻度）是⼩数部分的数字位数，也就是⼩数点右边的部分。因此数字 23.5141 的精度为6⽽刻度为4。可以认为整数的刻度为零。
numeric 列的最⼤精度和最⼤⽐例都是可以配置的。要声明⼀个类型为numeric 的列，你可以⽤下⾯的语法：
postgre trunc函数的使用方法NUMERIC(precision, scale)
精度必须为正数，⽐例可以为零或者正数。另外：
NUMERIC(precision)
###
选择⽐例为 0 。如果使⽤
NUMERIC
创建⼀个列时不使⽤精度或⽐例，则该列可以存储任何精度和⽐例的数字值，并且值的范围最多可以到实现精度的上限。⼀个这种列将不会把输⼊值转化成任何特定的⽐例，⽽带有⽐例声明的numeric 列将把输⼊值转化为该⽐例（SQL 标准要求缺省的⽐例是 0，即转化成整数精度。我们觉得这样做有点没⽤。如果你关⼼移植性，那你最好总是显式声明精度和⽐例）。
注意
显式指定类型精度时的最⼤允许精度为 1000，没有指定精度的NUMERIC 受到中描述的限制所控制。
如果⼀个要存储的值的⽐例⽐列声明的⽐例⾼，那么系统将尝试圆整（四舍五⼊）该值到指定的分数位数。 然后，如果⼩数点左边的位数超过了声明的精度减去声明的⽐例，那么抛出⼀个错误。
数字值在物理上是以不带任何前导或者后缀零的形式存储。 因此，列上声明的精度和⽐例都是最⼤值，⽽不是固定分配的 （在这个⽅⾯，numeric 类型更类似于varchar(*n *)， ⽽不像char(*n *)）。 实际存储要求是每四个⼗进制位组⽤两个字节，再加上三到⼋个字节的开销。 除了普通的数字值之外，numeric 类型允许特殊值NaN ， 表⽰“不是⼀个数字”。任何在 NaN 上⾯的操作都⽣成另外⼀个NaN 。 如果在 SQL 命令⾥把这些值当作⼀个常量写，你必须在其周围放上单引号，例如UPDATE table SET x = 'NaN'。在输⼊时，字串NaN 被识别为⼤⼩写⽆关。
注意
在“不是⼀个数字”概念的⼤部分实现中，NaN 被认为不等于任何其他数字值（包括NaN ）。为了允许numeric 值可以被排序和使⽤基于树的索引，PostgreSQL 把NaN 值视为相等，并且⽐所有⾮NaN 值都要⼤。
类型decimal 和numeric 是等效的。两种类型都是SQL 标准的⼀部分。
在对值进⾏圆整时，numeric 类型会圆到远离零的整数，⽽（在⼤部分机器上）real 和double precision
类型会圆到最近的偶数上。例如：SELECT x, round(x::numeric) AS num_round, round(x::double precision) AS dbl_round FROM generate_series(-3.5, 3.5, 1) as x; x | num_round | dbl_round ------+-----------+----------- -3.5 | -4 | -4 -2.5 | -3 | -2 -1.5 | -2 | -2 -0.5 | -1 | -0 0.5 | 1 | 0 1.5 | 2 | 2 2.5 | 3 | 2 3.5 | 4 | 4
(8 rows)
2.3 浮点类型
数据类型real 和double precision 是不精确的、变精度的数字类型。 在所有当前⽀持的平台上，这些类型是IEEE 标准 754 ⼆进制浮点算术（分别对应单精度和双精度）的实现， ⼀直到下层处理器、操作系统和⽀持它的编译器。
不准确意味着⼀些值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储和检索⼀个值可能出现⼀些缺失。 处理这些错误以及这些错误是如何在计算中传播的主题属于数学和计算机科学的⼀个完整的分⽀， 我们不会在这⾥进⼀步讨论它，这⾥的讨论仅限于如下⼏点：
如果你要求准确的存储和计算（例如计算货币⾦额），应使⽤numeric 类型。
>
如果你想⽤这些类型做任何重要的复杂计算，尤其是那些你对范围情况（⽆穷、下溢）严重依赖的事情，那你应该仔细评诂你的实现。
⽤两个浮点数值进⾏等值⽐较不可能总是按照期望地进⾏。
在所有当前⽀持的平台上，real 类型的范围是 1E-37 to 1E+37 ，精度⾄少是 6 位⼩数。 double precision 类型的范围是 1E-307 to 1E+308，精度⾄少是 15 位数字。 太⼤或者太⼩的值都会导致错误。 如果输⼊数字的精度太⾼，那么可能发⽣四舍五⼊。 太接近零的数字，如果不能体现出与零的区别就会导致下溢错误。
默认情况下，浮点值以其最短精确的⼗进制表⽰的⽂本形式输出；所产⽣的⼗进制值与相同⼆进制精度的任何其他的值表⽰相⽐，更接近于真实存储的⼆进制值。 （但是，当前输出值永远不会精确地处于两个可表⽰的值之间，以免输⼊程序不能正确遵守舍近取整法则。） 对于float8值，此值最多使⽤ 17 个有效⼗进制数字，对于float4值，最多使⽤9个数字。
注意
⽣成这种最短精确的输出格式⽐历史的四舍五⼊的格式要快得多。
为了与PostgreSQL 的较旧版本⽣成的输出兼容，并允许降低输出精度，可以使⽤参数选择四舍五⼊的
⼗进制输出。 将值设置为0将恢复以前的默认值，即将值四舍五⼊为6（对于float4）或15（对于float8）个有效的⼗进制数字。 设置负值会进⼀步减少位数。 例如-2会将输出分别舍⼊到4或13位数字。
设置位任何⼤于 0 的值将选择最短精确格式。
注意
需要更精确值的应⽤需要设置为3以获取更精确值。 为了版本之间的最⼤兼容性，他们可以继续这样做。
除了普通的数字值之外，浮点类型还有⼏个特殊值：Infinity -Infinity
NaN
这些分别代表 IEEE 754 特殊值“infinity”、“negative infinity”以及“not-a-number”， 如果在 SQL 命令⾥把这些数值当作常量写，你必须在它们周围放上单引号，例如UPDATE table SET x = '-Infinity'。 在输⼊时，这些字符串是以⼤⼩写不敏感的⽅式识别的。
注意
IEEE754指定NaN 不应该与任何其他浮点值（包括NaN ）相等。为了允许浮点值被排序或者在基于树的索引中使⽤，PostgreSQL 将NaN 值视为相等，并且⽐所有⾮NaN 值要更⼤。
PostgreSQL 还⽀持 SQL 标准表⽰法float 和float(*p *)⽤于声明⾮精确的数字类型。在这⾥，p 指定以⼆进制位表⽰的最低可接受精度。 在选取real 类型的时候，PostgreSQL 接受float(1)到float(24)，在选取double precision 的时候，接受float(25)到float(53)。在允许范围之外的p 值将导致⼀个错误。没有指定精度的float 将被当作是double precision 。
2.4 序数类型
注意
这⼀节描述了PostgreSQL 特有的创建⼀个⾃增列的⽅法。另⼀种⽅法是使⽤SQL 标准的标识列特性，它在中描述。 smallserial 、serial 和bigserial 类型不是真正的类型，它们只是为了创建唯⼀标识符列⽽存在的⽅便符号（类似其它⼀些数据库中⽀持的AUTO_INCREMENT 属性）。 在⽬前的实现中，下⾯⼀个语句：CREATE TABLE tablename ( colname SERIAL
);
等价于以下语句：
CREATE SEQUENCE tablename_colname_seq AS integer;
>####
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED lname;
因此，我们就创建了⼀个整数列并且把它的缺省值安排为从⼀个序列发⽣器取值。应⽤了⼀个NOT NULL约束以确保空值不会被插⼊（在⼤多数情况下你可能还希望附加⼀个UNIQUE或者PRIMARY KEY约束避免意外地插⼊重复的值，但这个不是⾃动发⽣的）。最后，该序列被标记为“属于”该列，这样当列或表被删除时该序列也会被删除。
##
注意
因为smallserial、serial和bigserial是⽤序列实现的，所以即使没有删除过⾏，在出现在列中的序列值可能有“空洞”或者间隙。如果⼀个从序列中分配的值被⽤在⼀⾏中，即使该⾏最终没有被成功地插⼊到表中，该值也被“⽤掉”了。例如，当插⼊事务回滚时就会发⽣这种情况。更多信息参见中的nextval()。
要使⽤serial列插⼊序列的下⼀个数值到表中，请指定serial列应该被赋予其缺省值。我们可以通过在INSERT语句中把该列排除在列列表之外来实现，也可以通过使⽤DEFAULT关键字来实现。
类型名serial和serial4是等效的：两个都创建integer列。类型名bigserial和serial8也⼀样，只不过它们创建⼀个bigint列。如果你预计在表的⽣存期中使⽤的标识符数⽬超过 231 个，那么你应该使⽤bigserial。类型名smallserial和serial2也以相同⽅式⼯作，只不过它们创建⼀
个smallint列。
为⼀个serial列创建的序列在所属的列被删除的时候⾃动删除。你可以在不删除列的情况下删除序列，但是这会强制删除该列的默认值表达式。
三、货币类型
money类型存储固定⼩数精度的货币数字，参阅。⼩数的精度由数据库的设置决定。表中展⽰的范围假设有两个⼩数位。可接受的输⼊格式很多，包括整数和浮点数⽂字，以及常⽤的货币格式，如'$1,000.0
0'。输出通常是最后⼀种形式，但和区域相关。
表 8.3. 货币类型
名字存储尺⼨描述范围
money8 bytes货币额-92233720368547758.08到+92233720368547758.07
由于这种数据类型的输出是区域敏感的，因此将money数据装⼊到⼀个具有不同lc_monetary设置的数据库是不起作⽤的。为了避免这种问题，在恢复⼀个转储到⼀个新数据库中之前，应确保新数据库的lc_monetary设置和被转储数据库的相同或者具有等效值。
数据类型numeric、int和bigint的值可以被造型成money。从数据类型real和double precision的转换可以通过先造型成numeric来实现，例如：
SELECT '12.34'::float8::numeric::money;
但是，我们不推荐这样做。浮点数不应该被⽤来处理货币，因为浮点数可能会有圆整错误。
⼀个money值可以在不损失精度的情况下被造型成numeric。转换到其他类型可能会丢失精度，并且必须采⽤两个阶段完成：
SELECT '52093.89'::money::numeric::float8;
⼀个money值被⼀个整数值除的除法结果会被截去分数部分。要得到圆整的结果，可以除以⼀个浮点值，或者在除法之前把money转换
成numeric然后在除法之后转回money（如果要避免精度丢失的风险则后者更好）。当⼀个money值被另⼀个money值除时，结果是double precision（即⼀个纯数字，⽽不是⾦额），在除法中货币单位被约掉了。
四、字符类型
表 8.4. 字符类型
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
postgrep 炸裂函数 -回复
« 上一篇
发表评论