PostgreSQL应用手册 第三章

 

第三章 数据类型


▓ 数值类型　

▓ 货币类型　

▓ 字符类型　

▓ 日期/时间类型　

▓ 布尔类型　

▓ 几何类型　

▓ IP版本4网络和主机类型　



▲ 描述Postgres内建的可用数据类型

Postgres有着丰富的数据类型可用，用户可以使用DEFINE TYPE命令为Postgres增加新的数据类型，在数据类型这部分，随后的各节将讨论SQL标准的兼容性，移植问题和使用问题。一些Postgres类型直接与SQL92-兼容类型相对应，其它一些SQL92语法定义的数据类型直接映射为Postgres内部的数据类型，许多内建的数据类型有明确的外部格式。但是，有一些数据类型或者是Postgres特有的，如开放和闭合路径，或者是有几种可能格式的类型，如日期和时间类型。　





※注意：cidr和inet用于处理任IP类型数据，但目前只能处理ipv4的数据。在以后的版本里我们将将所有对ipv4的支持增加到对ipv6的支持中。





Postgres拥有开发ORDBMS应用的首要特性。除了符合SQL3规范外，我们还支持很大一部分SQL92规范。尽管我们尽可能地遵循SQL92规范，但该规范里有一些方面欠缺考虑，在后继的规范中不可能继续存在。Postgres不会为这些特性花费太多的时间；因为这些方面主要发生在很少使用或语意含混的场合，典型的用户很少能碰到它们。
　
绝大多数与基本类型（如：整数和浮点数）对应的输入输出函数都会做错误检查。出于改善执行性能的考虑，一些操作符和函数（如加法和乘法）并不做运行时的错误检查。因而在一些系统上的对某些数据类型的数字操作会导致轻微的数值溢出或下溢。　

要注意的是一些输入输出函数是不可逆的。也就是说，一个输出函数的输出结果与输入的数据相比可能会丢失精度。　


※注意：浮点数可以保持该类型的最高固有精度（一般来说,双精度是15位，4字节浮点数是6位）。其他依赖浮点的数据类型（如，几何类型）也有相似精度。



▲ 数值类型

数值类型由2或4字节的整数以及4或8字节的浮点数和固定精度小数组成。




数值类型对应有一套完整的数学操作符和函数。相关信息请参考数字操作符和数学函数。　

int8类型因为是要靠编译器来支持的，因而可能无法在所有平台上得到支持。　


● Serial（序列）类型

serial类型是Postgres用其他现有的类型构造出来的一种特殊的类型。典型的应用是创建表的唯一标识，在当前的实现中，下面一句话：　

CREATE　TABLE　tablename　(colname　SERIAL);


等价于下面几句话:



※注意　
创建为serial类型的隐含序列号在删除表时不会自动删除　


在删除一个包含serial类型的表的时候，隐含的支持serial的序列号不会被自动删除。因此下面顺序执行的命令将是无效的：




除非显式地使用DROP SEQUENCE命令，序列号不会被删掉而一直在数据库里面。　




● 货币类型

过时的类型：money（货币）现在已经过时，用numeric或decimal取代它。
money类型支持US-类的固定小数点位置的货币数字。如果编译Postgres时使用了USE_LOCALE编译选项，货币类型将使用为locale(7)定义的货币习惯。





● 字符类型

SQL92定义了两种基本的字符类型：char和varchar。Postgres支持这些类型，并且还支持更通用的text类型，该类型不象varchar一样必须要定义一个数据域的上限。　





另外还有一种定长字符类型name类型，该类型只有一个用途，就是提供给Postgres一个特别的类型用于内部名字。该类型通常不是给一般用户使用的，该类型长度当前定为32字符长，但可以使用NAME DATALEN重新定义，这个（变量）是在编译的时候设置的，在以后的版本可能会改变。　






● 日期/时间类型

PostgreSQL支持SQL中所有的日期和时间类型。





※注意：为了保证和早期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　类型可能的输入方式。　








▲ time（时间）

下面是有效的time（时间）输入　





△ 带时区时间

这个类型是SQL92定义的，但是该定义显示出非常基本的不足，使得这个类型几乎无用。在多数情况下，date，time和timestamp的组合应该能提供任何应用所需要的日期/时间功能的全部范围。　

带时区时间接受所有对time类型也合法的输入，附加一个合法的时区，如下：　




参考Postgres时区输入获取时区的更多例子。　


△ timestamp（时标）

有效的timestamp类型的输入包含一个日期和一个时间的连接，后面跟着可选的AD或BC，再后面跟着可选的时区。（参阅下面。）因此　

1999-01-08　04:05:06　-8:00

是一个有效的timestamp值，它是ISO-兼容的。另外，已经广泛使用的格式　

January　8　04:05:06　1999　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为方便期间同样还支持几个特殊常量。　





‘now’在该值被插入时解释，‘current’在每次检索该值时被解释。所以你可能会在大多数应用里使用‘now’。（当然你真的想用CURRENT_TIMESTAMP的话也行，它等效于‘now’。）　


● 日期/时间输出

使用SETDateStyle，输出格式可以设成四种风格之一：ISO-8601，SQL（Ingres），传统的　Postgres，和German风格。缺省是ISO格式。





日期和时间风格的输出当然只是对应上面例子的日期或者时间部分。　

SQL　风格有欧洲和非欧洲(US)变种，决定月份后面是日期还是正相反。（参阅上面日期/时间输入部分，看看这个设置是如何影响对输入值的解释。）　





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字节存储空间。





● 几何类型

几何类型表示二维的平面物体，最基本的类型：点，是其他类型的基础。　



一系列丰富的函数和操作符可用来进行各种几何计算，如度量，转换，旋转和计算相交等。　


● 点（point）

点（point）是一个2维坐标的的基本单位，一般是以坐标x和y来代表。
语法如下：

（x，y ）
或
x，y

以上的参数

x是用浮点数（float）表示的点的x坐标。
y是用浮点数（float）表示的点的y坐标。

例如：‘（3.1，4.2）’就代表在x轴为3.1和y轴为4.2的一个交点。


△ 线段

线段(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)　

组成路径的线段的端点。前面的（"["）表明一个开放的路径，而前面的（"("）表明一个闭合的路径。　

路径的输出使用第一种语法输出，注意Postgresv6.1以前的版本路径的格式是由一个圆括号开始一个“关闭标志”，一个点的计数然后后面是点的列表，最后是一个圆括号结束．有一个内建的函数upgradepath可用于从6.1前版本输出和重载路径，将其转换成新的。　

所使用的表示法如下：

((x1,y1)，…，(xn,yn)) 或
[(x1,y1)，…，(xn,yn)] 或
(x1,y1)，…，(xn,yn) 或
(x1,y1，…，xn,yn) 或
x1,y1 ，…，xn,yn 或

所代的参数是一系列的点坐标如下格式：

(xn,yn)是一系列的点（POINT）所组合而成的。

在表示法之中，若包括整个路径的中括号是“[”和“]”是代表一个开放（open）路径，而小括号“(”和“)”就代表是一个闭合的路径。

例如:‘((1,1)，(2,2)，(3,3))’就代表着一个闭合的路径，而‘[(1,1)，(2,2)，(3,3)]’就代表着一个开放的路径。


● 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　
圆的半径　
圆的输出用第一个格式。　





网络格式形态主要有以下三种：INET、CIDR、MACADDR，INET是用来存放一个网络的IP地址，例如：192.163.1.1，CIDR则是用来存放着网络的实体位址。如表3-19所示为各种形态的比较，以下有详细的介绍。


● INET网络位址形态

代表着一个网络的IP位址或是一个子网络，格式为

x.x.x.x 或
x.x.x.x/y

两种表示法，第一种代表一个主机（host），第二种代表一个子网络。

x.x.x.x表示一个网络的IP主机，而y代表一个子网络的netmask，我们以一个数字代表，若为32代表一个主机。


● CIDR网络位址形态

cidr跟inet是类同的数据型别，最大的不同在于inet可以去掉右边的netmask代表一个主机，但是cidr一定要有netmask，范例如下所示：

格式为

x.x.x.x/y

x.x.x.x表示一个网络的IP主机，而y 代表子网络的netmask，我们以一个数字代表， 若为32代表一个主机。


范例：

如表3-20为所有的输入和输出的格式。




● MACADDR网络位址数据型态

macaddr主要是代表着一个网络卡的实体位址，所使用的输入格式相当的多，范例如表3-21所示：




输出的格式以最后一种表示法‘08:00:2b:01:02:03’格式为主。



To be continued.......

出处：南方Linux