第三章. 数据类型
内容
数值类型
货币类型
字符类型
日期/时间类型
布尔类型
几何类型
IP 版本 4 网络和主机类型
描述 Postgres 内建的可用数据类型．
Postgres 有着丰富的数据类型可用．用户可以使用 DEFINE TYPE 命令为 Postgres增加新的数据类型．
在数据类型这部分，随后的各节将讨论 SQL 标准的兼容性，移植问题和使用问题．一些 Postgres 类型直接与 SQL92-兼容类型相对应．其它一些 SQL92 语法定义的数据类型直接映射为Postgres 内部的数据类型．许多内建的数据类型有明确的外部格式．但是，有一些数据类型或者是Postgres特有的，如开放和闭合路径，或者是有几种可能格式的类型，如日期和时间类型．
表 3-1. Postgres数据类型
Postgres 类型 SQL92 或 SQL3 类型 描述
bool boolean 逻辑布尔量 （真/假）
box 二维平面中的方形盒
char(n) character(n) 定长字符串
cidr IP v4网络或主机地址
circle 二维平面中的圆
date date 日历日期（不带时间）
decimal decimal(p,s) p <= 9，s = 0 的准确数字
float4 float(p), p < 7 精度为p的浮点数
float8 float(p), 7 <= p < 16 双精度浮点数
inet IP v4网络或主机地址
int2 smallint 两字节长带符号整数
int4 int, integer 四字节长带符号整数
int8 八字节长带符号整数
interval interval 通用的时间间隔
line 二维平面中的直线（无限长）
lseg 二维平面中的线段
money decimal(9,2) 美国风格的货币类型
numeric numeric(p,s) p == 9，s = 0的准确数字
path 二平面的开放的或封闭的几何路径
point 二维平面中的点
polygon 二维平面中的封闭几何路径
serial 用于索引和/或交叉索引的独一无二的标识符
time time 一天里的时间
timetz time with time zone 一天里的时间，包括时区
timestamp timestamp with time zone 日期/时间
varchar(n) character varying(n) 变长的字符串
注意： cidr 和 inet 用于处理任何IP类型数据，但目前只能处理ipv4的数据．在以后的版本里我们将将所有对ipv4的支持增加到对ipv6的支持中．
表 3-2. Postgres 函数常量
Postgres 函数 SQL92 常量 描述
getpgusername() current_user 当前会话的用户名
date('now') current_date 当前事务的日期
time('now') current_time 当前事务的时间
timestamp('now') current_timestamp 当前事务的日期和时间
Postgres 拥有开发 ORDBMS 应用的首要特性．除了符合SQL3规范外，我们还支持很大一部分 SQL92 规范．尽管我们尽可能地遵循 SQL92 规范，但该规范里有一些方面欠缺考虑，在后继的规范中不可能继续存在．Postgres不会为这些特性花费太多的时间；因为这些方面主要发生在很少使用或语意含混的场合，典型的用户很少能碰到它们。
绝大多数与基本类型（如：整数和浮点数）对应的输入输出函数都会做错误检查．出于改善执行性能的考虑，一些操作符和函数（如加法和乘法）并不做运行时的错误检查．因而在一些系统上的对某些数据类型的数字操作会导致轻微的数值溢出或下溢。
要注意的是一些输入输出函数是不可逆的．也就是说，一个输出函数的输出结果与输入的数据相比可能会丢失精度。
注意：浮点数可以保持该类型的最高固有精度（一般来说,双精度是15位，4字节浮点数是6位）．其他依赖浮点的数据类型（如，几何类型）也有相似精度．
数值类型
数值类型由2或4字节的整数以及4或8字节的浮点数和固定精度小数组成。
表 3-3. Postgres 数值类型
数值类型 存储空间 描述 范围
decimal 变长 用户声明精度 ~8000 位（数字）
float4 4 bytes 变精度 6 位数字
float8 8 bytes 变精度 15 位数字
int2 2 bytes 固定精度 -32768 到 +32767
int4 4 bytes 常用的固定精度数 -2147483648 到 +2147483647
int8 8 bytes 极大范围的固定精度数 +/- > 18 位数字
numeric variable 用户声明精度 无限制
serial 4 bytes 标识或交叉索引 0 到 +2147483647
数值类型对应有一套完整的数学操作符和函数．相关信息请参考 数字操作符 和 数学函数。
int8 类型因为是要靠编译器来支持的，因而可能无法在所有平台上得到支持。
Serial（序列）类型
serial 类型是 Postgres 用其他现有的类型构造出来的一种特殊的类型．典型的应用是创建表的唯一标识，在当前的实现中，下面一句话：
CREATE TABLE tablename (colname SERIAL);
等价于下面几句话:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename
(colname INT4 DEFAULT nextval('tablename_colname_seq');
CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
注意
创建为serial 类型的隐含序列号在删除表时不会自动删除
在删除一个包含 serial 类型的表的时候，隐含的支持 serial 的序列号不会被自动删除。因此下面顺序执行的命令将是无效的：
CREATE TABLE tablename (colname SERIAL);
DROP TABLE tablename;
CREATE TABLE tablename (colname SERIAL);
除非显式地使用 DROP SEQUENCE 命令，序列号不会被删掉而一直在数据库里面。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
货币类型
过时的类型：money （货币）现在已经过时，用numeric 或 decimal 取代它。
money 类型支持US-类的固定小数点位置的货币数字．如果编译Postgres 时使用了 USE_LOCALE 编译选项，货币类型将使用为locale(7)定义的货币习惯．
表 3-4. Postgres 货币类型
货币类型 存储空间 描述 范围
money 4 字节 固定精度 -21474836.48 到 +21474836.47
numeric 最终将取代货币类型。因而应该优先使用。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
字符类型
SQL92 定义了两种基本的字符类型： char 和 varchar． Postgres 支持这些类型，并且还支持更通用的 text 类型，该类型不象varchar 一样必须要定义一个数据域的上限．
表 3-5. Postgres 字符类型
字符类型 存储空间 评价 描述
char 1 字节 SQL92-兼容 单字符
char(n) (4+n) 字节 SQL92-兼容 定长,不足补空白
text (4+x) 字节 最优 变长
varchar(n) (4+n) 字节 SQL92-兼容 变长,有局限
另外还有一种定长字符类型． name 类型，该类型只有一个用途，就是提供给Postgres 一个特别的类型用于内部名字．该类型通常不是给一般用户使用的．该类型长度当前定为32字符长，但可以使用 NAMEDATALEN 重新定义．这个（变量）是在编译的时候设置的，在以后的版本可能会改变．
表 3-6. Postgres 特殊字符类型
字符类型 存储空间 描述
name 32 字节 32字节内部类型
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
日期/时间类型
PostgreSQL 支持 SQL 中所有的日期和时间类型。
表 3-7. Postgres 日期/时间类型
类型 描述 存储 最早 最晚 精度
timestamp 用于包含日期和时间的数据 8 bytes 4713 BC AD 1465001 1 microsec / 14 digits
interval 用于时间间隔 12 bytes -178000000 years 178000000 years 1 mircosecond
date 用于只包含日期的数据 4 bytes 4713 BC 32767 AD 1 day
time 用于只包含时刻的数据 4 bytes 00:00:00.00 23:59:59.99 1 microsecond
注意：为了保证和早期 PostgreSQL 版本的兼容，我们还继续提供 datetime（等效于timestamp）和timespan（等效于interval）。不过对这些类型的支持现在局限于进行一个隐含的转换，转换成timestamp 和 interval。类型abstime和 reltime 是低分辨率类型，它们被用于系统内部。我们不鼓励你在新的应用里面使用这些类型，同时我们支持合适的时候把旧应用中对应的类型转换成目前上面指明的。因为这些旧类型的部分或全部在未来的版本里可能消失。
日期/时间输入
日期和时间的输入几乎可以是任何合理的格式，包括ISO-8601，SQL-兼容的，传统 Postgres 的和其他形式的。日期输入里的月份和日子输入可以是模糊的，因此存在一个设置用来表明具体应该如何解释。命令 SET DateStyle TO 'US' 或 SET DateStyle TO 'NonEuropean' 表示设置为“月份在日子前面”，而命令 SET DateStyle TO 'European' 表示设置为“日子在月份前面”。缺省是 ISO 风格，但是缺省值可以在编译或者运行时改变。
参阅 日期/时间支持 获取关于日期/时间输入的准确的分析规则和可识别的时区的信息。
记住任何日期或者时间输入需要被单引号包围，就象一个文本字符串一样。
date（日期）
下表是 date 类型可能的输入方式。
表 3-8. PostgreSQL 日期输入
例子 描述
January 8, 1999 无模糊
1999-01-08 ISO-8601 格式，建议方式
1/8/1999 US；在 European 模式下读做八月一日
8/1/1999 European；在 US 模式下读做八月一日
1/18/1999 US；在任何模式下都读做一月十八日
1999.008 年和年里的第几天
19990108 ISO-8601 年，月，日
990108 ISO-8601 年，月，日
1999.008 年和年里的第几天
99008 年和年里的第几天
January 8, 99 BC 公元前99年
表 3-9. PostgreSQL 月份缩写
月份 缩写
April（四月） Apr
August（八月） Aug
December（十二月） Dec
February（二月） Feb
January（一月） Jan
July（七月） Jul
June（六月） Jun
March（三月） Mar
November（十一月） Nov
October（十月） Oct
September（九月） Sep, Sept
注意： 五月（May）没有缩写，原因是显而易见的。
表 3-10. PostgreSQL 星期缩写
星期 缩写
Sunday（星期天） Sun
Monday（星期一） Mon
Tuesday（星期二） Tue, Tues
Wednesday（星期三） Wed, Weds
Thursday（星期四） Thu, Thur, Thurs
Friday（星期五） Fri
Saturday（星期六） Sat
time（时间）
下面是有效的 time （时间）输入
表 3-11. PostgreSQL 时间输入
例子 描述
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
z 与 00:00:00 一样
zulu 与 00:00:00 一样
allballs 与 00:00:00 一样
带时区时间
这个类型是 SQL92 定义的，但是该定义显示出非常基本的不足，使得这个类型几乎无用。在多数情况下，date，time 和 timestamp 的组合应该能提供任何应用所需要的日期/时间功能的全部范围。
带时区时间 接受所有对 time 类型也合法的输入，附加一个合法的时区，如下：
表 3-12. Postgres 带时区时间输入
例子 描述
04:05:06.789-8 ISO-8601
04:05:06-08:00 ISO-8601
04:05-08:00 ISO-8601
040506-08 ISO-8601
参考 Postgres 时区输入 获取时区的更多例子。
timestamp（时标）
有效的 timestamp 类型的输入包含一个日期和一个时间的连接，后面跟着可选的 AD 或 BC，再后面跟着可选的时区。（参阅下面。）因此
1999-01-08 04:05:06 -8:00
是一个有效的 timestamp 值，它是 ISO-兼容的。另外，已经广泛使用的格式
January 8 04:05:06 1999 PST
也是支持的。
表 3-13. PostgreSQL 时区输入
时区 描述
PST 太平洋标准时间（Pacific Standard Time）
-8:00 ISO-8601 与 PST 的偏移
-800 ISO-8601 与 PST 的偏移
-8 ISO-8601 与 PST 的偏移
interval（时间间隔）
interval 可以用下面语法声明：
Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
这里：Quantity 是 ...，-1，0，1，2，...；Unit 是 second，minute，hour，day，week，month，year，decade，century，millenium，或者这些单位的缩写或复数；Direction 可以是 ago 或者为空。
特殊值
下面的SQL-兼容的函数可以用于对应的数据类型的日期或时间输入：CURRENT_DATE，CURRENT_TIME，CURRENT_TIMESTAMP。
PostgreSQL 为方便期间同样还支持几个特殊常量。
表 3-14. PostgresSQL 日期/时间特殊常量
常量 描述
current 当前事务时间, (使用时)deferred
epoch 1970-01-01 00:00:00+00 (Unix 系统零时)
infinity 比任何有效时间晚
-infinity 比任何有效时间早
invalid 非法输入
now 当前事务时间
today 今日午夜
tomorrow 明日午夜
yesterday 昨日午夜
'now' 在该值被插入时解释，'current' 在每次检索该值时被解释。所以你可能会在大多数应用里使用 'now'。（当然你真的想用 CURRENT_TIMESTAMP 的话也行，它等效于 'now'。）
日期/时间输出
使用 SET DateStyle，输出格式可以设成四种风格之一： ISO-8601， SQL （Ingres），传统的 Postgres，和 German 风格。缺省是 ISO 格式。
表 3-15. PostgreSQL 日期/时间输出风格
风格描述 描述 例子
'ISO' ISO-8601 标准 1997-12-17 07:37:16-08
'SQL' 传统风格 12/17/1997 07:37:16.00 PST
'Postgres' 原始风格 Wed Dec 17 07:37:16 1997 PST
'German' 地区风格 17.12.1997 07:37:16.00 PST
日期和时间风格的输出当然只是对应上面例子的日期或者时间部分。
SQL 风格有欧洲和非欧洲(US)变种， 决定月份后面是日期还是正相反．（参阅上面日期/时间输入部分，看看这个设置是如何影响对输入值的解释。）
表 3-16. PostgreSQL 日期顺序传统
风格 描述 例子
European 日/月/年 17/12/1997 15:37:16.00 MET
US 月/日/年 12/17/1997 07:37:16.00 PST
interval 的输出看起来象输入格式，只是象 week 或 century 这样的单位被转换成年和日。在 ISO 模式下输出看起来象
[ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
有几种方法可以影响日期/时间类型的输出：
直接在postmaster启动时用于后端的环境变量 PGDATESTYLE。
会话开始时用于 libpg 的环境变量 PGDATESTYLE。
SQL 命令 SET DATESTYLE。
时区
Postgres 在典型应用中尽可能与SQL92 的定义相兼容．但 SQL92 标准在日期和时间类型和功能上有一些奇怪的混淆．两个显而易见的问题是：
date（日期）类型与时区没有联系，而 time（时间）类型却有或可以有．
缺省的时区用一个整数常量表示与GMT/UTC的偏移（时差）．
然而，现实世界的时区只有在与时间和日期都关联时才有意义，因为时间偏移量（时差）可能因为实行类似夏时制这样的制度而在一年里有所变化．
为了克服这些困难， Postgres 只将日期和时间类型（同时包含日期和时间）与时区相关联，并且假设任何类型的当地时间只包含日期或时间．另外，时区的支持从操作系统的时区功能中引入，这样就可以处理夏时制或其他可知的因素．
Postgres 从操作系统获得介于1902年和2038年的日期的时区支持（近乎是典型的Unix类系统的日期限制）．在这个范围之外的日期都假设为用全球统一时间（Universal Coordinated Time，UTC）声明和使用。
在系统内部,所有日期和时间都是用全球统一时间(UTC)格式存储，也就是通常所说的格林威治时间（GMT）．时间在发给客户前端前由数据库服务器转换成本地时间，因而缺省的时区是服务器的时区．
有几种影响时区特性的方法：
直接在postmaster启动时后端使用TZ环境变量作为缺省时区．
客户端使用libpq时用 PGTZ 环境变量将时区信息传递给后端．
SQL 命令 SET TIME ZONE 可以给会话设置时区．
如果使用了非法的时区，时区变为GMT（在大多数系统上）。
注意：如果设置了编译选项 USE_AUSTRALIAN_RULES，那么 EST 代表澳大利亚东部标准时间，（ Australia Eastern Std Time）与UTC有 +10:00 小时的偏移量。
内部
Postgres 用 Julian 记日法用于所有日期/时间计算。如果假设一年的长度是365.2425天时，这个方法可以很精确地预计/计算从4713BC（公元前4713年）到很久的未来的任意一天的日期。
19世纪以前的日期传统（历法）对一些趣味读物有意义，但是在我们这里好象没有充分的理由把它们编码入日期/时间控制器里面去。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
布尔类型
Postgres 支持用 bool 作为 SQL3 布尔数据类型． bool只能有两个状态之一： '真'('True') 或 '假'('False')．第三种状态，'未知'('Unknow')，在 SQL3中没有建议，因而也没有实现；NULL是一个有效的替代品．bool可用于任何布尔表达式，并且布尔表达式的结果也是布尔类型兼容的．
bool 使用1字节存储空间．
表 3-17. Postgres 布尔类型
状态 输出 输入
True 't' TRUE, 't', 'true', 'y', 'yes', '1'
False 'f' FALSE, 'f', 'false', 'n', 'no', '0'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
几何类型
几何类型表示二维的平面物体．最基本的类型：点，是其他类型的基础．
表 3-18. Postgres 几何类型
几何类型 存储空间 表达 描述
point 16 bytes (x,y) 空间中一点
line 32 bytes ((x1,y1),(x2,y2)) (无穷)直线
lseg 32 bytes ((x1,y1),(x2,y2)) 线段
box 32 bytes ((x1,y1),(x2,y2)) 长方形
path 4+32n bytes ((x1,y1),...) 闭合路径(与多边形类似)
path 4+32n bytes [(x1,y1),...] 开放路径
polygon 4+32n bytes ((x1,y1),...) 多边形(与闭合路径相似)
circle 24 bytes <(x,y),r> 圆(圆心和半径)
一系列丰富的函数和操作符可用来进行各种几何计算，如度量，转换，旋转和计算相交等。
Point（点）
点是集合类型的基本二维构造单位。
用下面语法描述 point：
( x , y )
x , y
这里的参数是
x
是用一个浮点数表示的点的 x 坐标。
y
y 是用一个浮点数表示的点的 y 坐标。
线段
线段(lseg)是用一对点来代表的．
lseg 用下面语法声明：
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
这里的参数是
(x1,y1), (x2,y2)
是线段的端点。
Box（方）
方是用一对对角点来表示的。
box 用下面语法声明：
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
这里的参数是
(x1,y1), (x2,y2)
是一对对角点。
方的输出使用第一种语法．在输入时将按先左下角后右上角的顺序重新排列．你也可以输入其他的一对对角点．但输入时将按照先左下角后右上角的顺序重排并存储．
Path（路径）
路径由一系列连接的点组成．路径可能是"开放"的，也就是第一个点和最后一个点没有连接，也可能是"闭合"的，这时第一个和最后一个点连接起来．我们提供了函数 popen(p) 和 pclose(p)来强制路径是开放的还是闭合的，可以用函数 isopen(p) 和 isclosed(p) 来在查询中选择是那种．
path 用下面语法声明：
( ( x1 , y1 ) , ... , ( xn , yn ) )
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
这里的参数是
(x,y)
组成路径的线段的端点。前面的（"["）表明一个开放的路径，而前面的（"("）表明一个闭合的路径。
路径的输出使用第一种语法输出．注意 Postgres v6.1以前的版本路径的格式是由一个圆括号开始――一个"关闭标志"，一个点的计数然后后面是点的列表，最后是一个圆括号结束．有一个内建的函数upgradepath 可用于从6.1前版本输出和重载路径，将其转换成新的。
Polygon（多边形）
多边形由一系列点代表．多边形可以认为与闭合路径一样，但是存储方式不一样而且有自己的一套支持过程/函数．
polygon 用下列语法声明：
( ( x1 , y1 ) , ... , ( xn , yn ) )
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
这里的参数是
(x,y)
组成多边形边界的线段的端点。
多边形输出使用第一种语法．要注意在 Postgres 版本v6.1前，多边形的格式是一个圆括号开头，后面是点的x坐标列表，然后是点的y坐标列表，最后是一个圆括号结束．用内建的函数upgradepoly 可用于从6.1前版本输出和重载多边形，将其转换成新的。
Circle（圆）
圆由一个圆心和一个半径代表．
circle 用下面语法表示：
< ( x , y ) , r >
( ( x , y ) , r )
( x , y ) , r
x , y , r
这里的参数是
(x,y)
圆心
r
圆的半径
圆的输出用第一个格式．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
IP 版本 4 网络和主机地址
cidr 类型用于存储用 CIDR（Classless Inter-Domain Routing）符号声明的网络． inet 类型用一种形式简单的变种来表示简单的主机TCP/IP地址，并以此来存储以 CIDR 符号表示的主机和网络。
表 3-19. PostgresIP V4 类型
IPV4 类型 存储空间 描述 范围
cidr 变长 CIDR 网络 有效的 IPV4 CIDR 字块
inet 变长 网络和主机 有效的 IPV4 CIDR 字块
CIDR
cidr 类型声明一个 CIDR 网络． 说明一个无类（classless）的网络的格式是 "x.x.x.x/y" 这里 "x.x.x.x" 网络地址 而 "/y" 网络掩码中1的个数．如果 "/y" 部分没有指明，那么掩码部分用旧的有类的网络分类假设进行计算，但要求输入的数据已经包括了确定掩码的所需的所有8进制位．
下面是些例子：
表 3-20. PostgresIP 类型举例
CIDR 输入 CIDR 显示
192.168.1 192.168.1/24
192.168 192.168.0/24
128.1 128.1/16
128 128.0/16
128.1.2 128.1.2/24
10.1.2 10.1.2/24
10.1 10.1/16
10 10/8
inet
inet 类型设计用来在一个数据域里存放所有主机信息，包括其所在的CIDR风格的子网．要注意如果你想正确存放CIDR网络，你应该使用cidr 类型．inet 类型类似cidr 类型，只是它的主机部分可以是非零．可以用函数从数据域中将各种元素提取出来．
该函数的输入格式是 x.x.x.x/y 这里 x.x.x.x 是互联网主机， y 是网络掩码的位数．如果 /y 部分未填，当作/32．输出时，如果 /y 部分为 /32，将不会打印出来．只要不填"/y"部分，该类型可以直接作为主机类型使用．（？？This allows the type to be used as a straight host type by just leaving of the bits part. ）
--------------------------------------------------------------------------------
