PostgreSQL数据类型（中文手册）

ゞ浴缸里的玫瑰 2022-09-02 00:46 452阅读 0赞

### PostgreSQL数据类型（中文手册） ###

*  一、数据类型
 *   *  1.1 兼容性
 *  二、数字类型
 *   *  2.1 整数类型
     *  2.2 任意精度数字
     *   *  注意
         *  注意
     *  2.3 浮点类型
     *   *  注意
         *  注意
         *  注意
     *  2.4 序数类型
     *   *  注意
         *  注意
 *  三、货币类型
 *  四、字符类型
 *   *  4.1 提示
 *  五、二进制数据类型
 *   *  5.1 \`bytea\`的十六进制格式
     *  5.2 \`bytea\`的转义格式
 *  六、日期/时间类型
 *   *  注意
     *  6.1 日期/时间输入
     *   *  6.1.1 日期
         *  6.1.2 时间
         *  6.1.3 时间戳
         *  6.1.4 特殊值
     *  6.2 日期/时间输出
     *   *  注意
         *  6.2.1 时区
         *  6.2.2 间隔输入
         *  6.2.3 间隔输出
 *  七、布尔类型
 *  八、枚举类型
 *   *  8.1 枚举类型的声明
     *  8.2 排序
     *  8.3 类型安全性
     *  8.4 实现细节
 *  九、几何类型
 *   *  9.1 点
     *  9.2 线
     *  9.3 线段
     *  9.4 方框
     *  9.5 路径
     *  9.6 多边形
     *  9.7 圆
 *  十、网络地址类型
 *   *  10.1 \`inet\`
     *  10.2 \`cidr\`
     *  10.3 \`inet\` vs. \`cidr\`
     *   *  提示
     *  10.4 \`macaddr\`
     *  10.5 \`macaddr8\`
 *  十一、位串类型
 *   *   *  注意
 *  十二、文本搜索类型
 *   *  12.1 \`tsvector\`
     *  12.2 \`tsquery\`
 *  十三、UUID类型
 *  十四、XML类型
 *   *  14.1 创建XML值
     *  14.2 编码处理
     *   *  小心
     *  14.3 访问XML值

# 一、数据类型 #

PostgreSQL有着丰富的本地数据类型可用。用户可以使用[CREATE TYPE][]命令为 PostgreSQL增加新的数据类型。

[表 8.1][8.1]显示了所有内建的普通数据类型。大部分在“别名”列里列出的可选名字都是因历史原因 被PostgreSQL在内部使用的名字。另外，还有一些内部使用的或者废弃的类型也可以用，但没有在这里列出。

**表 8.1. 数据类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>别名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>bigint</code></td> 
   <td><code>int8</code></td> 
   <td>有符号的8字节整数</td> 
  </tr> 
  <tr> 
   <td><code>bigserial</code></td> 
   <td><code>serial8</code></td> 
   <td>自动增长的8字节整数</td> 
  </tr> 
  <tr> 
   <td><code>bit [ (*</code>n<code>*) ]</code></td> 
   <td></td> 
   <td>定长位串</td> 
  </tr> 
  <tr> 
   <td><code>bit varying [ (*</code>n<code>*) ]</code></td> 
   <td><code>varbit [ (*</code>n<code>*) ]</code></td> 
   <td>变长位串</td> 
  </tr> 
  <tr> 
   <td><code>boolean</code></td> 
   <td><code>bool</code></td> 
   <td>逻辑布尔值（真/假）</td> 
  </tr> 
  <tr> 
   <td><code>box</code></td> 
   <td></td> 
   <td>平面上的普通方框</td> 
  </tr> 
  <tr> 
   <td><code>bytea</code></td> 
   <td></td> 
   <td>二进制数据（“字节数组”）</td> 
  </tr> 
  <tr> 
   <td><code>character [ (*</code>n<code>*) ]</code></td> 
   <td><code>char [ (*</code>n<code>*) ]</code></td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td><code>character varying [ (*</code>n<code>*) ]</code></td> 
   <td><code>varchar [ (*</code>n<code>*) ]</code></td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td><code>cidr</code></td> 
   <td></td> 
   <td>IPv4或IPv6网络地址</td> 
  </tr> 
  <tr> 
   <td><code>circle</code></td> 
   <td></td> 
   <td>平面上的圆</td> 
  </tr> 
  <tr> 
   <td><code>date</code></td> 
   <td></td> 
   <td>日历日期（年、月、日）</td> 
  </tr> 
  <tr> 
   <td><code>double precision</code></td> 
   <td><code>float8</code></td> 
   <td>双精度浮点数（8字节）</td> 
  </tr> 
  <tr> 
   <td><code>inet</code></td> 
   <td></td> 
   <td>IPv4或IPv6主机地址</td> 
  </tr> 
  <tr> 
   <td><code>integer</code></td> 
   <td><code>int</code>, <code>int4</code></td> 
   <td>有符号4字节整数</td> 
  </tr> 
  <tr> 
   <td><code>interval [ *</code>fields<code>* ] [ (*</code>p<code>*) ]</code></td> 
   <td></td> 
   <td>时间段</td> 
  </tr> 
  <tr> 
   <td><code>json</code></td> 
   <td></td> 
   <td>文本 JSON 数据</td> 
  </tr> 
  <tr> 
   <td><code>jsonb</code></td> 
   <td></td> 
   <td>二进制 JSON 数据，已分解</td> 
  </tr> 
  <tr> 
   <td><code>line</code></td> 
   <td></td> 
   <td>平面上的无限长的线</td> 
  </tr> 
  <tr> 
   <td><code>lseg</code></td> 
   <td></td> 
   <td>平面上的线段</td> 
  </tr> 
  <tr> 
   <td><code>macaddr</code></td> 
   <td></td> 
   <td>MAC（Media Access Control）地址</td> 
  </tr> 
  <tr> 
   <td><code>macaddr8</code></td> 
   <td></td> 
   <td>MAC（Media Access Control）地址（EUI-64格式）</td> 
  </tr> 
  <tr> 
   <td><code>money</code></td> 
   <td></td> 
   <td>货币数量</td> 
  </tr> 
  <tr> 
   <td><code>numeric [ (*</code>p<code>*, *</code>s<code>*) ]</code></td> 
   <td><code>decimal [ (*</code>p<code>*, *</code>s<code>*) ]</code></td> 
   <td>可选择精度的精确数字</td> 
  </tr> 
  <tr> 
   <td><code>path</code></td> 
   <td></td> 
   <td>平面上的几何路径</td> 
  </tr> 
  <tr> 
   <td><code>pg_lsn</code></td> 
   <td></td> 
   <td>PostgreSQL日志序列号</td> 
  </tr> 
  <tr> 
   <td><code>point</code></td> 
   <td></td> 
   <td>平面上的几何点</td> 
  </tr> 
  <tr> 
   <td><code>polygon</code></td> 
   <td></td> 
   <td>平面上的封闭几何路径</td> 
  </tr> 
  <tr> 
   <td><code>real</code></td> 
   <td><code>float4</code></td> 
   <td>单精度浮点数（4字节）</td> 
  </tr> 
  <tr> 
   <td><code>smallint</code></td> 
   <td><code>int2</code></td> 
   <td>有符号2字节整数</td> 
  </tr> 
  <tr> 
   <td><code>smallserial</code></td> 
   <td><code>serial2</code></td> 
   <td>自动增长的2字节整数</td> 
  </tr> 
  <tr> 
   <td><code>serial</code></td> 
   <td><code>serial4</code></td> 
   <td>自动增长的4字节整数</td> 
  </tr> 
  <tr> 
   <td><code>text</code></td> 
   <td></td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td><code>time [ (*</code>p<code>*) ] [ without time zone ]</code></td> 
   <td></td> 
   <td>一天中的时间（无时区）</td> 
  </tr> 
  <tr> 
   <td><code>time [ (*</code>p<code>*) ] with time zone</code></td> 
   <td><code>timetz</code></td> 
   <td>一天中的时间，包括时区</td> 
  </tr> 
  <tr> 
   <td><code>timestamp [ (*</code>p<code>*) ] [ without time zone ]</code></td> 
   <td></td> 
   <td>日期和时间（无时区）</td> 
  </tr> 
  <tr> 
   <td><code>timestamp [ (*</code>p<code>*) ] with time zone</code></td> 
   <td><code>timestamptz</code></td> 
   <td>日期和时间，包括时区</td> 
  </tr> 
  <tr> 
   <td><code>tsquery</code></td> 
   <td></td> 
   <td>文本搜索查询</td> 
  </tr> 
  <tr> 
   <td><code>tsvector</code></td> 
   <td></td> 
   <td>文本搜索文档</td> 
  </tr> 
  <tr> 
   <td><code>txid_snapshot</code></td> 
   <td></td> 
   <td>用户级别事务ID快照</td> 
  </tr> 
  <tr> 
   <td><code>uuid</code></td> 
   <td></td> 
   <td>通用唯一标识码</td> 
  </tr> 
  <tr> 
   <td><code>xml</code></td> 
   <td></td> 
   <td>XML数据</td> 
  </tr> 
 </tbody> 
</table>

## 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][8.2]列出了所有可用类型。

**表 8.2. 数字类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储尺寸</th> 
   <th>描述</th> 
   <th>范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>smallint</code></td> 
   <td>2字节</td> 
   <td>小范围整数</td> 
   <td>-32768 to +32767</td> 
  </tr> 
  <tr> 
   <td><code>integer</code></td> 
   <td>4字节</td> 
   <td>整数的典型选择</td> 
   <td>-2147483648 to +2147483647</td> 
  </tr> 
  <tr> 
   <td><code>bigint</code></td> 
   <td>8字节</td> 
   <td>大范围整数</td> 
   <td>-9223372036854775808 to +9223372036854775807</td> 
  </tr> 
  <tr> 
   <td><code>decimal</code></td> 
   <td>可变</td> 
   <td>用户指定精度，精确</td> 
   <td>最高小数点前131072位，以及小数点后16383位</td> 
  </tr> 
  <tr> 
   <td><code>numeric</code></td> 
   <td>可变</td> 
   <td>用户指定精度，精确</td> 
   <td>最高小数点前131072位，以及小数点后16383位</td> 
  </tr> 
  <tr> 
   <td><code>real</code></td> 
   <td>4字节</td> 
   <td>可变精度，不精确</td> 
   <td>6位十进制精度</td> 
  </tr> 
  <tr> 
   <td><code>double precision</code></td> 
   <td>8字节</td> 
   <td>可变精度，不精确</td> 
   <td>15位十进制精度</td> 
  </tr> 
  <tr> 
   <td><code>smallserial</code></td> 
   <td>2字节</td> 
   <td>自动增加的小整数</td> 
   <td>1到32767</td> 
  </tr> 
  <tr> 
   <td><code>serial</code></td> 
   <td>4字节</td> 
   <td>自动增加的整数</td> 
   <td>1到2147483647</td> 
  </tr> 
  <tr> 
   <td><code>bigserial</code></td> 
   <td>8字节</td> 
   <td>自动增长的大整数</td> 
   <td>1到9223372036854775807</td> 
  </tr> 
 </tbody> 
</table>

数字类型常量的语法在[第 4.1.2 节][4.1.2]里描述。数字类型有一整套对应的数学操作符和函数。相关信息请参考 [第 9 章][9]。下面的几节详细描述这些类型。

## 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`的列，你可以用下面的语法：

NUMERIC(precision, scale)

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

NUMERIC(precision)

选择比例为 0 。如果使用

NUMERIC

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

### 注意 ###

显式指定类型精度时的最大允许精度为 1000，没有指定精度的`NUMERIC`受到[表 8.2][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)

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

设置[extra\_float\_digits][extra_float_digits]位任何大于 0 的值将选择最短精确格式。

### 注意 ###

需要更精确值的应用需要设置[extra\_float\_digits][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`。

## 2.4 序数类型 ##

### 注意 ###

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

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

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

### 注意 ###

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

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

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

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

# 三、货币类型 #

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

**表 8.3. 货币类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储尺寸</th> 
   <th>描述</th> 
   <th>范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>money</code></td> 
   <td>8 bytes</td> 
   <td>货币额</td> 
   <td>-92233720368547758.08到+92233720368547758.07</td> 
  </tr> 
 </tbody> 
</table>

由于这种数据类型的输出是区域敏感的，因此将`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. 字符类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>character varying(*</code>n<code>*)</code>, <code>varchar(*</code>n<code>*)</code></td> 
   <td>有限制的变长</td> 
  </tr> 
  <tr> 
   <td><code>character(*</code>n<code>*)</code>, <code>char(*</code>n<code>*)</code></td> 
   <td>定长，空格填充</td> 
  </tr> 
  <tr> 
   <td><code>text</code></td> 
   <td>无限变长</td> 
  </tr> 
 </tbody> 
</table>

[表 8.4][8.4]显示了在PostgreSQL里可用的一般用途的字符类型。

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`，而不是给出一个任意长度限制）。

## 4.1 提示 ##

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

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

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

**表 8.5. 特殊字符类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储尺寸</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>"char"</code></td> 
   <td>1字节</td> 
   <td>单字节内部类型</td> 
  </tr> 
  <tr> 
   <td><code>name</code></td> 
   <td>64字节</td> 
   <td>用于对象名的内部类型</td> 
  </tr> 
 </tbody> 
</table>

# 五、二进制数据类型 #

`bytea`数据类型允许存储二进制串，参见[表 8.6][8.6]。

**表 8.6. 二进制数据类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储尺寸</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>bytea</code></td> 
   <td>1或4字节外加真正的二进制串</td> 
   <td>变长二进制串</td> 
  </tr> 
 </tbody> 
</table>

二进制串是一个八位位组（或字节）的序列。 二进制串和字符串的区别有两个： 首先，二进制串明确允许存储零值的字节以及其它“不可打印的”字节（通常是位于十进制范围32到126之外的字节）。 字符串不允许零字节，并且也不允许那些对于数据库的选定字符集编码是非法的任何其它字节值或者字节值序列。 第二，对二进制串的操作会处理实际上的字节，而字符串的处理和取决于区域设置。 简单说，二进制字串适用于存储那些程序员认为是“裸字节”的数据，而字符串适合存储文本。

`bytea`类型支持两种用于输入和输出的格式：“十六进制”格式和PostgreSQL的历史的“转义”格式。在输入时这两种格式总是会被接受。输出格式则取决于配置参数[bytea\_output][bytea_output]，其默认值为十六进制（注意十六进制格式是在PostgreSQL 9.0中被引入的，早期的版本和某些工具无法理解它）。

SQL标准定义了一种不同的二进制串类型， 叫做`BLOB`或者`BINARY LARGE OBJECT`。其输入格式和`bytea`不同，但是提供的函数和操作符大多一样。

## 5.1 `bytea`的十六进制格式 ##

“十六进制”格式将二进制数据编码为每个字节2个十六进制位，最高有效位在前。整个串以序列`\x`开头（用以和转义格式区分）。在某些情景中，开头的反斜线可能需要通过双写来转义，详见(see [第 4.1.2.1 节][4.1.2.1])。 作为输入，十六进制位可以是大写也可以是小写，在位对之间可以有空白（但是在位对内部以及开头的`\x`序列中不能有空白）。十六进制格式和很多外部应用及协议相兼容，并且其转换速度要比转义格式更快，因此人们更愿意用它。

例子：

SELECT '\xDEADBEEF';

## 5.2 `bytea`的转义格式 ##

“转义”格式是`bytea`类型的传统PostgreSQL格式。它采用将二进制串表示成ASCII字符序列的方法，而将那些无法用ASCII字符表示的字节转换成特殊的转义语句。从应用的角度来看，如果将字节表示为字符有意义，那么这种表示将很方便。但是在实际中，这常常是令人困扰的，因为它使二进制串和字符串之间的区别变得模糊，并且这种特别的转义机制也有点难于处理。因此这种格式可能会在大部分新应用中避免使用。

在转义模式下输入`bytea`值时，某些值的字节*必须*被转义，而所有的字节值都*可以*被转义。通常，要转义一个字节，需要把它转换成与它的三位八进制值， 并且前导一个反斜线。反斜线本身（十进制字节值92）也可以用双写的反斜线表示。[表 8.7][8.7]显示了必须被转义的字符，并给出了可以使用的替代转义序列。

**表 8.7. `bytea`文字转义字节**

<table> 
 <thead> 
  <tr> 
   <th>十进制字节值</th> 
   <th>描述</th> 
   <th>转义输入表示</th> 
   <th>例子</th> 
   <th>十六进制表示</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0</td> 
   <td>0字节</td> 
   <td><code>'\000'</code></td> 
   <td><code>SELECT '\000'::bytea;</code></td> 
   <td><code>\x00</code></td> 
  </tr> 
  <tr> 
   <td>39</td> 
   <td>单引号</td> 
   <td><code>''''</code>或<code>'\047'</code></td> 
   <td><code>SELECT ''''::bytea;</code></td> 
   <td><code>\x27</code></td> 
  </tr> 
  <tr> 
   <td>92</td> 
   <td>反斜线</td> 
   <td><code>'\\'</code>或<code>'\134'</code></td> 
   <td><code>SELECT '\\'::bytea;</code></td> 
   <td><code>\x5c</code></td> 
  </tr> 
  <tr> 
   <td>0到31和127到255</td> 
   <td>“不可打印的”字节</td> 
   <td><code>'\*</code>xxx’<code>*</code>（八进制值）</td> 
   <td><code>SELECT '\001'::bytea;</code></td> 
   <td><code>\x01</code></td> 
  </tr> 
 </tbody> 
</table>

转义“不可打印的”字节的要求取决于区域设置。在某些实例中，你可以不理睬它们，让它们保持未转义的状态。

如[表 8.7][8.7]中所示，要求单引号必须写两次的原因对任何SQL命令中的字符串常量都是一样的。 文字解析器消耗最外层的单引号，并缩减成对的单引号为一个普通数据字符。 `bytea`输入函数看到的只是一个单引号，它将其视为普通数据字符。 但是，`bytea`输入函数将反斜杠视为特殊字符，[表 8.7][8.7]中显示的其他行为由该函数实现。

在某些情况下，反斜杠必须加倍，如上所示，因为通用的字符串文字解析器也会 将一对反斜杠减少为一个数据字符;请参阅[第 4.1.2.1 节][4.1.2.1]。

`Bytea`字节默认被输出为`hex`格式。如果你把[bytea\_output][bytea_output]改为`escape`，“不可打印的”字节会被转换成与之等效的三位八进制值并且前置一个反斜线。大部分“可打印的”字节被输出为它们在客户端字符集中的标准表示形式，例如：

SET bytea_output = 'escape';
    
    SELECT 'abc \153\154\155 \052\251\124'::bytea;
         bytea
    ----------------
     abc klm *\251T

十进制值为92（反斜线）的字节在输出时被双写。详情请见[表 8.8][8.8]。

**表 8.8. `bytea`输出转义字节**

<table> 
 <thead> 
  <tr> 
   <th>十进制字节值</th> 
   <th>描述</th> 
   <th>转义的输出表示</th> 
   <th>例子</th> 
   <th>输出结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>92</td> 
   <td>反斜线</td> 
   <td><code>\\</code></td> 
   <td><code>SELECT '\134'::bytea;</code></td> 
   <td><code>\\</code></td> 
  </tr> 
  <tr> 
   <td>0到31和127到255</td> 
   <td>“不可打印的”字节</td> 
   <td><code>\*</code>xxx<code>*</code>（八进制值）</td> 
   <td><code>SELECT '\001'::bytea;</code></td> 
   <td><code>\001</code></td> 
  </tr> 
  <tr> 
   <td>32到126</td> 
   <td>“可打印的”字节</td> 
   <td>客户端字符集表示</td> 
   <td><code>SELECT '\176'::bytea;</code></td> 
   <td><code>~</code></td> 
  </tr> 
 </tbody> 
</table>

根据你使用的PostgreSQL前端，你在转义和未转义`bytea`串方面可能需要做额外的工作。例如，如果你的接口自动翻译换行和回车，你可能也不得不转义它们。

# 六、日期/时间类型 #

PostgreSQL支持SQL中所有的日期和时间类型，如[表 8.9][8.9]所示。这些数据类型上可用的操作如[第 9.9 节][9.9]所述。日期根据公历来计算，即使对于该历法被引入之前的年份也一样（见[第 B.5 节][B.5]）。

**表 8.9. 日期/时间类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储尺寸</th> 
   <th>描述</th> 
   <th>最小值</th> 
   <th>最大值</th> 
   <th>解析度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>timestamp [ (*</code>p<code>*) ] [ without time zone ]</code></td> 
   <td>8字节</td> 
   <td>包括日期和时间（无时区）</td> 
   <td>4713 BC</td> 
   <td>294276 AD</td> 
   <td>1微秒</td> 
  </tr> 
  <tr> 
   <td><code>timestamp [ (*</code>p<code>*) ] with time zone</code></td> 
   <td>8字节</td> 
   <td>包括日期和时间，有时区</td> 
   <td>4713 BC</td> 
   <td>294276 AD</td> 
   <td>1微秒</td> 
  </tr> 
  <tr> 
   <td><code>date</code></td> 
   <td>4字节</td> 
   <td>日期（没有一天中的时间）</td> 
   <td>4713 BC</td> 
   <td>5874897 AD</td> 
   <td>1日</td> 
  </tr> 
  <tr> 
   <td><code>time [ (*</code>p<code>*) ] [ without time zone ]</code></td> 
   <td>8字节</td> 
   <td>一天中的时间（无日期）</td> 
   <td>00:00:00</td> 
   <td>24:00:00</td> 
   <td>1微秒</td> 
  </tr> 
  <tr> 
   <td><code>time [ (*</code>p<code>*) ] with time zone</code></td> 
   <td>12字节</td> 
   <td>仅仅是一天中的时间（没有日期），带有时区</td> 
   <td>00:00:00+1459</td> 
   <td>24:00:00-1459</td> 
   <td>1微秒</td> 
  </tr> 
  <tr> 
   <td><code>interval [ *</code>fields<code>* ] [ (*</code>p<code>*) ]</code></td> 
   <td>16字节</td> 
   <td>时间间隔</td> 
   <td>-178000000年</td> 
   <td>178000000年</td> 
   <td>1微秒</td> 
  </tr> 
 </tbody> 
</table>

## 注意 ##

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

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

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

## 6.1 日期/时间输入 ##

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

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

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

type [ (p) ] 'value'

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

### 6.1.1 日期 ###

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

**表 8.10. 日期输入**

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

### 6.1.2 时间 ###

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

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

**表 8.11. 时间输入**

<table> 
 <thead> 
  <tr> 
   <th>例子</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>04:05:06.789</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>04:05:06</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>04:05</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>040506</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>04:05 AM</code></td> 
   <td>和04:05一样，AM并不影响值</td> 
  </tr> 
  <tr> 
   <td><code>04:05 PM</code></td> 
   <td>和16:05一样，输入的小时必须为 &lt;= 12</td> 
  </tr> 
  <tr> 
   <td><code>04:05:06.789-8</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>04:05:06-08:00</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>04:05-08:00</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>040506-08</code></td> 
   <td>ISO 8601</td> 
  </tr> 
  <tr> 
   <td><code>04:05:06 PST</code></td> 
   <td>缩写指定的时区</td> 
  </tr> 
  <tr> 
   <td><code>2003-04-12 04:05:06 America/New_York</code></td> 
   <td>全名指定的时区</td> 
  </tr> 
 </tbody> 
</table>

**表 8.12. 时区输入**

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

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

### 6.1.3 时间戳 ###

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

1999-01-08 04:05:06

和：

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

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

January 8 04:05:06 1999 PST

也被支持。

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

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

是一个`timestamp without time zone`， 而

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'

如果一个文字已被确定是`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 节][9.9.3]）。

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

### 6.1.4 特殊值 ###

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

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

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

下列SQL-兼容的函数可以被用来为相应的数据类型获得当前时间值： `CURRENT_DATE`、`CURRENT_TIME`、 `CURRENT_TIMESTAMP`、`LOCALTIME`、 `LOCALTIMESTAMP`。后四种接受一个可选的亚秒精度声明（参见[第 9.9.4 节][9.9.4]）。注意这些是SQL函数并且在数据输入串中*不*被识别。

## 6.2 日期/时间输出 ##

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

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

<table> 
 <thead> 
  <tr> 
   <th>风格声明</th> 
   <th>描述</th> 
   <th>例子</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>ISO</code></td> 
   <td>ISO 8601, SQL标准</td> 
   <td><code>1997-12-17 07:37:16-08</code></td> 
  </tr> 
  <tr> 
   <td><code>SQL</code></td> 
   <td>传统风格</td> 
   <td><code>12/17/1997 07:37:16.00 PST</code></td> 
  </tr> 
  <tr> 
   <td><code>Postgres</code></td> 
   <td>原始风格</td> 
   <td><code>Wed Dec 17 07:37:16 1997 PST</code></td> 
  </tr> 
  <tr> 
   <td><code>German</code></td> 
   <td>地区风格</td> 
   <td><code>17.12.1997 07:37:16.00 PST</code></td> 
  </tr> 
 </tbody> 
</table>

### 注意 ###

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

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

**表 8.15. 日期顺序习惯**

<table> 
 <thead> 
  <tr> 
   <th><code>datestyle</code>设置</th> 
   <th>输入顺序</th> 
   <th>例子输出</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>SQL, DMY</code></td> 
   <td><em><code>日</code></em>/<em><code>月</code></em>/<em><code>年</code></em></td> 
   <td><code>17/12/1997 15:37:16.00 CET</code></td> 
  </tr> 
  <tr> 
   <td><code>SQL, MDY</code></td> 
   <td><em><code>月</code></em>/<em><code>日</code></em>/<em><code>年</code></em></td> 
   <td><code>12/17/1997 07:37:16.00 PST</code></td> 
  </tr> 
  <tr> 
   <td><code>Postgres, DMY</code></td> 
   <td><em><code>日</code></em>/<em><code>月</code></em>/<em><code>年</code></em></td> 
   <td><code>Wed 17 Dec 07:37:16 1997 PST</code></td> 
  </tr> 
 </tbody> 
</table>

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

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

### 6.2.1 时区 ###

时区和时区习惯不仅仅受地球几何形状的影响，还受到政治决定的影响。 到了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 节][51.92]）。PostgreSQL用广泛使用的 IANA 时区数据来实现该目的，因此相同的时区名字也可以在其他软件中被识别。
 *   一个时区缩写，例如`PST`。这样一种声明仅仅定义了到UTC的一个特定偏移，而不像完整时区名那样指出整套夏令时转换日期规则。能被识别的缩写被列在`pg_timezone_abbrevs`视图中（参见[第 51.91 节][51.91]）。你不能将配置参数[TimeZone][]或[log\_timezone][log_timezone]设置成一个时区缩写，但是你可以在日期/时间输入值和`AT TIME ZONE`操作符中使用时区缩写。
 *   除了时区名和缩写，PostgreSQL将接受POSIX-风格的 时区声明，形式为\*`STD`**`offset`\*或 \*`STD`**`offset`\*\*`DST`*， 其中*`STD`*是一个区域缩写、*`offset`*是从UTC西 起的以小时计的数字偏移量、*`DST`\*是一个可选的夏令时区域缩 写（被假定为给定偏移量提前一小时）。例如，如果`EST5EDT`还不是一 个被识别的区域名，它可以被接受并且可能和美国东海岸时间的功效相同。在这种语法中， 一个时区缩写可以是一个字母的字符串或者由尖括号（`<>`）包围 的任意字符串。当一个夏令时区域缩写出现时，会假定根据 IANA 时区数据库的 `posixrules`条目中使用的同一个夏令时转换规则使用它。 在一个标准的PostgreSQL安装中， `posixrules`和`US/Eastern`相同， 因此POSIX-风格的时区声明遵循美国的夏令时规则。如果需要，你可以通过替换 `posixrules`文件来调整这种行为。

简而言之，在缩写和全称之间是有不同的：缩写表示从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`例子所示，这并不是必须和那一天的本地 标准时间相同。

你应该注意到POSIX-风格的时区特性可能导致伪造的输入被接受，因为它没有对区域缩写合理性的检查。例如`SET TIMEZONE TO FOOBAR0`将会正常工作，让系统实际使用一个相当奇怪的UTC缩写。另一个需要记住的问题是在POSIX时区名中，正值的偏移量被用于格林威治*以西*的位置。在其他情况下，PostgreSQL将遵循 ISO-8601 惯例，认为正值的时区偏移量是格林威治*以东*。

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

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

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

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

### 6.2.2 间隔输入 ###

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

[@] 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 ...]]

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

**表 8.16. ISO 8601 间隔单位缩写**

<table> 
 <thead> 
  <tr> 
   <th>缩写</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Y</td> 
   <td>年</td> 
  </tr> 
  <tr> 
   <td>M</td> 
   <td>月（在日期部分中）</td> 
  </tr> 
  <tr> 
   <td>W</td> 
   <td>周</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>日</td> 
  </tr> 
  <tr> 
   <td>H</td> 
   <td>小时</td> 
  </tr> 
  <tr> 
   <td>M</td> 
   <td>分钟 (在时间部分中）</td> 
  </tr> 
  <tr> 
   <td>S</td> 
   <td>秒</td> 
  </tr> 
 </tbody> 
</table>

如果使用替代格式：

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][8.17]展示了一些有效`interval`输入的例子。

**表 8.17. 间隔输入**

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

在内部，`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

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

### 6.2.3 间隔输出 ###

间隔类型的输出格式可以被设置为四种风格之一：`sql_standard`、`postgres`、`postgres_verbose`或`iso_8601`，设置方法使用`SET intervalstyle`命令。默认值为`postgres`格式。[表 8.18][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. 间隔输出风格例子**

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

# 七、布尔类型 #

PostgreSQL提供标准的SQL类型`boolean`，参见[表 8.19][8.19]。`boolean`可以有多个状态：“true（真）”、“false（假）”和第三种状态“unknown（未知）”，未知状态由SQL空值表示。

**表 8.19. 布尔数据类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储字节</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>boolean</code></td> 
   <td>1字节</td> 
   <td>状态为真或假</td> 
  </tr> 
 </tbody> 
</table>

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

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

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

<table> 
 <thead> 
  <tr> 
   <th><code>false</code></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>no</code></td> 
  </tr> 
  <tr> 
   <td><code>off</code></td> 
  </tr> 
  <tr> 
   <td><code>0</code></td> 
  </tr> 
 </tbody> 
</table>

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

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

**例 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

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

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

# 八、枚举类型 #

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

## 8.1 枚举类型的声明 ##

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

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)

## 8.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)

## 8.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

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

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

## 8.4 实现细节 ##

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

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

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

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

# 九、几何类型 #

几何数据类型表示二维的空间物体。[表 8.20][8.20]展示了PostgreSQL中可以用的几何类型。

**表 8.20. 几何类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储尺寸</th> 
   <th>表示</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>point</code></td> 
   <td>16字节</td> 
   <td>平面上的点</td> 
   <td>(x,y)</td> 
  </tr> 
  <tr> 
   <td><code>line</code></td> 
   <td>32字节</td> 
   <td>无限长的线</td> 
   <td>{A,B,C}</td> 
  </tr> 
  <tr> 
   <td><code>lseg</code></td> 
   <td>32字节</td> 
   <td>有限线段</td> 
   <td>((x1,y1),(x2,y2))</td> 
  </tr> 
  <tr> 
   <td><code>box</code></td> 
   <td>32字节</td> 
   <td>矩形框</td> 
   <td>((x1,y1),(x2,y2))</td> 
  </tr> 
  <tr> 
   <td><code>path</code></td> 
   <td>16+16n字节</td> 
   <td>封闭路径（类似于多边形）</td> 
   <td>((x1,y1),…)</td> 
  </tr> 
  <tr> 
   <td><code>path</code></td> 
   <td>16+16n字节</td> 
   <td>开放路径</td> 
   <td>[(x1,y1),…]</td> 
  </tr> 
  <tr> 
   <td><code>polygon</code></td> 
   <td>40+16n字节</td> 
   <td>多边形（类似于封闭路径）</td> 
   <td>((x1,y1),…)</td> 
  </tr> 
  <tr> 
   <td><code>circle</code></td> 
   <td>24字节</td> 
   <td>圆</td> 
   <td>&lt;(x,y),r&gt;（中心点和半径）</td> 
  </tr> 
 </tbody> 
</table>

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

## 9.1 点 ##

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

( x , y )
      x , y

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

点使用第一种语法输出。

## 9.2 线 ##

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

{ A, B, C }

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

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

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

## 9.3 线段 ##

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

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

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

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

## 9.4 方框 ##

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

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

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

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

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

## 9.5 路径 ##

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

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

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

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

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

## 9.6 多边形 ##

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

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

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

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

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

## 9.7 圆 ##

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

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

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

圆的输出用第一种语法。

# 十、网络地址类型 #

PostgreSQL提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型，如[表 8.21][8.21]所示。 用这些数据类型存储网络地址比用纯文本类型好，因为这些类型提供输入错误检查以及特殊的操作符和函数（见[第 9.12 节][9.12]）

**表 8.21. 网络地址类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储尺寸</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>cidr</code></td> 
   <td>7或19字节</td> 
   <td>IPv4和IPv6网络</td> 
  </tr> 
  <tr> 
   <td><code>inet</code></td> 
   <td>7或19字节</td> 
   <td>IPv4和IPv6主机以及网络</td> 
  </tr> 
  <tr> 
   <td><code>macaddr</code></td> 
   <td>6字节</td> 
   <td>MAC地址</td> 
  </tr> 
  <tr> 
   <td><code>macaddr8</code></td> 
   <td>8 bytes</td> 
   <td>MAC地址（EUI-64格式）</td> 
  </tr> 
 </tbody> 
</table>

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

## 10.1 `inet` ##

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

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

## 10.2 `cidr` ##

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

[表 8.22][8.22]展示了一些例子。

**表 8.22. `cidr`类型输入例子**

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

## 10.3 `inet` vs. `cidr` ##

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

### 提示 ###

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

## 10.4 `macaddr` ##

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

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

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

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

## 10.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字节）。前导和拖尾的空格会被忽略。 下面是可以被接受的输入格式的例子：

这些例子都指定相同的地址。数字`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)

# 十一、位串类型 #

位串就是一串 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 节][4.1.2.5]获取有关位串常量的语法的信息。还有一些位逻辑操作符和串操作函数可用，请见[第 9.6 节][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

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

# 十二、文本搜索类型 #

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

## 12.1 `tsvector` ##

一个`tsvector`值是一个排序的可区分*词位*的列表，*词位*是被*正规化*合并了同一个词的不同变种的词（详见[第 12 章][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 $$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 '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

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

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

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

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

## 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'

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

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

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

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

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

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

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

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

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

因为`postgres`会被处理成`postgr`：

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

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

# 十三、UUID类型 #

数据类型`uuid`存储由RFC 4122、ISO/IEC 9834-8:2005以及相关标准定义的通用唯一标识符（UUID）（某些系统将这种数据类型引用为全局唯一标识符GUID）。这种标识符是一个128位的量，它由一个精心选择的算法产生，该算法能保证在已知空间中任何其他使用相同算法的人能够产生同一个标识符的可能性非常非常小。因此，对于分布式系统，这些标识符相比序列生成器而言提供了一种很好的唯一性保障，序列生成器只能在一个数据库中保证唯一。

一个UUID被写成一个小写十六进制位的序列，该序列被连字符分隔成多个组：首先是一个8位组，接下来是三个4位组，最后是一个12位组。总共的32位（十六进制位）表示了128个二进制位。一个标准形式的UUID类似于：

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}

输出总是采用标准形式。

PostgreSQL为UUID提供了存储和比较函数，但是核心数据库不包含任何用于产生UUID的函数，因为没有一个单一算法能够很好地适应每一个应用。[uuid-ossp][]模块提供了实现一些标准算法的函数。 [pgcrypto][]模块也为随机 UUID 提供了一个生成函数。 此外，UUID可以由客户端应用产生，或者由通过服务器端函数调用的其他库生成。

# 十四、XML类型 #

`xml`数据类型可以被用来存储XML数据。它比直接在一个`text`域中存储XML数据的优势在于，它会检查输入值的结构是不是良好，并且有支持函数用于在其上执行类型安全的操作，参见[第 9.14 节][9.14]。使用这种数据类型要求在安装时用`configure --with-libxml`选项编译。

`xml`类型可以存储格式良好的遵循XML标准定义的“文档”，以及“内容”片段，它是通过引用更宽泛的[“document node”][document node] XQuery 和 XPath 数据模型来定义的。 大致上说，这意味着内容片段中可以有多于一个的顶层元素或字符节点。 表达式`*`xmlvalue`* IS DOCUMENT`可以被用来评估一个特定的`xml`值是一个完整文档或者仅仅是一个文档片段。

`xml` 数据类型的限制和兼容性说明可以在 [第 D.3 节][D.3]中找到.

## 14.1 创建XML值 ##

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

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>')

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

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

也可以被使用。

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

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

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 };

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

SET xmloption TO { DOCUMENT | CONTENT };

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

## 14.2 编码处理 ##

在客户端、服务器以及其中流过的XML数据上处理多字符编码时必须要注意。在使用文本模式向服务器传递查询以及向客户端传递查询结果（在普通模式）时，PostgreSQL将所有在客户端和服务器之间传递的字符数据转换为目标端的字符编码，参见[第 23.3 节][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()`上，这是一个已知的问题。

## 14.3 访问XML值 ##

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

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

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

[CREATE TYPE]: http://www.postgres.cn/docs/12/sql-createtype.html
[8.1]: http://www.postgres.cn/docs/12/datatype.html#DATATYPE-TABLE
[8.2]: http://www.postgres.cn/docs/12/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
[4.1.2]: http://www.postgres.cn/docs/12/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
[9]: http://www.postgres.cn/docs/12/functions.html
[extra_float_digits]: http://www.postgres.cn/docs/12/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS
[CREATE TABLE]: http://www.postgres.cn/docs/12/sql-createtable.html
[9.16]: http://www.postgres.cn/docs/12/functions-sequence.html
[8.3]: http://www.postgres.cn/docs/12/datatype-money.html#DATATYPE-MONEY-TABLE
[lc_monetary]: http://www.postgres.cn/docs/12/runtime-config-client.html#GUC-LC-MONETARY
[8.4]: http://www.postgres.cn/docs/12/datatype-character.html#DATATYPE-CHARACTER-TABLE
[4.1.2.1]: http://www.postgres.cn/docs/12/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS
[23.3]: http://www.postgres.cn/docs/12/multibyte.html
[8.5]: http://www.postgres.cn/docs/12/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE
[8.6]: http://www.postgres.cn/docs/12/datatype-binary.html#DATATYPE-BINARY-TABLE
[bytea_output]: http://www.postgres.cn/docs/12/runtime-config-client.html#GUC-BYTEA-OUTPUT
[8.7]: http://www.postgres.cn/docs/12/datatype-binary.html#DATATYPE-BINARY-SQLESC
[8.8]: http://www.postgres.cn/docs/12/datatype-binary.html#DATATYPE-BINARY-RESESC
[8.9]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-DATETIME-TABLE
[9.9]: http://www.postgres.cn/docs/12/functions-datetime.html
[B.5]: http://www.postgres.cn/docs/12/datetime-units-history.html
[DateStyle]: http://www.postgres.cn/docs/12/runtime-config-client.html#GUC-DATESTYLE
[B]: http://www.postgres.cn/docs/12/datetime-appendix.html
[4.1.2.7]: http://www.postgres.cn/docs/12/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
[8.10]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE
[8.11]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-DATETIME-TIME-TABLE
[8.12]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-TIMEZONE-TABLE
[8.5.3]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-TIMEZONES
[TimeZone]: http://www.postgres.cn/docs/12/runtime-config-client.html#GUC-TIMEZONE
[9.9.3]: http://www.postgres.cn/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
[8.13]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-TABLE
[9.9.4]: http://www.postgres.cn/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
[8.14]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT-TABLE
[8.5.1]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-DATETIME-INPUT
[8.15]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT2-TABLE
[9.8]: http://www.postgres.cn/docs/12/functions-formatting.html
[51.92]: http://www.postgres.cn/docs/12/view-pg-timezone-names.html
[51.91]: http://www.postgres.cn/docs/12/view-pg-timezone-abbrevs.html
[log_timezone]: http://www.postgres.cn/docs/12/runtime-config-logging.html#GUC-LOG-TIMEZONE
[B.4]: http://www.postgres.cn/docs/12/datetime-config-files.html
[19]: http://www.postgres.cn/docs/12/runtime-config.html
[IntervalStyle]: http://www.postgres.cn/docs/12/runtime-config-client.html#GUC-INTERVALSTYLE
[8.16]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-INTERVAL-ISO8601-UNITS
[8.17]: http://www.postgres.cn/docs/12/datatype-datetime.html#DATATYPE-INTERVAL-INPUT-EXAMPLES
[8.18]: http://www.postgres.cn/docs/12/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE
[8.19]: http://www.postgres.cn/docs/12/datatype-boolean.html#DATATYPE-BOOLEAN-TABLE
[8.2 1]: http://www.postgres.cn/docs/12/datatype-boolean.html#DATATYPE-BOOLEAN-EXAMPLE
[ALTER TYPE]: http://www.postgres.cn/docs/12/sql-altertype.html
[pg_enum]: http://www.postgres.cn/docs/12/catalog-pg-enum.html
[8.20]: http://www.postgres.cn/docs/12/datatype-geometric.html#DATATYPE-GEO-TABLE
[9.11]: http://www.postgres.cn/docs/12/functions-geometry.html
[8.21]: http://www.postgres.cn/docs/12/datatype-net-types.html#DATATYPE-NET-TYPES-TABLE
[9.12]: http://www.postgres.cn/docs/12/functions-net.html
[8.22]: http://www.postgres.cn/docs/12/datatype-net-types.html#DATATYPE-NET-CIDR-TABLE
[4.1.2.5]: http://www.postgres.cn/docs/12/sql-syntax-lexical.html#SQL-SYNTAX-BIT-STRINGS
[9.6]: http://www.postgres.cn/docs/12/functions-bitstring.html
[8.3 1]: http://www.postgres.cn/docs/12/datatype-character.html
[12]: http://www.postgres.cn/docs/12/textsearch.html
[9.13]: http://www.postgres.cn/docs/12/functions-textsearch.html
[uuid-ossp]: http://www.postgres.cn/docs/12/uuid-ossp.html
[pgcrypto]: http://www.postgres.cn/docs/12/pgcrypto.html
[9.14]: http://www.postgres.cn/docs/12/functions-xml.html
[document node]: https://www.w3.org/TR/2010/REC-xpath-datamodel-20101214/#DocumentNode
[D.3]: http://www.postgres.cn/docs/12/xml-limits-conformance.html