PostgreSQL 数据类型简介

PostgreSQL 中有哪些数据类型？

在深入探讨之前，让我们概览一下 PostgreSQL 提供的数据类型。

PostgreSQL 支持各种适用于简单和复杂数据类型的数据类型。这些包括：

• integer
• smallint
• bigint
• serial
• smallserial
• bigserial
• numeric
• float
• double precision
• money
• char
• varchar
• text
• boolean
• date
• time
• timestamp
• timestamptz
• interval
• enum
• uuid
• json
• jsonb
• xml
• inet (网络地址)
• cidr (网络地址)
• macaddr
• polygon
• line
• lseg (线段)
• box (矩形框)
• bytea (十六进制格式)
• tsvector (文本搜索)
• tsquery (文本搜索)

我们将在本指南中更深入地介绍其中最常见的类型。

PostgreSQL 数据类型入门

PostgreSQL 软件本身内置了大量类型。它还允许你通过组合不同类型的类型并指定其参数来定义自己的复杂类型。这使得管理员在使用 CREATE TABLE 等命令时，能够精确定义他们期望每个列接受的数据类型。然后 PostgreSQL 可以自动检查提议的值，以确保它们符合提供的标准。

当你开始使用类型时，重要的是要记住，类型本身并不总是数据验证的完整解决方案，而只是一个组成部分。其他数据库工具，如约束，在定义正确性方面也发挥着作用。尽管如此，数据类型通常是抵御无效数据的第一道防线。

在许多情况下，PostgreSQL 提供的通用类型适用于你将存储的数据种类。然而，有时也有更具体的类型可用，它们可以提供额外的运算符、关联函数或内置的类似约束的验证。例如，虽然你可以将几何点的坐标存储在两个不同的数字列中，但提供的point 类型是专门用于存储和验证此类信息的。选择类型时，请检查你是否使用了最适用于你的用例的特定类型。

整数

整数（integer）数据类型是一类用于存储不带小数或分数的数字的类型。它们可以是正值或负值，不同的整数类型可以存储不同范围的数字。可接受值范围较小的整数类型占用的空间少于范围较宽的类型。

整数类型的基本列表包括以下内容：

上述类型受其有效范围限制。任何超出范围的值都将导致错误。

除了上述标准整数类型，PostgreSQL 还包含一组特殊的整数类型，称为序列（serial）类型。这些类型主要用于为记录创建唯一标识符或主键（primary keys）。

默认情况下，当添加新记录时，序列类型将自动使用内部跟踪序列中的下一个整数。因此，如果序列列中使用的上一个整数是 8559，默认情况下，下一个记录将自动使用 8560。为保证每个值都是唯一的，你可以在该列上进一步添加 UNIQUE 约束。

对于前面提到的每种整数大小，都有一种序列类型，这决定了序列中使用的整数类型：

• serial: 一种序列类型，自动将列递增为下一个 integer 值。
• smallserial: 一种序列类型，自动将列递增为下一个 smallint 值。
• bigserial: 一种序列类型，自动将列递增为下一个 bigint 值。

任意精度

任意精度类型用于控制带小数的数字的精度（precision）或特异性。在 PostgreSQL 中，这可以通过操作两个因素来控制：精度（precision）和标度（scale）。

精度（Precision）是一个数字可以拥有的总位数上限。相比之下，标度（scale）是小数点右侧的位数。通过操纵这些数字，你可以控制一个数字的小数部分和非小数部分允许的大小。

这两个参数用于使用numeric数据类型控制任意精度。numeric 类型接受零到两个参数。

没有参数时，列可以存储任何精度和标度的值。

NUMERIC

当提供单个参数时，它被解释为列的精度，标度设置为 0。虽然在磁盘上不是这样存储的，但这实际上允许你指定一个整数类数字（无小数部分或十进制部分）的最大位数。例如，如果你需要一个 5 位整数，你可以指定：

NUMERIC(5)

当使用两个控制参数配置列时，请指定精度后跟标度。PostgreSQL 将使用标度数字将任何输入的十进制部分四舍五入到正确的位数。之后，它将检查完整的四舍五入数字（包括整数部分和小数部分）是否超过给定的精度数字。如果超过，PostgreSQL 将产生一个错误。

例如，我们可以指定一个总精度为 5，标度为 2 的列：

NUMERIC(5, 2)

此列将具有以下行为：

浮点数

浮点数是表达小数的另一种方式，但没有精确、一致的精度。相反，浮点类型只有最大精度的概念，这通常与硬件的架构和平台有关。

例如，要将浮点列限制为 8 位精度，你可以输入：

FLOAT(8)

由于这些设计选择，浮点数可以高效地处理大量小数，但并非总是精确的。数字的内部表示可能导致输入和输出之间存在细微差异。这在比较值、进行浮点运算或执行需要精确值的操作时，可能导致意外行为。

双精度（浮点）vs 数值类型

由 float 和 double precision 等类型提供的浮点数，以及由 numeric 类型提供的任意精度数，都可以用于存储十进制值。你如何知道应该使用哪种？

一般规则是，如果你的计算需要精确性，那么 numeric 类型始终是更好的选择。numeric 类型会精确存储所提供的值，这意味着在检索或计算值时，结果是完全可预测的。numeric 类型被称为任意精度，因为你指定了该类型所需的精度量，它将在字段中存储精确的位数。

相比之下，像 float 和 double precision 这样的类型是可变精度类型。它们保持的精度量取决于输入值。当它们达到允许的精度级别末尾时，可能会对剩余数字进行四舍五入，导致提交值和检索值之间存在差异。

那么什么时候会使用可变精度类型呢？像 float 和 double precision 这样的可变精度类型非常适合不需要精确值（例如，反正会被四舍五入）且对速度要求很高的场景。可变精度通常比 numeric 类型提供更好的性能。

货币值

PostgreSQL 包含一个特殊的 money 类型，用于存储表示货币单位的数值。

money 类型不接受参数，因此列定义只使用该类型。

MONEY

money 类型具有固定的分数部分，其精度取自 PostgreSQL 本地化选项 lc_monetary。如果该变量未定义，则精度取自 Linux 或类 Unix 环境中的 LC_MONETARY 环境变量，或在其他操作系统中的等效语言环境设置。在许多情况下，精度将设置为两位小数以符合常用习惯。

由于这种精度，建议仅在不存在或不重要分厘的情况下使用 money 类型。同样，由于该类型不附带货币信息，因此不适用于需要货币转换的情况。然而，money 类型对于简单用例具有出色的性能，因此尽管存在这些限制，它仍然很有价值。

由于依赖于 PostgreSQL 安装或执行环境的语言环境设置，money 值在不同系统之间传输数据时，务必确保这些值匹配。

将值转换为 money 类型或从其转换时也必须小心，因为它在某些类型之间转换时可能会丢失精度数据。将 money 值转换为 numeric 类型（如上所示，用于任意精度）或从其转换是安全的，因此建议在转换为其他类型之前始终使用 numeric 作为中间类型。

日期

date 类型可以存储不带关联时间值的日期。

DATE

在处理 date 列的输入时，PostgreSQL 可以解释许多不同的格式来确定要存储的正确日期。有些格式基于众所周知的标准，而另一些则是许多实际场景中使用的口语格式。

PostgreSQL 理解的日期输入格式的完整范围显示在PostgreSQL 文档中的“日期输入”表中。

为了处理模糊的输入，例如 07/12/2019（根据格式，这可能被解释为 2019 年 7 月 12 日或 2019 年 12 月 7 日），你可以使用 DateStyle 参数设置预期的排序。它可以设置为 DMY、MDY 或 YMD 来定义预期的排序。默认情况下，PostgreSQL 会将其设置为 MDY 或使用 lc_time 区域设置来确定适当的排序。

PostgreSQL 还可以使用各种格式输出日期：

• ISO：根据ISO 8601输出日期。2009 年 3 月 18 日将表示为 2009-03-18。
• SQL：传统的 SQL 日期格式。2009 年 3 月 18 日将表示为 03/18/2009。
• Postgres：日期采用 ISO 格式。2009 年 3 月 18 日将表示为 2009-03-18。
• German：德国地区风格。2009 年 3 月 18 日将表示为 18.03.2009。

SQL 和 Postgres 格式都遵循前面提到的 DateStyle 值，以确定输出中月份、日期和年份的顺序。

时间

time 数据类型（也称为 time without time zone）可以存储一天中的特定时间，而不带关联的时区或日期。

PostgreSQL 不建议使用 time with time zone，它是 time 类型的一种变体，将时区与时钟时间配对。这是由于在没有额外上下文（如关联日期）的情况下无法解决的复杂性和歧义。对于需要时区部分的时间，下一节介绍的 timezonetz 类型是一个很好的替代方案，它提供了日期组件的上下文。

在处理 time 列的输入时，PostgreSQL 可以解释许多不同的格式来确定要存储的正确时间。其中大多数是 ISO 8601 标准的变体，并具有捕获不同变体的灵活性。

PostgreSQL 理解的时间输入格式的完整范围显示在PostgreSQL 文档中的“时间输入”表中。

PostgreSQL 可以存储具有微秒分辨率的时间值。精度量可以在创建列时通过指定一个“（p）”或精度值来定义，该值可以是 0 到 6 之间的任何整数。

例如，要存储小数秒精度为 3 位的时间值，你可以这样定义时间列：

TIME (3)

如果未提供 (p) 值，列将根据输入的精度进行存储，最多保留 6 位小数。

输出时间时，PostgreSQL 依赖于与日期选项相同的格式定义。这些大多会产生相同或相似的输出：

• ISO：根据ISO 8601输出时间。下午 04:28 和 52 秒将表示为 16:28:52。
• SQL：传统的 SQL 时间格式。下午 04:28 和 52 秒将表示为 16:28:52.00。
• Postgres：使用 Unix 日期/时间格式。下午 04:28 和 52 秒将表示为 16:28:52。
• German：德国地区风格。下午 04:28 和 52 秒将表示为 16:28:52.00。

如你所见，输出格式对时间表示的影响不如对日期那么大。主要区别可以在我们接下来会看到的时间戳输出中体现。

间隔

PostgreSQL 还可以存储和处理表示时间间隔的值。这些值基本上描述了两个特定时间戳之间的时间量。

与 time、timestamp 和 timestamptz 一样，interval 数据类型可以表示微秒级别的时间差。同样，(p) 参数用于表示数字将记录的精度量或小数位数，范围为零到六。

INTERVAL (3)

虽然 (p) 参数影响秒的小数部分的存储方式，但 interval 类型还有另一个参数可以更普遍地改变特定性。通过在定义 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：仅存储分钟数和秒数

当同时指定要存储的字段和精度时，字段在前。因此，要创建一个精度为 5 位、只存储给定间隔的天数、小时数、分钟数和秒数的列，你可以输入：

INTERVAL DAY TO SECOND (5)

请注意，只有当你的声明字段范围包含秒值时，你才能指定精度，因为这是此参数唯一有意义的情况。

向 interval 列添加值时，有多种不同的输入格式。最直接的方法是指定要提供的每个列或字段的数量和单位，用空格分隔。例如：

7 days 3 hours 27 minutes 8 seconds

要指示间隔是反向计数的，请在末尾添加 ago 关键字。

7 days 3 hours 27 minutes 8 seconds ago

上述间隔也可以不带单位表示，方法是提供一个日期字段，后跟用冒号分隔的时钟单位。

7 3:27:08

同样，仅表示年和月的间隔可以用年、破折号和月表示。因此 38 年 4 个月将如下所示：

38-4

PostgreSQL 还支持基于ISO 8601 时间戳的缩写输入，这可用于表示使用更多字段的间隔。基于此标准有两种独立的输入格式。

第一种使用以下单位缩写，由以下字段的粗体部分表示：

• 年（Y）
• 月（M）
• 周（W）
• 日（D）
• 小时（H）
• 分钟（M）
• 秒（S）

你可能会注意到 M 既用于表示月份也用于表示分钟。在这种格式中，日期组件与时间组件通过一个“T”分隔。如果 M 出现在 T 之前，则解释为月份；如果出现在 T 之后，则表示分钟。

基于 ISO 8601 的格式以 P 开头，然后包含间隔字符串。因此，要表示 4 年、2 个月、7 天、3 小时、27 分钟和 8 秒的间隔，以下字符串将有效：

P4Y2M7DT3H27M8S

另一种 ISO 8601 格式根本不使用单位缩写。相反，它用破折号分隔日期组件，用冒号分隔时间组件。同样，字符串以 P 开头，并用 T 分隔日期和时间组件。前面表达的相同间隔可以写成：

P4-2-7T3:27:8

PostgreSQL 可以以多种格式从 interval 列输出值。输出样式由 intervalstyle 设置决定，可以是以下之一：

• postgres：默认样式。此格式使用 years、mons 和 days 单位，每个单位用空格分隔以表示日期组件。它使用 HH:MM:SS 来表示时间组件。
• postgres_verbose：此格式以“@”符号开头。它包含所有字段的单位或单位缩写，以空格分隔：years、mons、days、hours、mins 和 secs。
• sql_standard：遵循 SQL 标准输出规范。年和月之间用破折号分隔：YYYY-MM。之后，日和时间组件由独立的日字段和 HH:MM:SS 时间字段表示。完整的表示形式将如下所示：YYYY-MM D HH:MM:SS
• iso_8601：此样式生成符合 ISO 8601“带指示符的格式”的输出（上面描述的第一种 ISO 8601 样式）。用实际值替换井号，输出将如下所示：P#Y#M#DT#H#M#S

PostgreSQL 是否支持用户定义类型？

PostgreSQL 以几种不同的方式支持用户定义类型。

CREATE TYPE season AS ENUM ('winter', 'spring', 'summer', 'autumn');

SEASON

UUID

通用唯一标识符（Universally Unique Identifiers），或称 UUID，是用于唯一标识信息片段的 128 位数字。它们在许多不同的上下文中用于分配一个极不可能在其他地方重复的全局标识符。PostgreSQL 包含 uuid 类型以处理这些值。

UUID

UUID 有 32 位数字，通常在视觉上用连字符分为五组（8 位、4 位、4 位、4 位，最后是 12 位）。

########-####-####-####-############

每个占位符包含一个十六进制数字（0 到 9，加上小写字母“a”到“f”）。PostgreSQL 使用此标准格式进行输出。

对于输入，PostgreSQL 理解多种格式，包括使用大写字母、不同的数字分组、无数字分组以及用花括号括住 UUID。

JSON

PostgreSQL 支持使用 json 和 jsonb 格式的 JSON 列。以 json 形式存储的数据按原样存储，而以 jsonb 形式存储的数据会被解释、处理并以二进制形式存储，以加快执行和处理速度。PostgreSQL 还可以为 jsonb 列建立索引，以获得更好的性能。因此，一般来说，建议将 jsonb 用于 JSON 数据。

JSONB

这两种格式之间存在一些细微差别。json 类型保留了意外的空白、键的顺序和重复的键。jsonb 格式删除了无关紧要的空白，覆盖重复的键，并且不提供键的顺序保证。

PostgreSQL 包含JSON 运算符，可以索引 jsonb 列，测试jsonb 对象是否包含其他 jsonb 对象，并且可以将值转换为不同语言中使用的数据类型。这些内容超出了本指南的范围，但将在未来关于使用 PostgreSQL 处理 JSON 的文章中介绍。