名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数，序列类型，类似于MySQL中的auto_increment	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

1. 整数类型

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

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

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

2. 任意精度数字

类型numeric可以存储非常多位的数字。我们特别建议将它用于货币金额和其它要求计算准确的数量。numeric值的计算在可能的情况下会得到准确的结果，例如加法、减法、乘法。不过，numeric类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。

在随后的内容里，我们使用了下述术语：一个numeric的precision（精度）是整个数中有效位的总数，也就是小数点两边的位数。numeric的scale（刻度）是小数部分的数字位数，也就是小数点右边的部分。因此数字 23.5141 的精度为6而刻度为4。可以认为整数的刻度为零。

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

NUMERIC(precision, scale)

1	NUMERIC(precision, scale)

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

NUMERIC(precision)

1	NUMERIC(precision)

选择比例为 0 。如果使用

NUMERIC

NUMERIC

创建一个列时不使用精度或比例，则该列可以存储任何精度和比例的数字值，并且值的范围最多可以到实现精度的上限。一个这种列将不会把输入值转化成任何特定的比例，而带有比例声明的numeric列将把输入值转化为该比例（SQL标准要求缺省的比例是 0，即转化成整数精度。我们觉得这样做有点没用。如果你关心移植性，那你最好总是显式声明精度和比例）。

注意

显式指定类型精度时的最大允许精度为 1000，没有指定精度的NUMERIC受到表 8.2中描述的限制所控制。

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

数字值在物理上是以不带任何前导或者后缀零的形式存储。因此，列上声明的精度和比例都是最大值，而不是固定分配的（在这个方面，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)

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)

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的较旧版本生成的输出兼容，并允许降低输出精度，可以使用extra_float_digits参数选择四舍五入的十进制输出。将值设置为0将恢复以前的默认值，即将值四舍五入为6（对于float4）或15（对于float8）个有效的十进制数字。设置负值会进一步减少位数。例如-2会将输出分别舍入到4或13位数字。

设置extra_float_digits位任何大于 0 的值将选择最短精确格式。

注意

需要更精确值的应用需要设置extra_float_digits为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。

4. 序数类型

注意

这一节描述了PostgreSQL特有的创建一个自增列的方法。另一种方法是使用SQL标准的标识列特性，它在CREATE TABLE中描述。

smallserial、serial和bigserial类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的AUTO_INCREMENT属性）。在目前的实现中，下面一个语句：

CREATE TABLE tablename (
    colname SERIAL
);

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 BY tablename.colname;

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 BY tablename.colname;

因此，我们就创建了一个整数列并且把它的缺省值安排为从一个序列发生器取值。应用了一个NOT NULL约束以确保空值不会被插入（在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的值，但这个不是自动发生的）。最后，该序列被标记为“属于”该列，这样当列或表被删除时该序列也会被删除。

注意

因为smallserial、serial和bigserial是用序列实现的，所以即使没有删除过行，在出现在列中的序列值可能有“空洞”或者间隙。如果一个从序列中分配的值被用在一行中，即使该行最终没有被成功地插入到表中，该值也被“用掉”了。例如，当插入事务回滚时就会发生这种情况。更多信息参见第 9.17 节中的nextval()。

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

类型名serial和serial4是等效的：两个都创建integer列。类型名bigserial和serial8也一样，只不过它们创建一个 bigint列。如果你预计在表的生存期中使用的标识符数目超过 231 个，那么你应该使用bigserial。类型名smallserial和serial2也以相同方式工作，只不过它们创建一个smallint列。

为一个serial列创建的序列在所属的列被删除的时候自动删除。你可以在不删除列的情况下删除序列，但是这会强制删除该列的默认值表达式。

货币类型

money 类型存储带有固定小数精度的货币金额。小数的精度由数据库的参数lc_monetary设置决定。表中展示的范围假设有两个小数位。可接受的输入格式很多，包括整数和浮点数文字，以及常用的货币格式，如'$1,000.00'。输出通常是最后一种形式，但和区域相关。

numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。

名字	存储容量	描述	范围
money	8 字节	货币金额	-92233720368547758.08 到 +92233720368547758.07

由于这种数据类型的输出是区域敏感的，因此将money数据装入到一个具有不同lc_monetary设置的数据库是不起作用的。为了避免这种问题，在恢复一个转储到一个新数据库中之前，应确保新数据库的lc_monetary设置和被转储数据库的相同或者具有等效值。

数据类型numeric、int和bigint的值可以被造型成money。从数据类型real和double precision的转换可以通过先造型成numeric来实现，例如：

SELECT '12.34'::float8::numeric::money;

1	SELECT '12.34'::float8::numeric::money;

但是，我们不推荐这样做。浮点数不应该被用来处理货币，因为浮点数可能会有圆整错误。

一个money值可以在不损失精度的情况下被造型成numeric。转换到其他类型可能会丢失精度，并且必须采用两个阶段完成：

SELECT '52093.89'::money::numeric::float8;

1	SELECT '52093.89'::money::numeric::float8;

一个money值被一个整数值除的除法结果会被截去分数部分。要得到圆整的结果，可以除以一个浮点值，或者在除法之前把money转换成numeric然后在除法之后转回money（如果要避免精度丢失的风险则后者更好）。当一个money值被另一个money值除时，结果是double precision（即一个纯数字，而不是金额），在除法中货币单位被约掉了。

字符类型

下表列出了 PostgreSQL 所支持的字符类型：

序号	名字 & 描述
1	character varying(n), varchar(n)变长，有长度限制
2	character(n), char(n)f定长,不足补空白
3	text 变长，无长度限制

SQL定义了两种基本的字符类型： character varying(*n*)和character(*n*)，其中n是一个正整数。两种类型都可以存储最多n个字符长的串。试图存储更长的串到这些类型的列里会产生一个错误，除非超出长度的字符都是空白，这种情况下该串将被截断为最大长度（这个看上去有点怪异的例外是SQL标准要求的）。如果要存储的串比声明的长度短，类型为character的值将会用空白填满；而类型为character varying的值将只是存储短些的串。

如果我们明确地把一个值造型成character varying(*n*)或者character(*n*)，那么超长的值将被截断成n个字符，而不会抛出错误（这也是SQL标准的要求）。

varchar(*n*)和char(*n*)的概念分别是character varying(*n*)和character(*n*)的别名。没有长度声明词的character等效于character(1)。如果不带长度说明词使用character varying，那么该类型接受任何长度的串。后者是一个PostgreSQL的扩展。

另外，PostgreSQL提供text类型，它可以存储任何长度的串。尽管类型text不是SQL标准，但是许多其它 SQL 数据库系统也有它。

类型character的值物理上都用空白填充到指定的长度n，并且以这种方式存储和显示。不过，拖尾的空白被当作是没有意义的，并且在比较两个 character类型值时不会考虑它们。在空白有意义的排序规则中，这种行为可能会产生意料之外的结果，例如SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)会返回真（即便C区域会认为一个空格比新行更大）。当把一个character值转换成其他字符串类型之一时，拖尾的空白会被移除。请注意，在character varying和text值里，结尾的空白语意上是\有含义的，并且在使用模式匹配（如LIKE和正则表达式）时也会被考虑。

这些类型的存储需求是 4 字节加上实际的字串，如果是 character 的话再加上填充的字节。长的字串将会自动被系统压缩，因此在磁盘上的物理需求可能会更少些。长的数值也会存储在后台表里面，这样它们就不会干扰对短字段值的快速访问。不管怎样，允许存储的最长字串大概是 1 GB。（允许在数据类型声明中出现的的 n 的最大值比这还小。修改这个行为没有甚么意义，因为在多字节编码下字符和字节的数目可能差别很大。如果你想存储没有特定上限的长字串，那么使用 text 或者没有长度声明词的 character varying，而不要选择一个任意长度限制。）一个短串（最长126字节）的存储要求是1个字节外加实际的串，该串在character情况下包含填充的空白。长一些的串在前面需要4个字节而不是1个字节。长串会被系统自动压缩，这样在磁盘上的物理需求可能会更少。非常长的值也会被存储在背景表中，这样它们不会干扰对较短的列值的快速访问。在任何情况下，能被存储的最长的字符串是1GB（数据类型定义中n能允许的最大值比这个值要小。修改它没有用处，因为对于多字节字符编码来说，字符的数量和字节数可能完全不同。如果你想要存储没有指定上限的长串，使用text或没有长度声明的character varying，而不是给出一个任意长度限制）。

提示

这三种类型之间没有性能差别，只不过是在使用填充空白的类型的时候需要更多存储尺寸，以及在存储到一个有长度约束的列时需要少量额外CPU周期来检查长度。虽然在某些其它的数据库系统里，character(*n*)有一定的性能优势，但在PostgreSQL里没有。事实上，character(*n*)通常是这三种类型之中最慢的一个，因为它需要额外的存储开销。在大多数情况下，应该使用text或者character varying。

请参考第 4.1.2.1 节获取关于串文本的语法的信息，以及参阅第 9 章获取关于可用操作符和函数的信息。数据库的字符集决定用于存储文本值的字符集；有关字符集支持的更多信息，请参考第 23.3 节。

例 8.1. 使用字符类型

CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)

  a   | char_length
------+-------------
 ok   |           2

CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES ('ok');
INSERT INTO test2 VALUES ('good      ');
INSERT INTO test2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
SELECT b, char_length(b) FROM test2;

   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5

CREATE TABLE test1 (a character(4));

INSERT INTO test1 VALUES ('ok');

SELECT a, char_length(a) FROM test1; -- (1)

a | char_length

------+-------------

ok | 2

CREATE TABLE test2 (b varchar(5));

INSERT INTO test2 VALUES ('ok');

INSERT INTO test2 VALUES ('good ');

INSERT INTO test2 VALUES ('too long');

ERROR: value too long for type character varying(5)

INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation

SELECT b, char_length(b) FROM test2;

b | char_length

-------+-------------

ok | 2

good | 5

too l | 5

在PostgreSQL里另外还有两种定长字符类型，在表 8.5里显示。 name类型只\用于在内部系统目录中存储标识符并且不是给一般用户使用的。该类型长度当前定为 64 字节（63 可用字符加结束符）但在C源代码应该使用常量 NAMEDATALEN引用。这个长度是在编译的时候设置的（因而可以为特殊用途调整），缺省的最大长度在以后的版本可能会改变。类型"char"（注意引号）和 char(1)是不一样的，它只用了一个字节的存储空间。它在系统内部用于系统目录当做简化的枚举类型用。

表 8.5. 特殊字符类型

名字	存储尺寸	描述
`"char"`	1字节	单字节内部类型
`name`	64字节	用于对象名的内部类型

日期/时间类型

下表列出了 PostgreSQL 支持的日期和时间类型。

名字	存储空间	描述	最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间(无时区)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间，有时区	4713 BC	294276 AD	1 毫秒 / 14 位
date	4 字节	只用于日期	4713 BC	5874897 AD	1 天
time [ (p) ] [ without time zone ]	8 字节	只用于一日内时间	00:00:00	24:00:00	1 毫秒 / 14 位
time [ (p) ] with time zone	12 字节	只用于一日内时间，带时区	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [ fields ] [ (p) ]	12 字节	时间间隔	-178000000 年	178000000 年	1 毫秒 / 14 位

注意

SQL要求只写timestamp等效于timestamp without time zone，并且PostgreSQL鼓励这种行为。timestamptz被接受为timestamp with time zone的一种简写，这是一种PostgreSQL的扩展。

time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界。p允许的范围是从 0 到 6。

interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

YEAR TO MONTH

DAY TO HOUR

DAY TO MINUTE

DAY TO SECOND

HOUR TO MINUTE

HOUR TO SECOND

MINUTE TO SECOND

注意如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。

类型time with time zone是 SQL 标准定义的，但是该定义显示出了一些会影响可用性的性质。在大多数情况下， date、time、timestamp without time zone和timestamp with time zone的组合就应该能提供任何应用所需的全范围的日期/时间功能。

1. 日期/时间输入

日期和时间的输入可以接受几乎任何合理的格式，包括 ISO 8601、SQL-兼容的、传统POSTGRES的和其他的形式。对于一些格式，日期输入里的日、月和年的顺序会让人混淆，并且支持指定所预期的这些域的顺序。把DateStyle参数设置为MDY，就是选择“月－日－年”的解释，设置为DMY就是 “日－月－年”，而YMD是 “年－月－日”。

PostgreSQL在处理日期/时间输入上比SQL标准要求的更灵活。参阅附录 B获取关于日期/时间输入的准确的分析规则和可识别文本域，包括月份、星期几和时区。

请记住任何日期或者时间的文字输入需要由单引号包围，就象一个文本字符串一样。参考第 4.1.2.7 节获取更多信息。SQL要求下面的语法

type [ (p) ] 'value'

1	type [ (p) ] 'value'

其中p是一个可选的精度声明，它给出了在秒域中的小数位数目。精度可以被指定给time、timestamp和interval类型，并且可以取从0到6的值。这允许前文所述的值。如果在一个常数声明中没有指定任何精度，它将默认取文字值的精度（但不能超过6位）。

1.1. 日期

表 8.10显示了date类型可能的输入方式。

表 8.10. 日期输入

例子	描述
1999-01-08	ISO 8601; 任何模式下的1月8日（推荐格式）
January 8, 1999	在任何`datestyle`输入模式下都无歧义
1/8/1999	`MDY`模式中的1月8日；`DMY`模式中的8月1日
1/18/1999	`MDY`模式中的1月18日；在其他模式中被拒绝
01/02/03	`MDY`模式中的2003年1月2日； `DMY`模式中的2003年2月1日； `YMD`模式中的2001年2月3日
1999-Jan-08	任何模式下的1月8日
Jan-08-1999	任何模式下的1月8日
08-Jan-1999	任何模式下的1月8日
99-Jan-08	`YMD`模式中的1月8日，否则错误
08-Jan-99	1月8日，除了在`YMD`模式中错误
Jan-08-99	1月8日，除了在`YMD`模式中错误
19990108	ISO 8601; 任何模式中的1999年1月8日
990108	ISO 8601; 任何模式中的1999年1月8日
1999.008	年和一年中的日子
J2451187	儒略日期
January 8, 99 BC	公元前99年

1.2. 时间

当日时间类型是time [ (*p*) ] without time zone和time [ (*p*) ] with time zone。只写time等效于time without time zone。

这些类型的有效输入由当日时间后面跟着可选的时区组成（参阅表 8.11和表 8.12）。如果在time without time zone的输入中指定了时区，那么它会被无声地忽略。你也可以指定一个日期但是它会被忽略，除非你使用了一个涉及到夏令时规则的时区，例如America/New_York。在这种情况下，为了判断是应用了标准时间还是夏令时时间，要求指定该日期。适当的时区偏移被记录在time with time zone值中。

表 8.11. 时间输入

例子	描述
`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
`04:05:06.789-8`	ISO 8601
`04:05:06-08:00`	ISO 8601
`04:05-08:00`	ISO 8601
`040506-08`	ISO 8601
`04:05:06 PST`	缩写指定的时区
`2003-04-12 04:05:06 America/New_York`	全名指定的时区

表 8.12. 时区输入

例子	描述
`PST`	缩写（太平洋标准时间）
`America/New_York`	完整时区名
`PST8PDT`	POSIX风格的时区声明
`-8:00`	PST的ISO-8601偏移
`-800`	PST的ISO-8601偏移
`-8`	PST的ISO-8601偏移
`zulu`	UTC的军方缩写
`z`	`zulu`的短形式

参考第 8.5.3 节可以了解如何指定时区。

1.3. 时间戳

时间戳类型的有效输入由一个日期和时间的串接组成，后面跟着一个可选的时区，一个可选的AD或者BC（另外，AD/BC可以出现在时区前面，但这个顺序并非最佳）。因此：

1999-01-08 04:05:06

1	1999-01-08 04:05:06

和：

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

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

都是有效的值，它遵循ISO 8601 标准。另外，使用广泛的格式：

January 8 04:05:06 1999 PST

1	January 8 04:05:06 1999 PST

也被支持。

SQL标准通过“+”或者“-”符号的存在以及时间后面的时区偏移来区分timestamp without time zone和timestamp with time zone文字。因此，根据标准，

TIMESTAMP '2004-10-19 10:23:54'

1	TIMESTAMP '2004-10-19 10:23:54'

是一个timestamp without time zone，而

TIMESTAMP '2004-10-19 10:23:54+02'

1	TIMESTAMP '2004-10-19 10:23:54+02'

是一个timestamp with time zone。PostgreSQL从来不会在确定文字串的类型之前检查其内容，因此会把上面两个都看做是 timestamp without time zone。因此要保证把上面的文字当作timestamp with time zone看待，就要给它正确的显式类型：

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

1	TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

如果一个文字已被确定是timestamp without time zone，PostgreSQL将不声不响忽略任何其中指出的时区。即，结果值是从输入值的日期/时间域衍生出来的，并且没有就时区进行调整。

对于timestamp with time zone，内部存储的值总是 UTC （全球统一时间，以前也叫格林威治时间GMT）。如果一个输入值有明确的时区声明，那么它将用该时区合适的偏移量转换成 UTC。如果在输入串里没有时区声明，那么它就被假设是在系统的TimeZone参数里的那个时区，然后使用这个 timezone时区的偏移转换成 UTC。

如果一个timestamp with time zone值被输出，那么它总是从 UTC 转换成当前的timezone时区，并且显示为该时区的本地时间。要看其它时区的时间，要么修改timezone，要么使用AT TIME ZONE构造（参阅第 9.9.3 节）。

在timestamp without time zone和timestamp with time zone之间的转换通常假设timestamp without time zone值应该以timezone本地时间的形式接受或者写出。为该转换指定一个不同的可以用AT TIME ZONE。

1.4. 特殊值

为了方便，PostgreSQL支持一些特殊日期/时间输入值，如表 8.13所示。这些值中infinity和-infinity被在系统内部以特殊方式表示并且将被原封不动地显示。但是其他的仅仅只是概念上的速写，当被读到的时候会被转换为正常的日期/时间值（特殊地，now及相关串在被读到时立刻被转换到一个指定的时间值）。在作为常量在SQL命令中使用时，所有这些值需要被包括在单引号内。

表 8.13. 特殊日期/时间输入

输入串	合法类型	描述
`epoch`	`date`, `timestamp`	1970-01-01 00:00:00+00（Unix系统时间0）
`infinity`	`date`, `timestamp`	比任何其他时间戳都晚
`-infinity`	`date`, `timestamp`	比任何其他时间戳都早
`now`	`date`, `time`, `timestamp`	当前事务的开始时间
`today`	`date`, `timestamp`	今日午夜 (`00:00`)
`tomorrow`	`date`, `timestamp`	明日午夜 (`00:00`)
`yesterday`	`date`, `timestamp`	昨日午夜 (`00:00`)
`allballs`	`time`	00:00:00.00 UTC

下列SQL-兼容的函数可以被用来为相应的数据类型获得当前时间值： CURRENT_DATE、CURRENT_TIME、 CURRENT_TIMESTAMP、LOCALTIME、 LOCALTIMESTAMP。（参见第 9.9.4 节）。注意这些是SQL函数并且在数据输入串中不\被识别。

小心

虽然输入字符串 now、today、tomorrow和yesterday都可以在交互式 SQL 命令中使用，但当命令被保存以待稍后执行时，它们可能会有令人惊讶的行为，例如在准备的语句、视图和函数定义中。字符串可以被转换为特定的时间值，该值在变得过时后长时间继续使用。在这种上下文中使用某一种SQL函数代替。例如， CURRENT_DATE + 1 比 'tomorrow'::date更安全。

2. 日期/时间输出

时间/日期类型的输出格式可以设成四种风格之一： ISO 8601、SQL（Ingres）、传统的POSTGRES（Unix的date格式）或 German 。缺省是ISO格式（ISO标准要求使用 ISO 8601 格式。ISO输出格式的名字是历史偶然）。表 8.14显示了每种输出风格的例子。date和time类型的输出通常只有日期或时间部分和例子中一致。不过，POSTGRES风格输出的是ISO格式的只有日期的值。

表 8.14. 日期/时间输出风格

风格声明	描述	例子
`ISO`	ISO 8601, SQL标准	`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`

注意

ISO 8601指定使用大写字母T来分隔日期和时间。PostgreSQL在输入上接受这种格式，但是在输出时它采用一个空格而不是T，如上所示。和一些其他数据库系统一样，这是为了可读性以及与RFC 3339的一致性。

SQL和POSTGRES风格中，如果DMY域顺序被指定，“日”将出现在“月”之前，否则“月”出现在“日”之前（有关该设置如何影响输入值的解释，请参考第 8.5.1 节）。表 8.15给出了例子。

表 8.15. 日期顺序习惯

`datestyle`设置	输入顺序	例子输出
`SQL, DMY`	`日`/`月`/`年`	`17/12/1997 15:37:16.00 CET`
`SQL, MDY`	`月`/`日`/`年`	`12/17/1997 07:37:16.00 PST`
`Postgres, DMY`	`日`/`月`/`年`	`Wed 17 Dec 07:37:16 1997 PST`

日期/时间风格可以由用户使用SET datestyle命令选取，在postgresql.conf配置文件里的参数DateStyle设置或者在服务器或客户端的PGDATESTYLE环境变量里设置。

格式化函数to_char（见第 9.8 节）也可以作为一个更灵活的方式来格式化日期/时间输出。

3. 时区

时区和时区习惯不仅仅受地球几何形状的影响，还受到政治决定的影响。到了19世纪，全球的时区变得稍微标准化了些，但是还是易于遭受随意的修改，部分是因为夏时制规则。PostgreSQL使用广泛使用的 IANA (Olson) 时区数据库来得到有关历史时区规则的信息。对于未来的时间，我们假设关于一个给定时区的最新已知规则将会一直持续到无穷远的未来。

PostgreSQL努力在典型使用中与SQL标准的定义相兼容。但SQL标准在日期和时间类型和功能上有一些奇怪的混淆。两个显而易见的问题是：

尽管date类型与时区没有联系，而time类型却可以有。然而，现实世界的时区只有在与时间和日期都关联时才有意义，因为偏移（时差）可能因为实行类似夏时制这样的制度而在一年里有所变化。
缺省的时区会指定一个到UTC的数字常量偏移（时差）。因此，当跨DST边界做日期/时间算术时，我们根本不可能适应于夏时制时间。

为了克服这些困难，我们建议在使用时区的时候，使用那些同时包含日期和时间的日期/时间类型。我们不\建议使用类型 time with time zone （尽管PostgreSQL出于遗留应用以及与SQL标准兼容性的考虑支持这个类型）。 PostgreSQL假设你用于任何类型的本地时区都只包含日期或时间。

在系统内部，所有时区相关的日期和时间都用UTC存储。它们在被显示给客户端之前会被转换成由TimeZone配置参数指定的本地时间。

PostgreSQL允许你使用三种不同形式指定时区：

一个完整的时区名字，例如America/New_York。能被识别的时区名字被列在pg_timezone_names视图中（参见第 51.92 节）。PostgreSQL用广泛使用的 IANA 时区数据来实现该目的，因此相同的时区名字也可以在其他软件中被识别。
一个时区缩写，例如PST。这样一种声明仅仅定义了到UTC的一个特定偏移，而不像完整时区名那样指出整套夏令时转换规则。能被识别的缩写被列在pg_timezone_abbrevs视图中（参见第 51.91 节）。你不能将配置参数TimeZone或log_timezone设置成一个时区缩写，但是你可以在日期/时间输入值和AT TIME ZONE操作符中使用时区缩写。
除了时区名和缩写，PostgreSQL将接受POSIX-风格的时区规范，如同第 B.5 节所描述的。这个选项通常不优先用于指定时区，但是，如果没有合适的IANA时区条目，这可能是必要的。

简而言之，在缩写和全称之间是有不同的：缩写表示从UTC开始的一个特定偏移量，而很多全称表示一个本地夏令时规则并且因此具有两种可能的UTC偏移量。例如， 2014-06-04 12:00 America/New_York表示纽约本地时间的中午，这个特殊的日期是东部夏令时间（UTC-4）。因此2014-06-04 12:00 EDT 指定的是同一个时间点。但是2014-06-04 12:00 EST指定东部标准时间的中午（UTC-5），不管在那个日期夏令时是否生效。

更要命的是，某些行政区已经使用相同的时区缩写在不同的时间表示不同的 UTC 偏移量。例如，在莫斯科MSK在某些年份表示 UTC+3 而在另一些年份表示 UTC+4。 PostgreSQL 会根据在指定的日期它们到底表示什么（或者最近表示什么）来解释这种缩写。但是，正如上面的EST例子所示，这并不是必须和那一天的本地标准时间相同。

在所有情况下，时区名及其缩写都是大小写不敏感的（这是对PostgreSQL 8.2之前版本的一个修改，在这些版本中某些环境下时区名是大小写敏感的而在另外一些环境中却是大小写不敏感的）。

时区名和缩写都不是硬写在服务器中的，它们是从存储在安装目录下的.../share/timezone/和.../share/timezonesets/子目录中获取的（参见第 B.4 节）。

TimeZone配置参数可以在文件postgresql.conf中被设置，或者使用第 19 章中描述的任何一种标准方法设置。同时也有一些特殊的方法来设置它：

SQL命令SET TIME ZONE为会话设置时区。它是SET TIMEZONE TO的另一种拼写，它更加符合SQL的语法。
libpq客户端使用PGTZ环境变量来通过连接发送一个SET TIME ZONE命令给服务器。

4. 间隔输入

interval值可以使用下列语法书写：

[@] quantity unit [quantity unit...] [direction]

1	[@] quantity unit [quantity unit...] [direction]

其中quantity是一个数字（很可能是有符号的）； unit是毫秒、 millisecond、second、 minute、hour、day、 week、month、year、 decade、century、millennium 或者缩写或者这些单位的复数； direction可以是ago或者为空。At符号（@）是一个可选的噪声。不同单位的数量通过合适的符号计数被隐式地添加。ago对所有域求反。如果IntervalStyle被设置为postgres_verbose，该语法也被用于间隔输出。

日、小时、分钟和秒的数量可以不适用显式的单位标记指定。例如，'1 12:59:10'被读作'1 day 12 hours 59 min 10 sec'。同样，一个年和月的组合可以使用一个横线指定，例如'200-10'被读作'200年10个月'（这些较短的形式事实上是SQL标准唯一许可的形式，并且在IntervalStyle被设置为sql_standard时用于输出）。

间隔值也可以被写成 ISO 8601 时间间隔，使用该标准4.4.3.2小节的“带标志符的格式”或者4.4.3.3小节的“替代格式”。带标志符的格式看起来像这样：

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

1	P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

该串必须以一个P开始，并且可以包括一个引入当日时间单位的T。可用的单位缩写在表 8.16中给出。单位可以被忽略，并且可以以任何顺序指定，但是小于一天的单位必须出现在T之后。特别地，M的含义取决于它出现在T之前还是之后。

表 8.16. ISO 8601 间隔单位缩写

缩写	含义
Y	年
M	月（在日期部分中）
W	周
D	日
H	小时
M	分钟 (在时间部分中）
S	秒

如果使用替代格式：

P [ years-months-days ] [ T hours:minutes:seconds ]

1	P [ years-months-days ] [ T hours:minutes:seconds ]

串必须以P开始，并且一个T分隔间隔的日期和时间部分。其值按照类似于 ISO 8601日期的数字给出。

在用一个域声明书写一个间隔常量时，或者为一个用域声明定义的间隔列赋予一个串时，对于为标记的量的解释依赖于域。例如INTERVAL '1' YEAR被解读成1年，而INTERVAL '1'表示1秒。同样，域声明允许的最后一个有效域“右边”的域值会被无声地丢弃掉。例如书写INTERVAL '1 day 2:03:04' HOUR TO MINUTE将会导致丢弃秒域，而不是日域。

根据SQL标准，一个间隔值的所有域都必须由相同的符号，这样一个领头的负号将会应用到所有域；例如在间隔文字'-1 2:03:04'中的负号会被应用于日、小时、分钟和秒部分。PostgreSQL允许域具有不同的符号，并且在习惯上认为以文本表示的每个域具有独立的符号，因此在这个例子中小时、分钟和秒部分被认为是正值。如果IntervalStyle被设置为sql_standard，则一个领头的符号将被认为是应用于所有域（但是仅当没有额外符号出现）。否则将使用传统的PostgreSQL解释。为了避免混淆，我们推荐在任何域为负值时为每一个域都附加一个显式的符号。

在冗长的输入格式中，以及在更紧凑输入格式的某些域中，域值可以有分数部分；例如'1.5 week'或'01:02:03.45'。这样的输入被转换为合适的月数、日数和秒数用于存储。当这样会导致月和日中的分数时，分数被加到低序域中，使用的转换因子是1月=30日和1日=24小时。例如，'1.5 month'会变成1月和15日。只有秒总是在输出时被显示为分数。

表 8.17展示了一些有效interval输入的例子。

表 8.17. 间隔输入

例子	描述
`1-2`	SQL标准格式：1年2个月
`3 4:05:06`	SQL标准格式：3日4小时5分钟6秒
`1 year 2 months 3 days 4 hours 5 minutes 6 seconds`	传统Postgres格式：1年2个月3日4小时5分钟6秒钟
`P1Y2M3DT4H5M6S`	“带标志符的”ISO 8601 格式：含义同上
`P0001-02-03T04:05:06`	ISO 8601 的“替代格式”：含义同上

在内部，interval值被存储为months、days以及seconds。之所以这样做是因为一个月中的天数是变化的，并且在涉及到夏令时调整时一天可以有23或者25个小时。months以及days域是整数，而seconds域可以存储分数。因为区间通常是从常量字符串或者timestamp减法创建而来，这种存储方法在大部分情况下都很好，但是也可能导致预料之外的结果：

SELECT EXTRACT(hours from '80 minutes'::interval);
 date_part
-----------
         1

SELECT EXTRACT(days from '80 hours'::interval);
 date_part
-----------
         0

SELECT EXTRACT(hours from '80 minutes'::interval);

date_part

-----------

SELECT EXTRACT(days from '80 hours'::interval);

date_part

-----------

函数justify_days和justify_hours可以用来调整溢出其正常范围之外的days和hours。

5. 间隔输出

间隔类型的输出格式可以被设置为四种风格之一：sql_standard、postgres、postgres_verbose或iso_8601，设置方法使用SET intervalstyle命令。默认值为postgres格式。表 8.18展示了每种输出风格的例子。

如果间隔值符合SQL标准的限制（仅年-月或仅日-时间，没有正负值部分的混合），sql_standard风格为间隔文字串产生符合SQL标准规范的输出。否则输出将看起来像一个标准的年-月文字串跟着一个日-时间文字串，并且带有显式添加的符号以区分混合符号的间隔。

当DateStyle参数被设置为ISO时，postgres风格的输出匹配PostgreSQL 8.4版本以前的输出。

当DateStyle参数被设置为非ISO输出时，postgres_verbose风格的输出匹配PostgreSQL 8.4版本以前的输出。

iso_8601风格的输出匹配在ISO 8601标准的4.4.3.2小节中描述的“带标志符的格式”。

表 8.18. 间隔输出风格例子

风格声明	年-月间隔	日-时间间隔	混合间隔
`sql_standard`	1-2	3 4:05:06	-1-2 +3 -4:05:06
`postgres`	1 year 2 mons	3 days 04:05:06	-1 year -2 mons +3 days -04:05:06
`postgres_verbose`	@ 1 year 2 mons	@ 3 days 4 hours 5 mins 6 secs	@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago
`iso_8601`	P1Y2M	P3DT4H5M6S	P-1Y-2M3DT-4H-5M-6S

布尔类型

PostgreSQL 支持标准的 boolean 数据类型。

boolean 有"true"(真)或"false"(假)两个状态，第三种"unknown"(未知)状态，用 NULL 表示。

名称	存储格式	描述
boolean	1 字节	true/false

在SQL查询中，布尔常量可以表示为SQL关键字TRUE, FALSE,和 NULL.

boolean 类型的数据类型输入函数接受这些字符串表示“真”状态:

`true`
`yes`
`on`
`1`

下面这些表示“假” 状态:

`false`
`no`
`off`
`0`

这些字符串的唯一前缀也可以接受，例如t 或 n. 前端或尾部的空格将被忽略，并且大小写不敏感。

boolean类型的数据类型输出函数总是发出 t 或 f，如例 8.2所示。

例 8.2. 使用boolean类型

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est

CREATE TABLE test1 (a boolean, b text);

INSERT INTO test1 VALUES (TRUE, 'sic est');

INSERT INTO test1 VALUES (FALSE, 'non est');

SELECT * FROM test1;

a | b

---+---------

t | sic est

f | non est

SELECT * FROM test1 WHERE a;

a | b

---+---------

t | sic est

在SQL查询中优先使用关键字TRUE 和 FALSE来写布尔常数(SQL-兼容)。但是你也可以使用遵循第 4.1.2.7 节中描述的通用字符串文字常量句法的字符串来表达，例如'yes'::boolean.

注意语法分析程序会把TRUE 和 FALSE 自动理解为boolean类型，但是不包括NULL ，因为它可以是任何类型的。因此在某些语境中你也许要将 NULL 转化为显示boolean类型，例如NULL::boolean. 反过来，上下文中的字符串文字布尔值也可以不转换，当语法分析程序能够断定文字必定为boolean类型时。

枚举类型

枚举类型是一个包含静态和值的有序集合的数据类型。枚举类型的值大小写是敏感的，大写和小写不一样。

枚举（enum）类型是由一个静态、值的有序集合构成的数据类型。它们等效于很多编程语言所支持的enum类型。枚举类型的一个例子可以是一周中的日期，或者一个数据的状态值集合。

PostgtesSQL中的枚举类型类似于 C 语言中的 enum 类型。

与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy','OK');

1	CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy','OK');

创建一周中的几天，如下所示:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

1	CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

就像其他类型一样，一旦创建，枚举类型可以用于表和函数定义。

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy','OK');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy'),('a','OK'),('b','ok');

SELECT * FROM person WHERE current_mood = 'happy';

INSERT INTO person VALUES ('c','Ok');

postgres=# SELECT * FROM person;
 name | current_mood
------+--------------
 Moe  | happy
 a    | OK
 b    | ok
(3 rows)

postgres=# INSERT INTO person VALUES ('c','Ok');
ERROR:  invalid input value for enum mood: "Ok"
LINE 1: INSERT INTO person VALUES ('c','Ok');
                                       ^

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy','OK');

CREATE TABLE person (

name text,

current_mood mood

);

INSERT INTO person VALUES ('Moe', 'happy'),('a','OK'),('b','ok');

SELECT * FROM person WHERE current_mood = 'happy';

INSERT INTO person VALUES ('c','Ok');

postgres=# SELECT * FROM person;

name | current_mood

------+--------------

Moe | happy

a | OK

b | ok

(3 rows)

postgres=# INSERT INTO person VALUES ('c','Ok');

ERROR: invalid input value for enum mood: "Ok"

LINE 1: INSERT INTO person VALUES ('c','Ok');

1. 枚举类型的声明

枚举类型可以使用CREATE TYPE命令创建，例如：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

1	CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

一旦被创建，枚举类型可以像很多其他类型一样在表和函数定义中使用：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood
------+--------------
 Moe  | happy
(1 row)

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (

name text,

current_mood mood

);

INSERT INTO person VALUES ('Moe', 'happy');

SELECT * FROM person WHERE current_mood = 'happy';

name | current_mood

------+--------------

Moe | happy

(1 row)

2. 排序

一个枚举类型的值的排序是该类型被创建时所列出的值的顺序。枚举类型的所有标准的比较操作符以及相关聚集函数都被支持。例如：

INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
 name  | current_mood
-------+--------------
 Moe   | happy
 Curly | ok
(2 rows)

SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
 name  | current_mood
-------+--------------
 Curly | ok
 Moe   | happy
(2 rows)

SELECT name
FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
 name
-------
 Larry
(1 row)

INSERT INTO person VALUES ('Larry', 'sad');

INSERT INTO person VALUES ('Curly', 'ok');

SELECT * FROM person WHERE current_mood > 'sad';

name | current_mood

-------+--------------

Moe | happy

Curly | ok

(2 rows)

SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;

name | current_mood

-------+--------------

Curly | ok

Moe | happy

(2 rows)

SELECT name

FROM person

WHERE current_mood = (SELECT MIN(current_mood) FROM person);

name

-------

Larry

(1 row)

3. 类型安全性

每一种枚举数据类型都是独立的并且不能和其他枚举类型相比较。看这样一个例子：

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
    num_weeks integer,
    happiness happiness
);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');

CREATE TABLE holidays (

num_weeks integer,

happiness happiness

);

INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');

INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');

INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');

INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');

ERROR: invalid input value for enum happiness: "sad"

SELECT person.name, holidays.num_weeks FROM person, holidays

WHERE person.current_mood = holidays.happiness;

ERROR: operator does not exist: mood = happiness

如果你确实需要做这样的事情，你可以写一个自定义的操作符或者在查询中加上显式造型：

SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks
------+-----------
 Moe  |         4
(1 row)

SELECT person.name, holidays.num_weeks FROM person, holidays

WHERE person.current_mood::text = holidays.happiness::text;

name | num_weeks

------+-----------

Moe | 4

(1 row)

4. 实现细节

枚举标签是大小写敏感的，因此'happy'与'HAPPY'是不同的。标签中的空格也是有意义的。

尽管枚举类型的主要目的是用于值的静态集合，但也有方法在现有枚举类型中增加新值和重命名值（见ALTER TYPE）。不能从枚举类型中去除现有的值，也不能更改这些值的排序顺序，如果要那样做可以删除并且重建枚举类型。

一个枚举值在磁盘上占据4个字节。一个枚举值的文本标签的长度受限于NAMEDATALEN设置，该设置被编译在PostgreSQL中，在标准编译下它表示最多63字节。

从内部枚举值到文本标签的翻译被保存在系统目录pg_enum中。可以直接查询该目录。

几何类型

几何数据类型表示二维的平面物体。

下表列出了 PostgreSQL 支持的几何类型。

最基本的类型：点，它是其它类型的基础。

名字	存储空间	说明	表现形式
point	16 字节	平面中的点，是最基本的几何类型	(x,y)
line	32 字节	(无穷)直线(未完全实现)	((x1,y1),(x2,y2))
lseg	32 字节	(有限)线段	((x1,y1),(x2,y2))
box	32 字节	矩形	((x1,y1),(x2,y2))
path	16+16n 字节	闭合路径(与多边形类似)	((x1,y1),...)
path	16+16n 字节	开放路径	[(x1,y1),...]
polygon	40+16n 字节	多边形(与闭合路径相似)	((x1,y1),...)
circle	24 字节	圆	<(x,y),r> (圆心和半径)

我们有一系列丰富的函数和操作符可用来进行各种几何操作，如缩放、平移、旋转和计算相交等它们在第 9.11 节中解释。

1. 点

点是几何类型的基本二维构造块。用下面的语法描述point类型的值：

( x , y )
  x , y

1 2	( x , y ) x , y

其中x和y分别是坐标，都是浮点数。

点使用第一种语法输出。

2. 线

线由线性方程Ax + By + C = 0 表示，其中A和B都不为零。类型line 的值采用以下形式输入和输出：

{ A, B, C }

1	{ A, B, C }

另外，还可以用下列任一形式输入：

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

[ ( x1 , y1 ) , ( x2 , y2 ) ]

( ( x1 , y1 ) , ( x2 , y2 ) )

( x1 , y1 ) , ( x2 , y2 )

x1 , y1 , x2 , y2

其中 (*x1*,*y1*) 和 (*x2*,*y2*) 是线上不同的两点。

3. 线段

线段用一对线段的端点来表示。lseg类型的值用下面的语法声明：

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

[ ( x1 , y1 ) , ( x2 , y2 ) ]

( ( x1 , y1 ) , ( x2 , y2 ) )

( x1 , y1 ) , ( x2 , y2 )

x1 , y1 , x2 , y2

其中(*x1*,*y1*) 和 (*x2*,*y2*) 是线段的端点。

线段使用第一种语法输出。

4. 方框

方框用其对角的点对表示。box类型的值使用下面的语法指定：

( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

( ( x1 , y1 ) , ( x2 , y2 ) )

( x1 , y1 ) , ( x2 , y2 )

x1 , y1 , x2 , y2

其中(*x1*,*y1*) 和 (*x2*,*y2*) 是方框的对角点。

方框使用第二种语法输出。

在输入时可以提供任意两个对角，但是值将根据需要被按顺序记录为右上角和左下角。

5. 路径

路径由一系列连接的点组成。路径可能是开放的，也就是认为列表中第一个点和最后一个点没有被连接起来；也可能是封闭的，这时认为第一个和最后一个点被连接起来。

path类型的值用下面的语法声明：

[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( ( x1 , y1 ) , ... , ( xn , yn ) )
  ( x1 , y1 ) , ... , ( xn , yn )
  ( x1 , y1   , ... ,   xn , yn )
    x1 , y1   , ... ,   xn , yn

[ ( x1 , y1 ) , ... , ( xn , yn ) ]

( ( x1 , y1 ) , ... , ( xn , yn ) )

( x1 , y1 ) , ... , ( xn , yn )

( x1 , y1 , ... , xn , yn )

x1 , y1 , ... , xn , yn

其中的点是组成路径的线段的端点。方括弧（[]）表示一个开放的路径，圆括弧（()）表示一个封闭的路径。如第三种到第五种语法所示，当最外面的圆括号被忽略时，路径将被假定为封闭。

路径的输出使用第一种或第二种语法。

6. 多边形

多边形由一系列点代表（多边形的顶点）。多边形和封闭路径很像，但是存储方式不一样而且有自己的一套支持例程。

polygon类型的值用下列语法声明：

( ( x1 , y1 ) , ... , ( xn , yn ) )
  ( x1 , y1 ) , ... , ( xn , yn )
  ( x1 , y1   , ... ,   xn , yn )
    x1 , y1   , ... ,   xn , yn

( ( x1 , y1 ) , ... , ( xn , yn ) )

( x1 , y1 ) , ... , ( xn , yn )

( x1 , y1 , ... , xn , yn )

x1 , y1 , ... , xn , yn

其中的点是组成多边形边界的线段的端点。

多边形的输出使用第一种语法。

7. 圆

圆由一个圆心和一个半径代表。circle类型的值用下面的语法指定：

< ( x , y ) , r >
( ( x , y ) , r )
  ( x , y ) , r
    x , y   , r

< ( x , y ) , r >

( ( x , y ) , r )

( x , y ) , r

x , y , r

其中(*x*,*y*)是圆心，而r是圆的半径。

圆的输出用第一种语法。

网络地址类型

PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。

用这些数据类型存储网络地址比用纯文本类型好，因为这些类型提供输入错误检查和特殊的操作和功能。

名字	存储空间	描述
cidr	7 或 19 字节	IPv4 或 IPv6 网络
inet	7 或 19 字节	IPv4 或 IPv6 主机和网络
macaddr	6 字节	MAC 地址，用于存储以太网的MAC地址

在对 inet 或 cidr 数据类型进行排序的时候， IPv4 地址总是排在 IPv6 地址前面，包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址，比如 ::10.2.3.4 或 ::ffff:10.4.3.2。

在对inet或者cidr数据类型进行排序的时候， IPv4 地址将总是排在 IPv6 地址前面，包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址，例如 ::10.2.3.4 或者 ::ffff::10.4.3.2。

1. inet

inet在一个数据域里保存一个 IPv4 或 IPv6 主机地址，以及一个可选的它的子网。子网由主机地址中表示的网络地址位数表示（“网络掩码”）。如果网络掩码为 32 并且地址是 IPv4 ，那么该值不表示任何子网，只是一台主机。在 IPv6 中地址长度是 128 位，因此 128 位指定一个唯一的主机地址。请注意如果你想只接受网络地址，你应该使用cidr类型而不是inet。

该类型的输入格式是地址/y，其中地址是一个 IPv4 或者 IPv6 地址，y是网络掩码的位数。如果/y部分省略，则网络掩码对 IPv4 取为 32，对 IPv6 取为 128，所以该值表示只有一台主机。在显示时，如果/y部分指定一个单台主机，它将不会被显示出来。

2. cidr

cidr类型保存一个 IPv4 或 IPv6 网络地址声明。其输入和输出遵循无类的互联网域路由（Classless Internet Domain Routing）习惯。声明一个网络的格式是地址/y，其中address是最低位地址，表现为 IPv4 或 IPv6 网络地址而y是网络掩码的位数。如果省略y，那么掩码部分用旧的有类的网络编号系统进行计算，否则它将至少大到足以包括写在输入中的所有字节。声明一个在其指定的掩码右边置了位的网络地址会导致错误。

表 8.22展示了一些例子。

表 8.22. cidr类型输入例子

`cidr`输入	`cidr`输出	`abbrev(cidr)`
192.168.100.128/25	192.168.100.128/25	192.168.100.128/25
192.168/24	192.168.0.0/24	192.168.0/24
192.168/25	192.168.0.0/25	192.168.0.0/25
192.168.1	192.168.1.0/24	192.168.1/24
192.168	192.168.0.0/24	192.168.0/24
128.1	128.1.0.0/16	128.1/16
128	128.0.0.0/16	128.0/16
128.1.2	128.1.2.0/24	128.1.2/24
10.1.2	10.1.2.0/24	10.1.2/24
10.1	10.1.0.0/16	10.1/16
10	10.0.0.0/8	10/8
10.1.2.3/32	10.1.2.3/32	10.1.2.3/32
2001:4f8:3:ba::/64	2001:4f8:3:ba::/64	2001:4f8:3:ba/64
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128	2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128	2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
::ffff:1.2.3.0/120	::ffff:1.2.3.0/120	::ffff:1.2.3/120
::ffff:1.2.3.0/128	::ffff:1.2.3.0/128	::ffff:1.2.3.0/128

3. inet和cidr

inet和cidr类型之间的本质区别是inet接受右边有非零位的网络掩码，而cidr不接受。例如，192.168.0.1/24对inet是有效的，但对cidr是无效的。

提示

如果你不喜欢inet或cidr值的输出格式，可以尝试函数host、text和abbrev。

4. macaddr

macaddr类型存储 MAC 地址，也就是以太网卡硬件地址（尽管 MAC 地址还用于其它用途）。可以接受下列格式的输入：

`'08:00:2b:01:02:03'`
`'08-00-2b-01-02-03'`
`'08002b:010203'`
`'08002b-010203'`
`'0800.2b01.0203'`
`'0800-2b01-0203'`
`'08002b010203'`

这些例子指定的都是同一个地址。对于位a到f，大小写都可以接受。输出总是使用展示的第一种形式。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

IEEE Std 802-2001 指定第二种展示的形式（带有连字符）作为MAC地址的标准形式，并且指定第一种形式（带有分号）作为位翻转的记号，因此 08-00-2b-01-02-03 = 01:00:4D:08:04:0C。这种习惯目前已经被广泛地忽略，并且它只与废弃的网络协议（如令牌环）相关。PostgreSQL 没有对位翻转做任何规定，并且所有可接受的格式都使用标准的LSB顺序。

剩下的五种输入格式不属于任何标准。

5. macaddr8

macaddr8类型以EUI-64格式存储MAC地址，例如以太网卡的硬件地址（尽管MAC地址也被用于其他目的）。这种类型可以接受6字节和8字节长度的MAC地址，并且将它们存储为8字节长度的格式。以6字节格式给出的MAC地址被存储为8字节长度格式的方式是吧第4和第5字节分别设置为FF和FE。注意IPv6使用一种修改过的EUI-64格式，其中从EUI-48转换过来后的第7位应该被设置为一。函数macaddr8_set7bit被用来做这种修改。一般而言，任何由16进制数（字节边界上）对构成的输入（可以由':'、'-'或者'.'统一地分隔）都会被接受。16进制数的数量必须是16（8字节）或者12（6字节）。前导和拖尾的空格会被忽略。下面是可以被接受的输入格式的例子：

`'08:00:2b:01:02:03:04:05'`
`'08-00-2b-01-02-03-04-05'`
`'08002b:0102030405'`
`'08002b-0102030405'`
`'0800.2b01.0203.0405'`
`'0800-2b01-0203-0405'`
`'08002b01:02030405'`
`'08002b0102030405'`

这些例子都指定相同的地址。数字a到f的大小写形式都被接受。输出总是以上面显示的第一种形式。

上述的后六种输入格式不属于任何标准。

要把EUI-48格式的传统48位MAC地址转换成修改版EUI-64格式（包括在IPv6地址中作为主机部分），可以使用下面的macaddr8_set7bit：

SELECT macaddr8_set7bit('08:00:2b:01:02:03');

    macaddr8_set7bit     
-------------------------
 0a:00:2b:ff:fe:01:02:03
(1 row)

SELECT macaddr8_set7bit('08:00:2b:01:02:03');

macaddr8_set7bit

-------------------------

0a:00:2b:ff:fe:01:02:03

(1 row)

位串类型

位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。我们有两种 SQL 位类型：bit(n) 和bit varying(n)，这里的n是一个正整数。

bit 类型的数据必须准确匹配长度 n，试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型；更长的串会被拒绝。写一个没有长度的bit 等效于 bit(1)，没有长度的 bit varying 意思是没有长度限制。

注意

如果我们显式地把一个位串值转换成bit(*n*)，那么它的右边将被截断或者在右边补齐零，直到刚好n位，而且不会抛出任何错误。类似地，如果我们显式地把一个位串数值转换成bit varying(*n*)，如果它超过了n位，那么它的右边将被截断。

请参考第 4.1.2.5 节获取有关位串常量的语法的信息。还有一些位逻辑操作符和串操作函数可用，请见第 9.6 节。

例 8.3. 使用位串类型

CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');

ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;

  a  |  b
-----+-----
 101 | 00
 100 | 101

CREATE TABLE test (a BIT(3), b BIT VARYING(5));

INSERT INTO test VALUES (B'101', B'00');

INSERT INTO test VALUES (B'10', B'101');

ERROR: bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');

SELECT * FROM test;

a | b

-----+-----

101 | 00

100 | 101

一个位串值对于每8位的组需要一个字节，外加总共5个或8个字节，这取决于串的长度（但是长值可能被压缩或者移到线外，如第 8.3 节中对字符串的解释一样）。

文本搜索类型

全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。

PostgreSQL 提供了两种数据类型用于支持全文检索：

序号	名字 & 描述
1	tsvector tsvector 的值是一个无重复值的 lexemes 排序列表，即一些同一个词的不同变种的标准化。
2	tsquery tsquery 存储用于检索的词汇，并且使用布尔操作符 &(AND)，\|(OR)和!(NOT) 来组合它们，括号用来强调操作符的分组。

PostgreSQL提供两种数据类型，它们被设计用来支持全文搜索，全文搜索是一种在自然语言的文档集合中搜索以定位那些最匹配一个查询的文档的活动。tsvector类型表示一个为文本搜索优化的形式下的文档，tsquery类型表示一个文本查询。第 12 章提供了对于这种功能的详细解释，并且第 9.13 节总结了相关的函数和操作符。

1. tsvector

一个tsvector值是一个排序的可区分词位的列表，词位是被正规化合并了同一个词的不同变种的词（详见第 12 章）。排序和去重是在输入期间自动完成的，如下例所示：

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;

tsvector

----------------------------------------------------

'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

要表示包含空白或标点的词位，将它们用引号包围：

SELECT $$the lexeme '    ' contains spaces$$::tsvector;
                 tsvector
-------------------------------------------
 '    ' 'contains' 'lexeme' 'spaces' 'the'

SELECT $$the lexeme ' ' contains spaces$$::tsvector;

tsvector

-------------------------------------------

' ' 'contains' 'lexeme' 'spaces' 'the'

（我们在这个例子中使用美元符号包围的串文字并且下一个用来避免在文字中包含双引号记号产生的混淆）。嵌入的引号和反斜线必须被双写：

SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
                    tsvector
------------------------------------------------
 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'

SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;

tsvector

------------------------------------------------

'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'

可选的，整数位置可以被附加给词位：

SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
                                  tsvector
-------------------------------------------------------------------------------
 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4

SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;

tsvector

-------------------------------------------------------------------------------

'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4

一个位置通常表示源词在文档中的定位。位置信息可以被用于邻近排名。位置值可以从 1 到 16383，更大的数字会被 16383。对于相同的词位出现的重复位置将被丢弃。

具有位置的词位可以进一步地被标注一个权重，它可以是A、 B、C或D。 D是默认值并且因此在输出中不会显示：

SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;
          tsvector
----------------------------
 'a':1A 'cat':5 'fat':2B,4C

SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;

tsvector

----------------------------

'a':1A 'cat':5 'fat':2B,4C

权重通常被用来反映文档结构，例如将主题词标记成与正文词不同。文本搜索排名函数可以为不同的权重标记器分配不同的优先级。

了解tsvector类型本身并不执行任何词正规化这一点很重要，它假定给它的词已经被恰当地为应用正规化过。例如，

SELECT 'The Fat Rats'::tsvector;
      tsvector
--------------------
 'Fat' 'Rats' 'The'

SELECT 'The Fat Rats'::tsvector;

tsvector

--------------------

'Fat' 'Rats' 'The'

对于大部分英语文本搜索应用，上面的词将会被认为是非正规化的，但是tsvector并不在乎这一点。原始文档文本通常应该经过to_tsvector以恰当地为搜索正规化其中的词：

SELECT to_tsvector('english', 'The Fat Rats');
   to_tsvector
-----------------
 'fat':2 'rat':3

SELECT to_tsvector('english', 'The Fat Rats');

to_tsvector

-----------------

'fat':2 'rat':3

再次地，详情请参阅第 12 章。

2. tsquery

一个tsquery值存储要用于搜索的词位，并且使用布尔操作符&（AND）、|（OR）和!（NOT）来组合它们，还有短语搜索操作符<->（FOLLOWED BY）。也有一种 FOLLOWED BY 操作符的变体<*N*>，其中N是一个整数常量，它指定要搜索的两个词位之间的距离。<->等效于<1>。

圆括号可以被用来强制对操作符分组。如果没有圆括号，!（NOT）的优先级最高，其次是<->（FOLLOWED BY），然后是&（AND），最后是|（OR）。

这里有一些例子：

SELECT 'fat & rat'::tsquery;
    tsquery    
---------------
 'fat' & 'rat'

SELECT 'fat & (rat | cat)'::tsquery;
          tsquery          
---------------------------
 'fat' & ( 'rat' | 'cat' )

SELECT 'fat & rat & ! cat'::tsquery;
        tsquery         
------------------------
 'fat' & 'rat' & !'cat'

SELECT 'fat & rat'::tsquery;

tsquery

---------------

'fat' & 'rat'

SELECT 'fat & (rat | cat)'::tsquery;

tsquery

---------------------------

'fat' & ( 'rat' | 'cat' )

SELECT 'fat & rat & ! cat'::tsquery;

tsquery

------------------------

'fat' & 'rat' & !'cat'

可选地，一个tsquery中的词位可以被标注一个或多个权重字母，这将限制它们只能和具有那些权重之一的tsvector词位相匹配：

SELECT 'fat:ab & cat'::tsquery;
    tsquery
------------------
 'fat':AB & 'cat'

SELECT 'fat:ab & cat'::tsquery;

tsquery

------------------

'fat':AB & 'cat'

此外，一个tsquery中的词位可以被标注为*来指定前缀匹配：

SELECT 'super:*'::tsquery;
  tsquery
-----------
 'super':*

SELECT 'super:*'::tsquery;

tsquery

-----------

'super':*

这个查询将匹配一个tsvector中以“super”开头的任意词。

词位的引号规则和之前描述的tsvector中的词位相同；并且，正如tsvector，任何请求的词正规化必须在转换到tsquery类型之前完成。to_tsquery函数可以方便地执行这种正规化：

SELECT to_tsquery('Fat:ab & Cats');
    to_tsquery
------------------
 'fat':AB & 'cat'

SELECT to_tsquery('Fat:ab & Cats');

to_tsquery

------------------

'fat':AB & 'cat'

注意to_tsquery将会以和其他词同样的方式处理前缀，这也意味着下面的比较会返回真：

SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );
 ?column?
----------
 t

SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );

?column?

----------

因为postgres会被处理成postgr：

SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
  to_tsvector  | to_tsquery
---------------+------------
 'postgradu':1 | 'postgr':*

SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );

to_tsvector | to_tsquery

---------------+------------

'postgradu':1 | 'postgr':*

这会匹配postgraduate被处理后的形式。

UUID 类型

uuid 数据类型用来存储 RFC 4122，ISO/IEF 9834-8:2005 以及相关标准定义的通用唯一标识符（UUID）。（一些系统认为这个数据类型为全球唯一标识符，或GUID。）这个标识符是一个由算法产生的 128 位标识符，使它不可能在已知使用相同算法的模块中和其他方式产生的标识符相同。因此，对分布式系统而言，这种标识符比序列能更好的提供唯一性保证，因为序列只能在单一数据库中保证唯一。

UUID 被写成一个小写十六进制数字的序列，由分字符分成几组，特别是一组8位数字+3组4位数字+一组12位数字，总共 32 个数字代表 128 位，一个这种标准的 UUID 例子如下：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

1	a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

PostgreSQL也接受另一种输入形式：使用大写位、标准格式被花括号包围、忽略某些或者全部连字符、在任意4位组后面增加一个连字符。例如：

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11

{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}

a0eebc999c0b4ef8bb6d6bb9bd380a11

a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11

{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

输出总是采用标准形式。

对于如何在PostgreSQL中生成UUID，请参见第 9.14 节。

XML 类型

xml 数据类型可以用于存储XML数据。将 XML 数据存到 text 类型中的优势在于它能够为结构良好性来检查输入值，并且还支持函数对其进行类型安全性检查。要使用这个数据类型，编译时必须使用 configure --with-libxml。

xml 可以存储由XML标准定义的格式良好的"文档"，以及由 XML 标准中的 XMLDecl? content 定义的"内容"片段，大致上，这意味着内容片段可以有多个顶级元素或字符节点。 xmlvalue IS DOCUMENT 表达式可以用来判断一个特定的 xml 值是一个完整的文件还是内容片段。

创建XML值

使用函数 xmlparse: 来从字符数据产生 xml 类型的值：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

1 2	XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>') XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

要从字符数据中生成一个xml类型的值，可以使用函数xmlparse:

XMLPARSE ( { DOCUMENT | CONTENT } value)

1	XMLPARSE ( { DOCUMENT \| CONTENT } value)

例子：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

1 2	XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>') XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

然而根据SQL标准这是唯一将字符串转换为XML值的方法，PostgreSQL特有的语法：

xml '<foo>bar</foo>'
'<foo>bar</foo>'::xml

1 2	xml '<foo>bar</foo>' '<foo>bar</foo>'::xml

也可以被使用。

即便输入值指定了一个文档类型声明（DTD），xml类型也不根据DTD来验证输入值。目前也没有内建的支持用于根据其他XML模式语言（如XML模式）来进行验证。

作为一个逆操作，从xml产生一个字符串可以使用函数xmlserialize:

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

1	XMLSERIALIZE ( { DOCUMENT \| CONTENT } value AS type )

type可以是 character、character varying或 text（或者其中之一的一个别名）。再次地，根据SQL标准，这也是在xml类型和字符类型间做转换的唯一方法，但是PostgreSQL也允许你简单地造型这些值。

当一个字符串不是使用XMLPARSE造型成xml或者不是使用XMLSERIALIZE从xml造型得到，对于DOCUMENT和CONTENT两者的选择是根据“XML option” 会话配置参数决定的，它可以使用标准命令来设置：

SET XML OPTION { DOCUMENT | CONTENT };

1	SET XML OPTION { DOCUMENT \| CONTENT };

或者是更具有PostgreSQL风格的语法

SET xmloption TO { DOCUMENT | CONTENT };

1	SET xmloption TO { DOCUMENT \| CONTENT };

默认值是CONTENT，因此所有形式的XML数据都被允许。

编码处理

在客户端、服务器以及其中流过的XML数据上处理多字符编码时必须要注意。在使用文本模式向服务器传递查询以及向客户端传递查询结果（在普通模式）时，PostgreSQL将所有在客户端和服务器之间传递的字符数据转换为目标端的字符编码，参见第 23.3 节。这也包括了表示XML值的串，正如上面的例子所述。这也通常意味着由于字符数据会在客户端和服务器之间传递时被转换成其他编码，包含在XML数据中的编码声明可能是无效的，因为内嵌的编码声明没有被改变。为了处理这种行为，包含在表示xml类型输入的字符串中包含的编码声明会被忽略\，并且其内容被假定为当前服务器的编码。接着，为了正确处理，XML数据的字符串必须以当前客户端编码从客户端发出。客户端负责在把文档发送给服务器之前将它们转换为当前客户端编码，或者适当地调整客户端编码。在输出时，xml类型的值将不会有一个编码声明，并且客户端将会假设所有数据都是当前客户端编码。

在使用二进制模式传送查询参数给服务器以及传回查询结果给客户端时，不会执行编码转换，因此情况就有所不同。在这种情况下，XML数据中的编码声明将被注意到，并且如果缺少编码声明时该数据会被假定为UTF-8（由于XML标准的要求，注意PostgreSQL不支持UTF-16）。在输出时，数据将会有一个编码声明来指定客户端编码，除非客户端编码为UTF-8（这种情况下编码声明会被忽略）。

不用说，在PostgreSQL中处理XML数据产生错误的可能性更小，并且在XML数据编码、客户端编码和服务器编码三者相同时效率更高。因为XML数据在内部是以UTF-8处理的，如果服务器编码也是UTF-8时，计算效率将会最高。

小心

当服务器编码不是UTF-8时，某些XML相关的函数可能在非ASCII数据上完全无法工作。尤其在xmltable()和xpath()上，这是一个已知的问题。

访问XML值

xml数据类型有些不同寻常，因为它不提供任何比较操作符。这是因为对于XML数据不存在良定义的和通用的比较算法。这种状况造成的后果就是，你无法通过比较一个xml和一个搜索值来检索行。XML值因此通常应该伴随着一个独立键值域，如一个ID。另一种比较XML值的方案是将它们先转换为字符串，但注意字符串比较对于XML比较方法没有什么帮助。

由于没有可以用于xml数据类型的比较操作符，因此无法直接在这种类型上创建索引。如果需要在XML中快速的搜索，可能的解决方案包括将表达式造型为一个字符串类型然后索引之，或者在一个XPath表达式上索引。当然，实际的查询必须被调整为使用被索引的表达式。

PostgreSQL中的文本搜索功能也可以被用来加速XML数据的全文搜索。但是，所需的预处理支持目前在PostgreSQL发布中还不可用。

JSON 类型

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据，这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

此外还有相关的函数来处理 json 数据：

实例	实例结果
array_to_json('{{1,5},{99,100}}'::int[])	[[1,5],[99,100]]
row_to_json(row(1,'foo'))	{"f1":1,"f2":"foo"}

参考：https://www.xmmup.com/pgshujuleixingzhijsonhejsonb.html

数组类型

PostgreSQL 允许将字段定义成变长的多维数组。

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

声明数组

创建表的时候，我们可以声明数组，方式如下：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

CREATE TABLE sal_emp (

name text,

pay_by_quarter integer[],

schedule text[][]

);

pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。

我们也可以使用 "ARRAY" 关键字，如下所示：

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

CREATE TABLE sal_emp (

name text,

pay_by_quarter integer ARRAY[4],

schedule text[][]

);

插入值

插入值使用花括号 {}，元素在 {} 使用逗号隔开：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

INSERT INTO sal_emp

VALUES ('Bill',

'{10000, 10000, 10000, 10000}',

'{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp

VALUES ('Carol',

'{20000, 25000, 25000, 25000}',

'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

访问数组

现在我们可以在这个表上运行一些查询。

首先，我们演示如何访问数组的一个元素。这个查询检索在第二季度薪水变化的雇员名：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-
 Carol
(1 row)

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

name

Carol

(1 row)

数组的下标数字是写在方括弧内的。下标数字是从1开始的。

修改数组

我们可以对数组的值进行修改：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

1 2	UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol';

或者使用 ARRAY 构造器语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

1 2	UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol';

数组中检索

要搜索一个数组中的数值，你必须检查该数组的每一个值。

比如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR

pay_by_quarter[2] = 10000 OR

pay_by_quarter[3] = 10000 OR

pay_by_quarter[4] = 10000;

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

1	SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

或者，可以使用 generate_subscripts 函数。例如：

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

SELECT * FROM

(SELECT pay_by_quarter,

generate_subscripts(pay_by_quarter, 1) AS s

FROM sal_emp) AS foo

WHERE pay_by_quarter[s] = 10000;

复合类型

复合类型表示一行或者一条记录的结构；它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL 允许像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。

声明复合类型

下面是两个定义复合类型的简单例子：

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

CREATE TYPE complex AS (

r double precision,

i double precision

);

CREATE TYPE inventory_item AS (

name text,

supplier_id integer,

price numeric

);

语法类似于 CREATE TABLE，只是这里只可以声明字段名字和类型。

定义了类型，我们就可以用它创建表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

CREATE TABLE on_hand (

item inventory_item,

count integer

);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

复合类型值输入

要以文本常量书写复合类型值，在圆括弧里包围字段值并且用逗号分隔他们。你可以在任何字段值周围放上双引号，如果值本身包含逗号或者圆括弧，你必须用双引号括起。

复合类型常量的一般格式如下：

'( val1 , val2 , ... )'

1	'( val1 , val2 , ... )'

一个例子是:

'("fuzzy dice",42,1.99)'

1	'("fuzzy dice",42,1.99)'

访问复合类型

要访问复合类型字段的一个域，我们写出一个点以及域的名字，非常类似从一个表名字里选出一个字段。实际上，因为实在太像从表名字中选取字段，所以我们经常需要用圆括弧来避免分析器混淆。比如，你可能需要从on_hand 例子表中选取一些子域，像下面这样：

SELECT item.name FROM on_hand WHERE item.price > 9.99;

1	SELECT item.name FROM on_hand WHERE item.price > 9.99;

这样将不能工作，因为根据 SQL 语法，item是从一个表名字选取的，而不是一个字段名字。你必须像下面这样写：

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

1	SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者如果你也需要使用表名字(比如，在一个多表查询里)，那么这么写：

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

1	SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

现在圆括弧对象正确地解析为一个指向item字段的引用，然后就可以从中选取子域。

范围类型

范围数据类型代表着某一元素类型在一定范围内的值。

例如，timestamp 范围可能被用于代表一间会议室被预定的时间范围。

PostgreSQL 内置的范围类型有：

int4range — integer的范围
int8range —bigint的范围
numrange —numeric的范围
tsrange —timestamp without time zone的范围
tstzrange —timestamp with time zone的范围
daterange —date的范围

此外，你可以定义你自己的范围类型。

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

CREATE TABLE reservation (room int, during tsrange);

INSERT INTO reservation VALUES

(1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含

SELECT int4range(10, 20) @> 3;

-- 重叠

SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界

SELECT upper(int8range(15, 25));

-- 计算交叉

SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空

SELECT isempty(numrange(1, 5));

范围值的输入必须遵循下面的格式：

(下边界,上边界)
(下边界,上边界]
[下边界,上边界)
[下边界,上边界]
空

(下边界,上边界)

(下边界,上边界]

[下边界,上边界)

[下边界,上边界]

空

圆括号或者方括号显示下边界和上边界是不包含的还是包含的。注意最后的格式是空，代表着一个空的范围（一个不含有值的范围）。

-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;

-- 包括3，不包括7，并且包括二者之间的所有点

SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之间所有点

SELECT '(3,7)'::int4range;

-- 只包括单一值4

SELECT '[4,4]'::int4range;

-- 不包括点（被标准化为‘空’）

SELECT '[4,4)'::int4range;

对象标识符类型

PostgreSQL 在内部使用对象标识符(OID)作为各种系统表的主键。

同时，系统不会给用户创建的表增加一个 OID 系统字段(除非在建表时声明了WITH OIDS 或者配置参数default_with_oids设置为开启)。oid 类型代表一个对象标识符。除此以外 oid 还有几个别名：regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, 和regdictionary。

名字	引用	描述	数值例子
oid	任意	数字化的对象标识符	564182
regproc	pg_proc	函数名字	sum
regprocedure	pg_proc	带参数类型的函数	sum(int4)
regoper	pg_operator	操作符名	+
regoperator	pg_operator	带参数类型的操作符	*(integer,integer) 或 -(NONE,integer)
regclass	pg_class	关系名	pg_type
regtype	pg_type	数据类型名	integer
regconfig	pg_ts_config	文本搜索配置	english
regdictionary	pg_ts_dict	文本搜索字典	simple

伪类型

PostgreSQL类型系统包含一系列特殊用途的条目，它们按照类别来说叫做伪类型。伪类型不能作为字段的数据类型，但是它可以用于声明一个函数的参数或者结果类型。伪类型在一个函数不只是简单地接受并返回某种SQL 数据类型的情况下很有用。

下表列出了所有的伪类型：

名字	描述
any	表示一个函数接受任何输入数据类型。
anyelement	表示一个函数接受任何数据类型。
anyarray	表示一个函数接受任意数组数据类型。
anynonarray	表示一个函数接受任意非数组数据类型。
anyenum	表示一个函数接受任意枚举数据类型。
anyrange	表示一个函数接受任意范围数据类型。
cstring	表示一个函数接受或者返回一个空结尾的 C 字符串。
internal	表示一个函数接受或者返回一种服务器内部的数据类型。
language_handler	一个过程语言调用处理器声明为返回language_handler。
fdw_handler	一个外部数据封装器声明为返回fdw_handler。
record	标识一个函数返回一个未声明的行类型。
trigger	一个触发器函数声明为返回trigger。
void	表示一个函数不返回数值。
opaque	一个已经过时的类型，以前用于所有上面这些用途。

pg_lsn 类型

pg_lsn数据类型可以被用来存储 LSN（日志序列号）数据，LSN 是一个指向WAL中的位置的指针。这个类型是XLogRecPtr的一种表达并且是 PostgreSQL的一种内部系统类型。

在内部，一个 LSN 是一个 64 位整数，表示在预写式日志流中的一个字节位置。它被打印成两个最高 8 位的十六进制数，中间用斜线分隔，例如16/B374D848。 pg_lsn类型支持标准的比较操作符，如=和 >。两个 LSN 可以用-操作符做减法，结果将是分隔两个预写式日志位置的字节数。

域类型

域是一种用户定义的数据类型，它基于另一种底层类型。根据需要，它可以有约束来限制其有效值为底层类型所允许值的一个子集。如果没有约束，它的行为就和底层类型一样 — 例如，任何适用于底层类型的操作符或函数都对该域类型有效。底层类型可以是任何内建或者用户定义的基础类型、枚举类型、数组类型、组合类型、范围类型或者另一个域。

例如，我们可以在整数之上创建一个域，它只接受正整数：

CREATE DOMAIN posint AS integer CHECK (VALUE > 0);
CREATE TABLE mytable (id posint);
INSERT INTO mytable VALUES(1);   -- works
INSERT INTO mytable VALUES(-1);  -- fails

CREATE DOMAIN posint AS integer CHECK (VALUE > 0);

CREATE TABLE mytable (id posint);

INSERT INTO mytable VALUES(1); -- works

INSERT INTO mytable VALUES(-1); -- fails

当底层类型的一个操作符或函数适用于一个域值时，域会被自动向下造型为底层类型。因此，mytable.id - 1的结果会被认为是类型integer而不是posint。我们可以写成(mytable.id - 1)::posint来把结果转换回posint，这会导致域的约束被重新检查。在这种情况下，如果该表达式被应用于一个值为1的id就会错误。把底层类型的值赋给域类型的一个字段或者变量不需要写显式的造型，但是域的约束将会被检查。

打赏赞(6)

标签： PG 其它数据类型

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表评论取消回复

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

PostgreSQL 数据类型简介

PostgreSQL 数据类型

数值类型

1. 整数类型

2. 任意精度数字

注意

注意

3. 浮点类型

注意

注意

注意

4. 序数类型

注意

注意

货币类型

字符类型

提示

日期/时间类型

注意

1. 日期/时间输入

1.1. 日期

1.2. 时间

1.3. 时间戳

1.4. 特殊值

小心

2. 日期/时间输出

注意

3. 时区

4. 间隔输入

5. 间隔输出

布尔类型

枚举类型

1. 枚举类型的声明

2. 排序

3. 类型安全性

4. 实现细节

几何类型

1. 点

2. 线

3. 线段

4. 方框

5. 路径

6. 多边形

7. 圆

网络地址类型

1. inet

2. cidr

3. inet和cidr

提示

4. macaddr

5. macaddr8

位串类型

注意

文本搜索类型

1. tsvector

2. tsquery

UUID 类型

XML 类型

创建XML值

编码处理

小心

访问XML值

JSON 类型

数组类型

声明数组

插入值

访问数组

修改数组

数组中检索

复合类型

声明复合类型

复合类型值输入

访问复合类型

范围类型

对象标识符类型

伪类型

pg_lsn 类型

域类型

您可能还喜欢...

在CentOS 7.6上编译安装Pacemaker-2.1.3

发表评论取消回复