PostgreSQL-数据类型1

一、数字类型

整数类型：

SQL 仅指定整数类型 integer（或 int）、smallint 和 bigint。类型名称 int2、int4 和 int8 是扩展，其他一些 SQL 数据库系统也使用它们。

数值类型

numeric 类型可以存储具有非常多位数的数字。特别推荐用于存储货币数量和其他需要精确性的数量。在可能的情况下，使用数值的计算会产生精确的结果，例如加法、减法、乘法。但是，与整数类型或浮点类型相比，数值的计算非常慢。

我们使用以下术语： 数字的精度是整数中有效位数的总数，即小数点两边的位数。数字的小数位数是小数部分中小数位数的计数，位于小数点右侧。所以数字 23.5141 的精度为 6，小数位数为 4。整数可以被视为小数位数为零。

没有任何精度或scale会创建一个“不受约束的数字”列，其中可以存储任意长度的数值，直到实现限制。这种类型的列不会将输入值强制为任何特定scale，而具有声明scale的数字列会将输入值强制为该scale。（SQL 标准要求默认scale为 0，即强制转换为整数精度。我们觉得这有点没用。如果您担心可移植性，请始终明确指定精度和scale。）

如果要存储的值的小数位数大于列的声明小数位数，系统会将值四舍五入到指定的小数位数。然后，如果小数点左边的位数超过声明的精度减去声明的小数位数，则会引发错误。

数值是物理存储的，没有任何额外的前导零或尾随零。因此，列的声明精度和比例是最大值，而不是固定分配。（在这个意义上，数字类型更类似于 varchar(n) 而不是 char(n)。）实际的存储要求是每组四个十进制数字需要两个字节，加上三到八个字节的开销。

除了普通的数值，数值类型还有几个特殊的值：

Infinity
-Infinity
NaN

它们改编自 IEEE 754 标准，分别代表“无穷大”、“负无穷大”和“非数”。在 SQL 命令中将这些值作为常量写入时，必须在它们周围加上引号，例如 UPDATE table SET x = '-Infinity'。在输入时，这些字符串以不区分大小写的方式被识别。无穷大值也可以拼写为 inf 和 -inf。

无穷大值的行为符合数学期望。例如，Infinity 加上任何有限值等于 Infinity，Infinity 加 Infinity 也是如此；但是 Infinity 减去 Infinity 会产生 NaN（不是数字），因为它没有明确定义的解释。请注意，无穷大只能存储在不受约束的数字列中，因为它在理论上超过了任何有限精度限制。

NaN（不是数字）值用于表示未定义的计算结果。通常，任何具有 NaN 输入的操作都会产生另一个 NaN。唯一的例外是当操作的其他输入使得如果将 NaN 替换为任何有限或无限数值时将获得相同的输出；然后，该输出值也用于 NaN。（这个原理的一个例子是，将 NaN 提高到零次方会产生 1。）

decimal 和 numeric 是等价的。

在舍入值时，数字类型从零开始舍入关系，而（在大多数机器上）实数和双精度类型将关系舍入到最接近的偶数。例如：

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;

浮点类型

数据类型实数和双精度是不精确的可变精度数值类型。

不精确意味着某些值无法精确转换为内部格式并存储为近似值，因此存储和检索值可能会显示出细微的差异。

在所有当前支持的平台上，real 类型的范围约为 1E-37 到 1E+37，精度至少为 6 位十进制数字。双精度类型的范围约为 1E-307 到 1E+308，精度至少为 15 位。值太大或太小都会导致错误。如果输入数字的精度太高，可能会发生舍入。太接近零且无法表示为与零不同的数字将导致下溢错误。

除了普通数值外，浮点类型还有几个特殊值：

Infinity
-Infinity
NaN

它们分别代表 IEEE 754 特殊值“无穷大”、“负无穷大”和“非数”。在 SQL 命令中将这些值作为常量写入时，必须在它们周围加上引号，例如 UPDATE table SET x = '-Infinity'。在输入时，这些字符串以不区分大小写的方式被识别。无穷大值也可以拼写为 inf 和 -inf。

PostgreSQL 还支持 SQL 标准表示法 float 和 float(p) 用于指定不精确的数字类型。这里，p 指定二进制数字的最小可接受精度。PostgreSQL 接受 float(1) 到 float(24) 作为选择真实类型，而 float(25) 到 float(53) 选择双精度。超出允许范围的 p 值会产生错误。没有指定精度的浮点数被认为是双精度。

Serial 类型

数据类型 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;

因为 smallserial、serial 和 bigserial 是使用序列实现的，所以即使没有删除任何行，列中出现的值序列也可能存在“漏洞”或间隙。即使包含该值的行从未成功插入到表列中，从序列分配的值仍然“用完”。例如，如果插入事务回滚，则可能会发生这种情况。

类型名称 serial 和 serial4 是等效的：都创建整数列。类型名称 bigserial 和 serial8 的工作方式相同，只是它们创建一个 bigint 列。如果您预计在表的生命周期内使用超过 2的31次方 个标识符，则应使用 bigserial。类型名称 smallserial 和 serial2 也以相同的方式工作，只是它们创建了一个 smallint 列。

删除所属列时，会自动删除为序列列创建的序列。您可以删除序列而不删除列，但这将强制删除列默认表达式。

二、货币类型
money 类型存储具有固定小数精度的货币金额。小数精度由数据库的 lc_monetary 设置确定。表中显示的范围假定有两个小数位。接受多种格式的输入，包括整数和浮点文字，以及典型的货币格式，例如“$1,000.00”。输出通常采用后一种形式，但取决于语言环境。

由于此数据类型的输出对区域设置敏感，因此将货币数据加载到具有不同 lc_monetary 设置的数据库中可能不起作用。为避免出现问题，在将转储恢复到新数据库之前，请确保 lc_monetary 具有与转储数据库中相同或等效的值。

numeric、int 和 bigint 数据类型的值可以转换为 money。从实数和双精度数据类型的转换可以通过先转换为数字来完成，例如：

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

但是，不建议这样做。由于可能出现舍入错误，不应使用浮点数来处理货币。

可以将货币值转换为数字而不会损失精度。转换为其他类型可能会丢失精度，并且还必须分两个阶段完成：

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

货币值除以整数值是通过将小数部分朝向零截断来执行的。要获得四舍五入的结果，请除以浮点值，或者在除以之前将货币值转换为数字，然后再转换为货币值。（后者最好避免冒精度损失的风险。）当一个货币值除以另一个货币值时，结果是双精度（即纯数字，而不是货币）；货币单位在除法中相互抵消。

三、字符类型

SQL 定义了两种主要的字符类型：character varying(n) 和 character(n)，其中 n 是一个正整数。这两种类型都可以存储最长为 n 个字符（不是字节）的字符串。尝试将较长的字符串存储到这些类型的列中将导致错误，除非多余的字符都是空格，在这种情况下，字符串将被截断为最大长度。（这个有点奇怪的例外是 SQL 标准所要求的。）如果要存储的字符串比声明的长度短，字符类型的值将被空格填充；字符变化类型的值将仅存储较短的字符串。

如果将一个值显式转换为字符可变（n）或字符（n），那么超长值将被截断为 n 个字符而不会引发错误。（这也是 SQL 标准所要求的。）

符号 varchar(n) 和 char(n) 分别是 character varying(n) 和 character(n) 的别名。没有长度说明符的字符等效于字符（1）。如果在没有长度说明符的情况下使用character varying，则该类型接受任何大小的字符串。后者是 PostgreSQL 扩展。

此外，PostgreSQL 还提供了 text 类型，可以存储任意长度的字符串。虽然类型文本不在 SQL 标准中，但其他几个 SQL 数据库管理系统也有它。

字符类型的值在物理上用空格填充到指定的宽度 n，并以这种方式存储和显示。但是，在比较两个字符类型的值时，尾随空格在语义上被视为无关紧要并被忽略。在空格很重要的排序规则中，这种行为可能会产生意想不到的结果；例如 SELECT 'a '::CHAR(2) collat​​e "C" < E'a\n'::CHAR(2) 返回 true，即使 C 语言环境会认为空格大于换行符。将字符值转换为其他字符串类型之一时，将删除尾随空格。请注意，尾随空格在字符变化和文本值中具有语义意义，并​​且在使用模式匹配时，即 LIKE 和正则表达式。

可以存储在任何这些数据类型中的字符由创建数据库时选择的数据库字符集决定。无论具体的字符集如何，都无法存储代码为零的字符（有时称为 NUL）。

短字符串（最多 126 个字节）的存储要求是 1 个字节加上实际字符串，其中包括字符情况下的空格填充。较长的字符串有 4 个字节的开销，而不是 1 个。长字符串由系统自动压缩，因此对磁盘的物理要求可能会更少。非常长的值也存储在后台表中，因此它们不会干扰对较短列值的快速访问。无论如何，可以存储的最长的字符串大约是 1 GB。（数据类型声明中允许的最大值 n 小于该值。更改此值没有用处，因为使用多字节字符编码，字符数和字节数可能完全不同。如果您希望存储没有特定上限的长字符串，使用character varying或text，而不是构成任意长度限制。）

这三种类型之间没有性能差异，除了在使用空白填充类型时增加了存储空间，以及在存储到长度受限的列时需要一些额外的 CPU 周期来检查长度。虽然 character(n) 在其他一些数据库系统中具有性能优势，但在 PostgreSQL 中没有这样的优势；事实上 character(n) 通常是三个中最慢的，因为它有额外的存储成本。在大多数情况下，应该使用文本或字符变化来代替。

例子：

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

CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES ('ok');
INSERT INTO test2 VALUES ('good      ');
INSERT INTO test2 VALUES ('too long');

INSERT INTO test2 VALUES ('too long'::varchar(5));
SELECT b, char_length(b) FROM test2;

PostgreSQL 中还有另外两种固定长度的字符类型。名称类型仅用于存储内部系统目录中的标识符，不适合一般用户使用。它的长度当前定义为 64 字节（63 个可用字符加上终止符），但应使用 C 源代码中的常量 NAMEDATALEN 来引用。长度是在编译时设置的（因此可以针对特殊用途进行调整）；默认的最大长度可能会在将来的版本中更改。"char" 类型（注意引号）与 char(1) 的不同之处在于它只使用一个字节的存储空间。它在系统目录内部用作简单的枚举类型。

四、二进制类型

二进制字符串是八位字节（或字节）的序列。二进制字符串与字符串有两种区别。首先，二进制字符串特别允许存储值为零的八位位组和其他“不可打印”八位位组（通常是十进制范围 32 到 126 之外的八位位组）。字符串不允许零八位字节，也不允许任何其他八位字节值和八位字节值序列根据数据库选择的字符集编码无效。其次，对二进制字符串的操作处理实际字节，而字符串的处理取决于区域设置。简而言之，二进制字符串适用于存储程序员认为是“原始字节”的数据，而字符串适用于存储文本。

bytea 类型支持两种输入和输出格式：“hex”格式和 PostgreSQL 历史上的“escape”格式。这两个总是在输入时被接受。输出格式取决于配置参数 bytea_output；默认值为十六进制。（请注意，十六进制格式是在 PostgreSQL 9.0 中引入的；早期版本和一些工具不理解它。）

SQL 标准定义了一种不同的二进制字符串类型，称为 BLOB 或 BINARY LARGE OBJECT。输入格式与 bytea 不同，但提供的函数和运算符大体相同。

bytea 十六进制格式

“十六进制”格式将二进制数据编码为每个字节 2 个十六进制数字，最重要的半字节在前。整个字符串前面是序列 \x （以将其与转义格式区分开来）。在某些情况下，最初的反斜杠可能需要通过加倍来转义。对于输入，十六进制数字可以是大写或小写，并且数字对之间允许有空格（但不能在数字对内，也不能在起始 \x 序列中）。十六进制格式兼容广泛的外部应用程序和协议，而且它的转换速度往往比转义格式更快，因此首选使用它。比如：

SELECT '\xDEADBEEF';

bytea 转义格式

“转义”格式是 bytea 类型的传统 PostgreSQL 格式。它采用将二进制字符串表示为 ASCII 字符序列的方法，同时将那些不能表示为 ASCII 字符的字节转换为特殊的转义序列。如果从应用程序的角度来看，将字节表示为字符是有意义的，那么这种表示会很方便。但在实践中，它通常会令人困惑，因为它模糊了二进制字符串和字符串之间的区别，而且选择的特定转义机制有些笨拙。因此，对于大多数新应用程序，应该避免这种格式。

以转义格式输入 bytea 值时，某些值的八位字节必须进行转义，而所有八位字节的值都可以转义。通常，要转义八位字节，请将其转换为其三位八进制值并在其前面加上反斜杠。反斜杠本身（八位十进制值 92）也可以用双反斜杠表示。下表显示了必须转义的字符，并在适用时给出了可选的转义序列。

转义不可打印八位字节的要求因区域设置而异。在某些情况下，您可以不让它们转义而侥幸逃脱。

默认情况下，Bytea 八位字节以十六进制格式输出。如果将 bytea_output 更改为转义，“不可打印”八位字节将转换为其等效的三位八进制值，并在前面加上一个反斜杠。大多数“可打印”八位字节都是通过客户端字符集中的标准表示形式输出的，例如：

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

     bytea
----------------
 abc klm *\251T

十进制值为 92（反斜杠）的八位字节在输出中加倍。详情见下表。

五、日期时间类型

SQL 标准要求只写时间戳等同于没有时区的时间戳，PostgreSQL 尊重这种行为。timestamptz 被接受为 timestamp with time zone 的缩写；这是一个 PostgreSQL 扩展。

time、timestamp 和 interval 接受一个可选的精度值 p，它指定秒字段中保留的小数位数。默认情况下，对精度没有明确的限制。p 的允许范围是从 0 到 6。

interval 类型有一个附加选项，即通过编写以下短语之一来限制存储字段集：

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

请注意，如果同时指定了字段和 p，则字段必须包含 SECOND，因为精度仅适用于秒。

带时区的类型 time 由 SQL 标准定义，但该定义显示的属性会导致有用性受到质疑。在大多数情况下，日期、时间、不带时区的时间戳和带时区的时间戳的组合应该提供任何应用程序所需的完整范围的日期/时间功能。

date和time

几乎可以接受任何合理格式的日期和时间输入，包括 ISO 8601、SQL 兼容、传统 POSTGRES 等。对于某些格式，日期输入中的日、月和年的顺序不明确，并且支持指定这些字段的预期顺序。将 DateStyle 参数设置为 MDY 选择月-日-年解释，DMY 选择日-月-年解释，或 YMD 选择年-月-日解释。

请记住，任何日期或时间文字输入都需要用单引号括起来，如文本字符串。SQL 需要以下语法：

type [ (p) ] 'value'

其中 p 是可选的精度规范，给出秒字段中的小数位数。可以为时间、时间戳和间隔类型指定精度，范围为 0 到 6。如果在常量规范中未指定精度，则默认为字面值的精度（但不超过 6 位）。

日期输入例子：

BC表示公元前

Times

时间类型是不带时区的时间 [ (p) ] 和带时区的时间 [ (p) ]。单独的时间相当于没有时区的时间。

时间输入：

时区输入：

timestamp
时间戳类型的有效输入包括日期和时间的串联，后跟可选的时区，后跟可选的 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 '2004-10-19 10:23:54'

是 timestamp without time zone。

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

是timestamp with time zone。PostgreSQL 在确定其类型之前从不检查文字字符串的内容，因此会将上述两者都视为没有时区的时间戳。为确保将文字视为带时区的时间戳，请为其指定正确的显式类型：

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

在已确定为无时区时间戳的文字中，PostgreSQL 将默默地忽略任何时区指示。也就是说，结果值源自输入值中的日期/时间字段，并且未针对时区进行调整。

对于带时区的时间戳，内部存储的值始终采用 UTC（通用协调时间，传统上称为格林威治标准时间，GMT）。使用该时区的适当偏移量将具有指定明确时区的输入值转换为 UTC。如果输入字符串中没有说明时区，则假定它位于系统的 TimeZone 参数指示的时区中，并使用时区的偏移量转换为 UTC。

当输出带有时区值的时间戳时，它总是从 UTC 转换为当前时区，并显示为该时区的本地时间。要查看另一个时区的时间，请更改时区或使用 AT TIME ZONE 构造。

不带时区的时间戳和带时区的时间戳之间的转换通常假定不带时区值的时间戳应取或给出为时区本地时间。可以使用 AT TIME ZONE 为转换指定不同的时区。

特殊值：

为了方便起见，PostgreSQL 支持几个特殊的日期/时间输入值。值infinity和-infinity在系统内部是专门表示的，将保持不变；但其他只是简单的符号速记，在读取时将转换为普通日期/时间值。（特别是，now 和相关的字符串在被读取后立即转换为特定的时间值。）当在 SQL 命令中用作常量时，所有这些值都需要用单引号括起来。

以下 SQL 兼容函数也可用于获取相应数据类型的当前时间值：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP。

 select current_date,current_time,current_timestamp,localtime,localtimestamp;

虽然现在、今天、明天和昨天的输入字符串可以很好地用于交互式 SQL 命令，但当命令被保存以供稍后执行时，它们可能会出现令人惊讶的行为，例如在准备好的语句、视图和函数定义中。该字符串可以转换为一个特定的时间值，该时间值在它变得陈旧后很长时间内仍然可以继续使用。在这种情况下，请改用其中一种 SQL 函数。例如，CURRENT_DATE + 1 比 'tomorrow'::date 更安全。

日期时间输出
日期/时间类型的输出格式可以设置为 ISO 8601、SQL (Ingres)、传统 POSTGRES（Unix 日期格式）或德语四种样式之一。默认为 ISO 格式。（SQL 标准要求使用 ISO 8601 格式。“SQL”输出格式的名称是历史偶然。）日期和时间类型的输出通常只有日期或时间部分，根据给定的示例。但是，POSTGRES 样式以 ISO 格式输出仅日期值。

在 ISO 样式中，时区始终显示为与 UTC 的带符号数字偏移，正号用于格林威治以东的区域。如果是整数小时，偏移量将显示为 hh（仅小时），如果是整数分钟，则显示为 hh:mm，否则显示为 hh:mm:ss。（第三种情况在任何现代时区标准中都不可能出现，但在处理采用标准化时区之前的时间戳时会出现。）在其他日期样式中，时区显示为字母缩写，如果一个是在当前区域常用。否则，它以 ISO 8601 基本格式（hh 或 hhmm）显示为带符号的数字偏移量。

用户可以使用 SET datestyle 命令、postgresql.conf 配置文件中的 DateStyle 参数或服务器或客户端上的 PGDATESTYLE 环境变量来选择日期/时间样式。

格式化函数 to_char也可以作为更灵活的方式来格式化日期/时间输出。

Time Zones
时区和时区惯例受到政治决策的影响，而不仅仅是地球几何形状。世界各地的时区在 1900 年代变得有些标准化，但仍然容易发生任意变化，特别是在夏令时规则方面。PostgreSQL 使用广泛使用的 IANA (Olson) 时区数据库来获取有关历史时区规则的信息。对于未来的时间，假设是给定时区的最新已知规则将在很远的将来继续无限期地遵守。

为了解决这些困难，我们建议在使用时区时使用包含日期和时间的日期/时间类型。我们不建议使用 time with time zone 类型（尽管 PostgreSQL 支持旧应用程序并符合 SQL 标准）。PostgreSQL 假定您的本地时区适用于仅包含日期或时间的任何类型。

所有可识别时区的日期和时间都以 UTC 格式在内部存储。在显示给客户端之前，它们会转换为 TimeZone 配置参数指定的区域中的本地时间。

PostgreSQL 允许您以三种不同的形式指定时区：

1.完整的时区名称，例如 America/New_York。识别的时区名称列在 pg_timezone_names 视图中。PostgreSQL 为此使用广泛使用的 IANA 时区数据，因此其他软件也可以识别相同的时区名称。

2.时区缩写，例如 PST。这样的规范仅定义了与 UTC 的特定偏移量，而全时区名称也可以暗示一组夏令时转换规则。公认的缩写列在 pg_timezone_abbrevs 视图中。您不能将配置参数 TimeZone 或 log_timezone 设置为时区缩写，但您可以在日期/时间输入值和 AT TIME ZONE 运算符中使用缩写。

3.除了时区名称和缩写，PostgreSQL 将接受 POSIX 样式的时区规范。此选项通常不比使用命名时区更可取，但如果没有合适的 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 示例一样，这不一定与该日期的当地民用时间相同。

在所有情况下，时区名称和缩写均不区分大小写。

时区名称和缩写都不是硬连线到服务器中的；它们是从存储在安装目录的 .../share/timezone/ 和 .../share/timezonesets/ 下的配置文件中获取的。

TimeZone 配置参数可以在文件 postgresql.conf 中设置。还有一些特殊的设置方法：

1.SQL 命令 SET TIME ZONE 设置会话的时区。这是 SET TIMEZONE TO 的另一种拼写，具有更符合 SQL 规范的语法。

2.libpq 客户端使用 PGTZ 环境变量在连接时向服务器发送 SET TIME ZONE 命令

**输入Interval **

可以使用以下详细语法编写Interval ：

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

其中quantity 是一个数字（可能有符号）；单位是 microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium或这些单位的缩写或复数；；方向可以是以前或为空。at 符号 (@) 是可选噪声。不同单位的数量通过适当的符号会计隐式添加。ago 否定所有字段。如果 IntervalStyle 设置为 postgres_verbose，此语法也用于间隔输出。

无需明确的单位标记即可指定天、小时、分钟和秒的数量。例如，“1 12:59:10”的读取方式与“1 天 12 小时 59 分 10 秒”相同。此外，可以用破折号指定年份和月份的组合；例如，“200-10”与“200 年 10 个月”的读法相同。（这些较短的形式实际上是 SQL 标准所允许的唯一形式，并且在 IntervalStyle 设置为 sql_standard 时用于输出。）

间隔值也可以写为 ISO 8601 时间间隔。带指示符的格式如下所示：

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

该字符串必须以 P 开头，并且可能包含一个 T 来引入时间单位。可用的单位缩写在下表中给出。单位可以省略，也可以按任意顺序指定，但小于天的单位必须出现在 T 之后。特别是，M 的含义取决于它是在 T 之前还是之后。

在替代格式中：

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 周”或“01:02:03.45”。但是，因为 interval 在内部仅存储三个整数单位（月、天、微秒），所以必须将小数单位溢出到更小的单位。大于月的单位的小数部分被截断为整数月，例如“1.5 年”变为“1 年 6 月”。周和天的小数部分被计算为天数和微秒的整数，假设每月 30 天和每天 24 小时，例如，“1.75 个月”变为 1 星期一 22 天 12:00:00。只有秒会在输出中显示为小数。

select interval '1-2';
select interval '3 4:05:05';
select interval '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';
select interval 'P1Y2M3DT4H5M6S';
select interval 'P0001-02-03T04:05:06';

内部间隔值存储为月、天和微秒。这样做是因为一个月中的天数不同，如果涉及夏令时调整，一天可以有 23 或 25 小时。月和日字段是整数，而微秒字段可以存储小数秒。因为间隔通常是由常量字符串或时间戳减法创建的，所以这种存储方法在大多数情况下效果很好，但可能会导致意想不到的结果：

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

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

**输出Interval **

可以使用命令 SET intervalstyle 将间隔类型的输出格式设置为 sql_standard、postgres、postgres_verbose 或 iso_8601 四种样式之一。默认为 postgres 格式。

六、Boolean 类型

PostgreSQL 提供标准 SQL 类型 boolean。布尔类型可以有几种状态：“真”、“假”和第三种状态“未知”，它由 SQL 空值表示。

布尔常量可以在 SQL 查询中由 SQL 关键字 TRUE、FALSE 和 NULL 表示。

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

    true
    yes
    on
    1

以及“假”状态的这些表示：

  false
  no
  off
  0

也接受这些字符串的唯一前缀，例如 t 或 n。前导或尾随空格被忽略，大小写无关紧要。

boolean 类型的数据类型输出函数始终发出 t 或 f：

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');

SELECT * FROM test1;

关键字 TRUE 和 FALSE 是在 SQL 查询中编写布尔常量的首选（SQL 兼容）方法。但是也可以用通用字符串文字常量语法来使用字符串表示，例如 'yes'::boolean。

请注意，解析器会自动理解 TRUE 和 FALSE 是布尔类型，但对于 NULL 则不是这样，因为它可以具有任何类型。因此，在某些情况下，您可能必须将 NULL 显式转换为布尔值，例如 NULL::boolean。相反，在解析器可以推断出文字必须是布尔类型的上下文中，可以从字符串文字布尔值中省略强制转换。