Postgresql官方文档之Data Types

8.1. Numeric Types

PostgreSQL 中的数值类型，这包括了两位、四位和八位的整数类型，四位和八位的浮点数类型，以及可选择精度的小数类型。具体的类型有：

• smallint（2字节，小范围整数，范围是 -32768 到 +32767）
• integer（4字节，常用的整数类型，范围是 -2147483648 到 +2147483647）
• bigint（8字节，大范围整数，范围是 -9223372036854775808 到 +9223372036854775807）
• decimal（可变大小，用户指定的精度，精确数值，可以存储最多 131072 位的数字在小数点前，以及最多 16383 位的数字在小数点后）
• numeric（与 decimal 类型相同）
• real（4字节，变精度，不准确，精度大约是 6 位十进制数字）
• double precision（8字节，变精度，不准确，精度大约是 15 位十进制数字）
• smallserial（2字节，小的自增整数）
• serial（4字节，自增整数）
• bigserial（8字节，大的自增整数）
  这些类型支持对应的算术运算符和函数。对于需要高精度和准确性的场景（如金融计算），推荐使用 numeric 类型。而对于需要处理大量数据且可以容忍一定精度损失的场景，可以使用浮点数类型（real 和 double precision）。整数类型（smallint、integer、bigint）用于处理整数值，它们在存储大小和范围上有所不同。自增类型（smallserial、serial、bigserial）常用于自动生成唯一标识符。

8.1.1. Integer Types

用于存储整数的数据类型，这包括 smallint、integer 和 bigint 类型。

• smallint: 用于存储小范围整数，占用 2 字节空间，范围是 -32768 到 +32767。
• integer: 是最常用的整数类型，占用 4 字节空间，范围是 -2147483648 到 +2147483647。这个类型提供了最佳的平衡，考虑到范围、存储大小和性能。
• bigint: 用于存储大范围整数，占用 8 字节空间，范围是 -9223372036854775808 到 +9223372036854775807。这个类型设计用于当 integer 类型的范围不够用时。
  这些类型存储的都是没有小数部分的数字，即整数。尝试存储超出它们允许范围的值将会导致错误。
  smallint 类型通常在磁盘空间非常宝贵时使用。对于大多数应用，integer 类型是首选，因为它提供了足够的范围同时保持了良好的性能。bigint 类型适用于需要存储非常大数值的场景。
  SQL 标准只规定了 integer（或 int）、smallint 和 bigint 这三种整数类型。int2、int4 和 int8 是 PostgreSQL 特有的扩展名，也被一些其他的 SQL 数据库系统使用。

8.1.2. Arbitrary Precision Numbers

PostgreSQL 中的 arbitrary precision numbers，即可以存储具有非常大数量级的数字，且具有用户指定的精度的数值类型。这主要涉及到 numeric 类型（也可以称作 decimal 类型），它允许用户定义数字的精度和标度（即小数点前后的位数）。- numeric[ (p, s) ] / decimal[ (p, s) ]: 这些类型用于存储精确的数值，其中 p 代表总的有效数字位数（精度），s 代表小数点后的位数（标度）。例如，NUMERIC(5,2) 可以存储的最大数值为 999.99。这种类型特别适合于需要高精度计算的场合，如金融领域的货币计算。
numeric 类型提供精确的算术运算，这意味着加法、减法和乘法运算的结果总是精确的。但是，需要注意的是，与整数类型或浮点类型相比，numeric 类型的计算通常会更慢。
此外，numeric 类型支持非常大的数字。具体来说，它可以支持最多 131072 位的数字在小数点前，以及最多 16383 位的数字在小数点后。这使得 numeric 类型非常适合存储超过传统整数和浮点数类型范围的数值。
numeric 类型的声明可以有几种形式，例如 NUMERIC(precision, scale) 其中 precision 是必须的而 scale 可以是可选的。如果不指定 scale，则默认为 0。如果完全不指定 precision 和 scale（即仅声明为 NUMERIC），则该列可以存储任何长度的数字，直到实现的限制。

8.1.3. Floating-Point Types

PostgreSQL 中用于存储浮点数的数据类型，这包括 real 和 double precision 类型。这些数据类型用于存储可以有小数部分的数值，并且这些数值是以浮点数的形式存储的，意味着它们是不准确的，可以用于那些不需要完全精确数值的场景。

• real: 也被称为 float4，使用 4 字节存储，是单精度浮点数类型。它提供大约 6 位十进制数字的精度。
• double precision: 也被称为 float8，使用 8 字节存储，是双精度浮点数类型。它提供大约 15 位十进制数字的精度。
  这些浮点类型是基于 IEEE Standard 754 for Binary Floating-Point Arithmetic 来实现的。由于它们是不准确的，一些值可能无法被准确地转换为内部格式并存储，因此存储和检索值可能会出现轻微的不一致。
  浮点数类型在进行数学计算时特别有用，尤其是当涉及到非常大或非常小的数值时。然而，如果你需要精确的数值计算（例如金融计算），应该使用 numeric 类型而不是浮点数类型。
  浮点类型的一个重要注意事项是比较操作。由于浮点数的不准确性，直接比较两个浮点数是否相等可能不会像预期那样工作。在处理浮点数时，通常需要考虑一个小的误差范围来判断数值是否“足够接近”相等。
  此外，浮点类型还支持特殊的值：正无穷大、负无穷大和 NaN（不是一个数字）。这些特殊值允许浮点数类型表示超出其正常数值范围的计算结果。

8.1.4. Serial Types

PostgreSQL 中的自增类型，这是用于自动生成唯一标识符（通常用于主键）的数据类型。这些类型包括 smallserial、serial 和 bigserial。

• smallserial: 使用 2 字节存储，是一个小范围的自增整数，自动从 1 开始递增，最大可以达到 32767。
• serial: 使用 4 字节存储，是一个中等范围的自增整数，自动从 1 开始递增，最大可以达到 2147483647。
• bigserial: 使用 8 字节存储，是一个大范围的自增整数，自动从 1 开始递增，最大可以达到 9223372036854775807。
  这些类型在创建表时非常有用，特别是当你需要一个自动生成的唯一标识符作为表的主键时。使用这些类型时，你不需要在插入新记录时手动指定这个字段的值；数据库会自动为你生成一个新的唯一值。
  需要注意的是，这些自增类型并不是真正的数据类型，而是一种快捷方式，用于创建带有适当默认值和关联序列的整数列。例如，声明一个列为 serial 类型实际上是在后台执行了以下操作：

1. 创建一个序列。
2. 创建一个整数列，并将其默认值设置为序列的下一个值。
3. 设置序列的所有权，使其与新列关联。
   这意味着，即使删除了使用这些类型的列，关联的序列仍然存在，除非它们被显式删除或通过 OWNED BY 选项与列关联并随列一起删除。
   使用这些自增类型时，可能会出现“空洞”或间隔，特别是在发生回滚或并发插入时。一旦从序列中获取了一个值用于插入，即使插入操作失败或被回滚，该值也不会被重新使用。

8.2. Monetary Types

PostgreSQL 中用于存储货币金额的数据类型，即 money 类型。

• money: 用于存储带有货币符号的金额数值，其存储大小为 8 字节。这个类型可以表示的范围是 -92233720368547758.08 到 +92233720368547758.07。
  money 类型主要用于处理涉及货币的计算，它自动提供了货币符号和小数点后两位的精度（这可能会根据区域设置而变化）。使用 money 类型可以简化涉及货币的处理，因为它直接支持货币格式，避免了将数值类型与格式化分开处理的复杂性。
  然而，使用 money 类型也有一些限制和需要注意的地方：
• 货币符号和格式化是基于数据库的区域设置（lc_monetary 参数）进行的，这意味着在不同的区域设置中，相同的 money 值可能会以不同的方式显示。
• 对于需要高精度和可控制的舍入行为的金融计算，更推荐使用 numeric 类型，因为 money 类型的计算精度和舍入行为可能不够精确或不可预测。
• money 类型的操作和计算可能不如 numeric 类型那样灵活，特别是在涉及不同货币和汇率转换的情况下。
  尽管 money 类型在处理简单的货币计算时很方便，但在复杂的金融应用中，使用 numeric 类型可能会更合适，因为它提供了更高的精度和更灵活的控制。

8.3. Character Types

PostgreSQL 中用于存储文本数据的数据类型。这些类型包括了 character varying(n), varchar(n), character(n), char(n) 以及 text。

• character varying(n) 或 varchar(n): 可以存储最多 n 个字符的可变长度字符串。如果插入的字符串长度小于 n，PostgreSQL 不会填充额外的空格。如果字符串超过了 n 个字符，会引发错误。不指定长度 n 的情况下，默认为可变长度，没有长度限制。
• character(n) 或 char(n): 存储固定长度为 n 的字符串。如果插入的字符串长度小于 n，PostgreSQL 会用空格填充至 n 长度。如果字符串超过了 n 个字符，会引发错误。如果不指定长度 n，那么默认长度为 1。
• text: 可以存储任意长度的字符串。与 varchar 类似，但是没有长度限制。
  这些字符类型之间的主要区别在于它们是否支持固定长度以及是否自动填充空格。在大多数情况下，text 和 varchar 类型是更加灵活的选择，因为它们不强制实施字符串长度限制。character(n) 或 char(n) 类型在特定情况下有用，例如当数据有确切的固定宽度时（如一些旧系统的数据导入）。
  需要注意的是，尽管 SQL 标准定义了 varchar 和 char 类型，但在 PostgreSQL 中，text 类型的性能与 varchar 和 char 类型相当，因此在选择数据类型时，通常不需要基于性能来区分这些类型。实际上，text 类型因其灵活性而被广泛使用。

8.4. Binary Data Types

PostgreSQL 中用于存储二进制数据的数据类型，主要是 bytea 类型。

• bytea: 用于存储可变长度的二进制字符串。bytea 类型的数据可以包含任何字节序列，这使得它非常适合存储例如图片、文件或加密数据等非文本数据。
  bytea 类型支持两种格式的输入和输出：“hex”格式和“escape”格式。
• Hex 格式: 以 \x 开头，后跟数据的十六进制表示。例如，二进制数据 0x123456 在 hex 格式下表示为 \x123456。
• Escape 格式: 使用八进制数表示二进制数据中的特定字节，以及使用双反斜杠 \\ 表示字节值为 92 的字节。Escape 格式允许你在字符串中包含任何可能的字节值，包括那些在文本字符串中通常无法直接包含的值。
  默认情况下，bytea 类型的输出使用 hex 格式，但可以通过更改 bytea_output 参数的设置来改变输出格式。
  使用 bytea 类型时需要特别注意的是，因为它直接存储二进制数据，所以在插入或提取数据时可能需要进行编码和解码，特别是在处理来自或发送到客户端应用程序的数据时。此外，存储大量的二进制数据可能会对数据库性能和存储空间产生影响，因此在设计数据库模式时需要考虑这一点。

8.4.1. bytea Hex Format

在 Hex 格式中，二进制数据被表示为一系列十六进制数字，每个字节由两个十六进制数字表示，并且整个字符串以 \x 开头来明确指出这是一个 Hex 格式的 bytea 字符串。
例如，如果你想存储二进制数据 0xDEADBEEF 到一个 bytea 类型的列中，你可以使用如下方式：

SELECT '\xDEADBEEF::bytea;
复制

这将会存储一个四字节的二进制数据，其十六进制表示为 DEADBEEF。这种表示法使得二进制数据的输入和输出更加直观和简洁，尤其是对于包含大量二进制数据的情况。
Hex 格式是 PostgreSQL 9.0 引入的，默认情况下 bytea 类型的输出就是使用这种格式。它的优点是易于阅读和处理，特别是在需要直接查看或者操作二进制数据的场景中。此外，Hex 格式避免了在 Escape 格式中可能遇到的转义问题，使得处理二进制数据更加直接和安全。
要注意的是，当使用 Hex 格式时，每个字节都会转换成两个十六进制字符，因此表示的字符串长度会是原始二进制数据长度的两倍加上 \x 前缀。

8.4.2. bytea Escape Format

PostgreSQL 中 bytea 类型数据的另一种表示法，即 Escape 格式。在 Escape 格式中，某些特定的字节值会被转换成特殊的转义序列。这种格式主要用于 PostgreSQL 的早期版本，并且为了向后兼容而保留。
在 Escape 格式中：

• 零字节（字节值为 0）被表示为 \000。
• 单引号（字节值为 39）可以通过两种方式转义：通过重复单引号（``）或使用八进制表示（\047）。
• 反斜杠（字节值为 92）也可以通过两种方式转义：通过重复反斜杠（\\）或使用八进制表示（\134）。
• 其他非打印字符（字节值在 0 到 31 之间以及 127 到 255 之间）必须使用其对应的三位八进制值进行转义，格式为 \xxx。
  例如，一个包含零字节和反斜杠的 bytea 值可以以如下方式表示：

SELECT E\\000\\134::bytea;
复制

这里，E 前缀表明紧随其后的字符串是一个 “escape” 字符串字面量，允许使用反斜杠转义序列。
Escape 格式相比 Hex 格式更加复杂，因为它需要处理更多的转义情况。然而，这种格式在处理一些特定的二进制数据时仍然非常有用，特别是当这些数据包含需要被转义的特殊字符时。
从 PostgreSQL 9.0 版本开始，默认的 bytea 输出格式变为了 Hex 格式，因为它更简单、更易于阅读。不过，用户仍然可以通过更改 bytea_output 参数来选择使用 Escape 格式作为输出格式。

8.5. Date/Time Types

PostgreSQL 中用于处理日期和时间的数据类型。这些类型允许你存储日期、时间、时间戳（日期和时间的组合）、间隔以及时间带时区信息。这些数据类型非常重要，因为它们让你能够对时间相关的数据进行精确的存储和复杂的操作。
以下是 PostgreSQL 支持的主要日期/时间类型：

• date: 用于存储日历日期（年、月、日），不包含时间信息。
• time [ § ] [ without time zone ]: 用于存储一天中的时间（小时、分钟、秒），不包含日期或时区信息。可选的精度 (p) 指定秒的小数位数。
• time [ § ] with time zone: 类似于 time，但包含时区信息。
• timestamp [ § ] [ without time zone ]: 用于存储日期和时间，不包含时区信息。可选的精度 (p) 指定秒的小数位数。
• timestamp [ § ] with time zone: 类似于 timestamp，但包含时区信息。通常被简称为 timestamptz。
• interval [ fields ] [ § ]: 用于存储时间间隔，即两个时间点之间的时长。fields 可以限制间隔包含的最大时间单位，而精度 (p) 指定秒的小数位数。
  这些类型中，timestamp with time zone 是特别值得注意的，因为它能够存储时区信息。然而，实际存储的时间值是转换为 UTC 后的结果，当查询时会根据当前时区设置转换回本地时间。这意味着，即使存储的时间带有时区信息，返回的结果也会受到服务器或会话时区设置的影响。
  日期/时间类型的另一个重要方面是它们支持许多专门的函数和操作符，允许你进行日期和时间的加减、提取特定的日期/时间部分（如年、月、日、小时等）以及格式化日期/时间输出等操作。
  在处理日期和时间数据时，选择合适的数据类型对于确保数据的准确性和操作的有效性至关重要。

8.5.1. Date/Time Input

PostgreSQL 中如何输入日期和时间类型的数据。PostgreSQL 接受多种日期和时间格式的输入，这提供了极高的灵活性。以下是一些主要的输入格式和规则：

1. ISO 8601 标准

这是推荐的国际标准格式，例如 YYYY-MM-DD 对于日期，YYYY-MM-DD HH:MI:SS 对于时间戳。
2. SQL 兼容格式

例如，MONTH DD, YYYY（如 January 8, 1999）。
3. 传统的 POSTGRES 格式

允许一些更自由形式的输入，如 MM/DD/YYYY，DD/MM/YYYY，以及它们的变体。
4. 日期和时间的解析

PostgreSQL 在解析日期和时间时会考虑当前的 DateStyle 设置。DateStyle 参数可以设置为 ISO, SQL, Postgres, 和 German 等，这影响了日期和时间值的解释和输出格式。
5. 带时区的时间

对于 timestamp with time zone 和 time with time zone 类型，可以在输入时指定时区信息，例如 YYYY-MM-DD HH:MI:SS+TZ。如果没有指定时区，PostgreSQL 会使用当前会话的时区设置。
6. 特殊值

PostgreSQL 支持一些特殊值的输入，如 now、today、yesterday、tomorrow、epoch、以及 infinity 和 -infinity。
7. 其他注意事项

• 在没有明确指定格式的情况下，日期和时间的解析可能依赖于区域设置和 DateStyle 参数。
• PostgreSQL 提供了强大的函数 to_date, to_timestamp, 以及 to_char 来进行日期时间的转换和格式化，这些函数可以帮助处理各种日期和时间格式的输入和输出需求。
  日期和时间的正确输入对于确保数据的准确性和后续操作的有效性至关重要。理解 PostgreSQL 支持的各种日期和时间输入格式以及如何正确使用它们，将帮助用户更有效地管理和操作日期和时间数据。

8.5.2. Date/Time Output

PostgreSQL 中日期和时间类型数据的输出格式。PostgreSQL 允许用户通过设置 DateStyle 参数来自定义日期和时间值的输出格式，以适应不同的偏好和需求。以下是 PostgreSQL 支持的主要输出样式：

1. ISO

ISO 8601 是一个国际标准日期和时间表示法，例如 YYYY-MM-DD 或 YYYY-MM-DD HH:MI:SS。这是推荐的、跨国界通用的格式。
2. SQL

这种格式遵循 SQL 标准，日期和时间的表示方式可能更符合美国的习惯，例如 MM/DD/YYYY 或 MM/DD/YYYY HH:MI:SS。
3. Postgres

这是 PostgreSQL 的传统格式，它提供了一种更自由的表示方式，例如 Mon DD YYYY HH:MI:SS（例如 Jan 8 1999 4:05:06）。
4. German

这种格式遵循德国的日期和时间表示习惯，例如 DD.MM.YYYY 或 DD.MM.YYYY HH:MI:SS。
5. 输出时区处理

对于带时区的时间类型（例如 timestamp with time zone），PostgreSQL 在输出时会根据当前会话的时区设置将时间值转换为本地时间。这意味着同一个 timestamp with time zone 值在不同的时区设置下可能会显示不同的本地时间。
6. 特殊值的输出

PostgreSQL 中的日期和时间类型支持特殊值，如 infinity 和 -infinity，这些值在输出时也会被特别表示。
7. 自定义输出格式

除了以上预设的输出样式外，PostgreSQL 还提供了 to_char 函数，允许用户根据自定义的格式模板来格式化日期和时间值的输出。这提供了极高的灵活性，使得几乎可以按任何所需的方式来显示日期和时间数据。
通过合理设置 DateStyle 参数和利用 to_char 函数，用户可以灵活控制日期和时间数据的输出，使其满足特定的格式要求和偏好。

8.5.3. Time Zones

PostgreSQL 中与时区相关的处理和配置。时区是一个重要的概念，因为它影响日期和时间数据的表示和存储。正确处理时区对于确保时间数据的准确性和一致性至关重要。

1. 时区的处理

• PostgreSQL 中的时间类型分为两类：不带时区的（time without time zone 和 timestamp without time zone）和带时区的（time with time zone 和 timestamp with time zone）。
• 对于不带时区的类型，PostgreSQL 仅存储时间信息，不考虑时区。这意味着，在不同的时区环境下，这些时间值的含义可能会发生变化。
• 对于带时区的类型，PostgreSQL 在内部将时间值转换为协调世界时（UTC），并在查询时根据当前会话的时区设置将其转换回本地时间。这确保了时间值的全球一致性。

2. 时区配置

• PostgreSQL 允许通过 TimeZone 配置参数设置会话或系统的默认时区。
• 用户可以通过 SQL 命令 SET TIMEZONE 动态改变会话的时区设置。
• PostgreSQL 支持命名时区（如 America/New_York）和时区偏移量（如 UTC-5）两种格式来指定时区。

3. 特殊时间值

• PostgreSQL 支持特殊的时间值 now 和 current_timestamp，它们在评估时会根据当前会话的时区设置生成时间戳。
• 时区的处理也适用于其他特殊时间值，如 today、yesterday 和 tomorrow。

4. 注意事项

• 使用带时区的时间类型时，重要的是要理解时区信息是如何影响时间值存储和查询的。
• 虽然 time with time zone 类型在技术上支持存储时区信息，但实践中很少使用，因为没有日期信息，时区转换可能会产生误导性的结果。
• 理解和正确设置时区对于开发跨时区应用程序和确保时间数据准确性非常重要。
  通过正确理解和使用 PostgreSQL 的时区功能，开发者可以有效地管理和操作跨地域和时区的时间敏感数据。

8.5.4. Interval Input

PostgreSQL 中如何输入间隔（interval）类型的数据。interval 类型用于表示时间段，可以用于日期和时间的加减运算中。PostgreSQL 支持多种格式来表示间隔，提供了灵活的输入方式。

1. 输入格式

interval 类型的值可以通过多种格式输入，包括 ISO 8601 标准格式、传统的 PostgreSQL 格式，以及更简洁的格式。以下是一些示例：

• ISO 8601 格式: P 开头，后跟时间量和时间单位的组合，例如 P1Y2M10D 表示 1 年 2 个月 10 天。如果包含时间部分，会在日期和时间部分之间加上 T，例如 P2DT12H 表示 2 天 12 小时。
• 传统的 PostgreSQL 格式: 可以直接使用时间单位后跟时间量的方式，例如 1 year 2 months 10 days 或 3 hours 20 mins。
• 简洁格式: 对于一些常用的间隔，可以直接使用数字后跟单位的方式，例如 10 days、12 hours。

2. 特殊值

interval 类型支持一些特殊值的输入，例如 infinity 和 -infinity，用于表示无限远的未来或过去。
3. 字段和精度

在定义 interval 类型时，可以指定包含的字段（如年、月、日等）和秒的小数精度。例如，interval day to second(3) 类型表示一个间隔，只包含天和秒，且秒的精度为小数点后三位。
4. 示例

以下是一些输入 interval 类型值的示例：

SELECT INTERVAL P1Y2M10D; -- ISO 8601 格式

SELECT INTERVAL 1 year 2 months 10 days; -- 传统格式

SELECT INTERVAL 10 days; -- 简洁格式

复制

这些输入方式提供了灵活的方法来表示时间间隔，使得在数据库操作中处理时间相关的计算变得更加直观和方便。

8.5.5. Interval Output

PostgreSQL 如何输出 interval 类型的数据。interval 类型用于表示一段时间的长度，可以包含年、月、日、小时、分钟和秒等不同的时间单位。PostgreSQL 提供了不同的输出样式，以适应不同的显示需求。

1. 输出样式

PostgreSQL 允许通过设置 IntervalStyle 参数来控制 interval 值的输出格式。这个参数支持以下几种样式：

• sql_standard: 产生符合 SQL 标准的输出，例如 1 year 2 months 或 3 days 04:05:06。
• postgres: 这是 PostgreSQL 的传统输出格式，例如 1 year 2 mons 或 3 days 04:05:06。
• postgres_verbose: 提供了更详细的输出，例如 @ 1 year 2 mons 或 @ 3 days 4 hours 5 mins 6 secs。
• iso_8601: 产生符合 ISO 8601 标准的输出，例如 P1Y2M 或 P3DT4H5M6S。

2. 特殊值的输出

类似于输入，interval 类型也支持特殊值 infinity 和 -infinity，这些特殊值在输出时也会被相应地表示。
3. 自定义输出格式

除了通过 IntervalStyle 参数设置预定义的输出格式外，to_char 函数可以用于生成自定义格式的 interval 输出。这为显示 interval 值提供了更高的灵活性。
4. 示例

假设有一个名为 duration 的 interval 类型的列，以下示例展示了如何使用不同的 IntervalStyle 设置来改变输出格式：

SET IntervalStyle = sql_standard;
SELECT duration FROM your_table;
SET IntervalStyle = postgres;
SELECT duration FROM your_table;
SET IntervalStyle = postgres_verbose;
SELECT duration FROM your_table;
SET IntervalStyle = iso_8601;
SELECT duration FROM your_table;
复制

通过正确地选择和使用 IntervalStyle 设置，开发者和数据库管理员可以根据应用程序的需求或个人偏好来控制 interval 类型数据的显示方式。

8.6. Boolean Type

PostgreSQL 中的布尔（Boolean）数据类型。布尔类型用于存储真（true）或假（false）的值，是数据库中用于表示二元逻辑的基本数据类型。

1. 布尔值的表示

在 PostgreSQL 中，布尔类型的值可以是 TRUE、FALSE 或 NULL（表示未知）。这些值可以直接在 SQL 语句中使用，例如在 INSERT 或 SELECT 语句的条件中。
2. 输入格式

PostgreSQL 接受多种方式来表示布尔值的输入：

• TRUE 可以表示为 true、yes、on、1。
• FALSE 可以表示为 false、no、off、0。
  布尔值的输入不区分大小写，且 PostgreSQL 会自动处理这些不同的表示方式。

3. 使用场景

布尔类型常用于条件表达式中，例如在 WHERE 子句中过滤结果，或者在 SELECT 列表中用作计算字段的一部分。它也经常用于存储设置或状态标志。
4. 示例

以下是一些使用布尔类型的示例 SQL 语句：

-- 创建一个包含布尔列的表

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN NOT NULL

);
-- 插入布尔值

INSERT INTO example (is_active) VALUES (TRUE);
INSERT INTO example (is_active) VALUES (f);
INSERT INTO example (is_active) VALUES (yes);
-- 查询布尔值

SELECT * FROM example WHERE is_active = TRUE;
SELECT * FROM example WHERE is_active = no;
复制

5. 特殊注意事项

• 布尔类型的字段在输出时通常会显示为 t（真）或 f（假）。
• 布尔类型的字段可以直接用于控制流语句中，如 IF 条件或循环中。
  布尔类型是数据库设计中不可或缺的一部分，能够简洁地表示逻辑状态。正确使用布尔类型可以提高数据库的可读性和维护性。

8.7. Enumerated Types

PostgreSQL 中枚举（Enumerated）类型的使用。枚举类型是一种数据类型，它包含一个静态、有序的集合，通常用于表示一个变量可以取的一组有限、固定的值。使用枚举类型可以提高数据一致性和代码可读性。

1. 声明枚举类型

在 PostgreSQL 中，你可以通过 CREATE TYPE 命令来创建一个新的枚举类型。例如，创建一个表示星期的枚举类型：

CREATE TYPE weekday AS ENUM (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday);
复制

这个命令创建了一个名为 weekday 的枚举类型，它包含星期一到星期日的值。
2. 使用枚举类型

一旦创建了枚举类型，就可以在表定义中使用它，就像使用其他数据类型一样：

CREATE TABLE schedule (
    day weekday,
    task VARCHAR(255)
);
复制

在这个例子中，schedule 表有一个名为 day 的列，其类型为之前定义的 weekday 枚举类型。
3. 插入和查询枚举值

向使用枚举类型的列插入数据时，必须使用枚举类型中定义的值之一：

INSERT INTO schedule (day, task) VALUES (Monday, Meeting with the team);
复制

查询时，也可以直接使用枚举值：

SELECT * FROM schedule WHERE day = Monday;
复制

4. 枚举类型的优点

• 数据一致性：枚举类型确保只能插入预定义的值，减少了数据错误的可能性。
• 可读性：枚举值是自描述的，使得查询和代码更易于理解。
• 效率：在内部，PostgreSQL 使用整数来表示枚举值，这比使用文本字段更加高效。

5. 注意事项

• 枚举类型的值一旦创建，就不能再更改（尽管可以添加新的值）。
• 枚举类型是与数据库紧密相关的，因此在数据库之间迁移时需要特别处理。
  枚举类型是 PostgreSQL 提供的一种强大的数据类型，适用于表示一组固定值的场景。

8.7.1. Declaration of Enumerated Types

如何在 PostgreSQL 中声明枚举（Enumerated）类型。枚举类型允许你创建一个自定义的数据类型，这个类型的值限定在一个特定的值集合中。这对于表示一组固定的选项非常有用，例如星期的天数、状态代码、操作模式等。

1. 创建枚举类型

要创建一个枚举类型，你可以使用 CREATE TYPE 命令，后跟 AS ENUM 关键字，然后在括号中列出所有可能的值。例如，如果你想创建一个表示一周中每一天的枚举类型，可以这样做：

CREATE TYPE day_of_week AS ENUM (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday);
复制

这个命令创建了一个名为 day_of_week 的枚举类型，它包含了星期一到星期日的值。
2. 使用枚举类型

一旦定义了枚举类型，就可以在表定义中使用它，就像使用其他数据类型一样。例如，创建一个包含枚举类型列的表：

CREATE TABLE activities (
    day day_of_week,
    activity VARCHAR(255)
);
复制

在这个例子中，activities 表有一个名为 day 的列，其类型为之前定义的 day_of_week 枚举类型。
3, 插入枚举值

向枚举类型的列插入数据时，必须使用枚举类型中定义的值之一：

INSERT INTO activities (day, activity) VALUES (Monday, Go to gym);
复制

4. 查询枚举值

查询使用枚举类型的列时，也可以直接使用枚举值作为条件：

SELECT * FROM activities WHERE day = Monday;
复制

5. 枚举类型的好处

• 数据完整性：使用枚举类型可以确保数据列只包含特定的预定义值，从而提高数据的准确性和一致性。
• 可读性：枚举值通常比数字代码更易于理解，提高了代码的可读性。

6. 注意事项

• 一旦创建了枚举类型，就不能修改其值列表（尽管可以向列表中添加新值）。
• 枚举类型是与特定数据库紧密相关的，因此在不同数据库间迁移数据时需要注意。
  枚举类型是 PostgreSQL 提供的一种强大的数据建模工具，适用于表示一组固定选项的场景。

8.7.2. Ordering

PostgreSQL 中枚举（Enumerated）类型的排序行为。枚举类型的一个关键特性是它们的值是有序的。这个顺序是根据枚举类型在创建时值的列出顺序确定的，而不是值的字典顺序。这意味着枚举类型的值在比较和排序操作中遵循这个自定义的顺序。

1. 枚举类型的排序规则

• 当对枚举类型的值进行排序时，它们的相对顺序是在枚举类型被定义时确定的，而不是基于值的字面量。
• 例如，如果有一个枚举类型 mood AS ENUM (sad, ok, happy)，则 sad < ok < happy，因为 sad 是在 ok 之前定义的，ok 是在 happy 之前定义的。

2. 使用枚举类型进行排序

由于枚举类型的值是有序的，你可以在 SQL 查询中使用 ORDER BY 子句对枚举类型的列进行排序。例如：

SELECT * FROM people ORDER BY mood;
复制

这个查询会根据 mood 列中枚举值的定义顺序对结果进行排序。
3. 枚举类型的比较

• 枚举类型的值也可以参与比较操作，如 =、!=、<、<=、>、>=。
• 比较操作符会根据枚举类型值的定义顺序来判断值之间的关系。

4. 示例

考虑以下枚举类型和表：

CREATE TYPE mood AS ENUM (sad, ok, happy);
CREATE TABLE person (
    name TEXT,
    current_mood mood

);
复制

插入一些数据：

INSERT INTO person (name, current_mood) VALUES (John, happy), (Jane, sad), (Doe, ok);
复制

按 current_mood 排序查询：

SELECT name, current_mood FROM person ORDER BY current_mood;
复制

这个查询将结果按 current_mood 的定义顺序排序：首先是 sad，然后是 ok，最后是 happy。
5. 注意事项

• 枚举类型的排序和比较完全基于枚举值在定义时的列出顺序，这为数据模型提供了更大的灵活性。
• 修改枚举类型以添加新值时，新值将被添加到现有值的末尾，这可能会影响排序结果。
  枚举类型的有序性质使得它们非常适合于需要明确排序的场景，如状态、级别、阶段等。

8.7.3. Type Safety

PostgreSQL 中枚举（Enumerated）类型的类型安全性。类型安全是指在编译时或运行时能够保证变量的类型用法是正确的。在枚举类型的上下文中，这意味着每个枚举类型都是唯一的，并且只能与相同的枚举类型进行比较或赋值。

1. 枚举类型的唯一性

PostgreSQL 中的每个枚举类型都被视为一个独立的类型。这意味着，即使两个枚举类型有相同的成员值，它们也被认为是不同的类型，并且不能直接相互赋值或比较。
2. 示例

假设我们定义了两个枚举类型，即使它们具有相同的值，它们也被视为不同的类型：

CREATE TYPE mood AS ENUM (happy, sad);
CREATE TYPE feeling AS ENUM (happy, sad);
复制

尝试将一个枚举类型的值赋给另一个枚举类型的列将会导致错误：

CREATE TABLE person_mood (
    name TEXT,
    current_mood mood

);
CREATE TABLE person_feeling (
    name TEXT,
    current_feeling feeling

);
-- 假设尝试将 mood 类型的值赋给 feeling 类型的列

-- 这将会失败，因为它们是不同的类型

复制

4. 类型安全的好处

• 数据一致性：类型安全确保了数据的一致性。使用枚举类型可以防止将无效或意外的值赋给变量。
• 代码清晰性：类型安全提高了代码的清晰性。每个枚举类型的用途和含义都非常明确，有助于代码的可读性和可维护性。
• 错误预防：类型安全有助于在编译时或运行时捕捉到潜在的错误，因为它强制执行严格的类型检查。

5. 类型转换

如果需要将一个枚举类型的值转换为另一个枚举类型，可以通过中间的文本表示进行转换：

-- 将 mood 枚举值转换为 feeling 枚举值

SELECT happy::mood::text::feeling;
复制

这种方法利用了文本类型作为中间桥梁，实现了枚举类型之间的转换，但使用时需要谨慎，以保持数据的正确性和一致性。
类型安全是枚举类型的一个重要特性，它在保护数据完整性、提高代码质量以及预防编程错误方面起着关键作用。

8.7.4. Implementation Details

PostgreSQL 中枚举（Enumerated）类型的实现细节。这些细节包括枚举类型的内部表示、存储需求以及如何在数据库中使用枚举类型时需要注意的一些特性。

1. 枚举类型的内部表示

• PostgreSQL 中的枚举类型是使用一个内部整数来表示的，但这个整数值对于数据库用户是不可见的。每个枚举值在创建枚举类型时赋予一个唯一的整数标识符，这些标识符用于在内部排序和比较枚举值。
• 枚举值的排序顺序是基于它们被添加到枚举类型中的顺序，而不是它们的字面值或内部整数表示。

2. 存储需求

• 枚举类型的值在数据库中的存储需求相对较小，因为它们是通过整数来引用的。这使得枚举类型在存储和查询效率上优于使用文本字段存储相同信息。

3. 添加和修改枚举值

• PostgreSQL 允许在枚举类型创建后添加新的枚举值，使用 ALTER TYPE 命令。例如：ALTER TYPE mood ADD VALUE excited;。这会将 excited 作为新的枚举值添加到 mood 类型的末尾。
• 一旦创建，枚举类型的值不能被删除或修改（除了它们的名称）。如果需要修改枚举类型，通常的做法是创建一个新的枚举类型并更新相关列，或者添加新的值。

4. 类型安全和类型转换

• 枚举类型在 PostgreSQL 中是类型安全的，这意味着一个枚举类型的值不能直接赋给另一个枚举类型的列。如果需要进行这种转换，必须通过文本类型作为中间步骤。
• 枚举类型与文本类型之间的转换是隐式支持的，这使得在查询和数据操作中使用枚举类型变得更加灵活。

5. 使用注意事项

• 枚举类型与特定数据库紧密绑定，这意味着在数据库迁移或复制时需要特别注意枚举类型的处理。
• 使用枚举类型可以提高数据一致性和表达能力，但也需要考虑到它们的不可变性和与数据库的紧密耦合。
  枚举类型提供了一种强大的方式来限制列中的值只能是预定义的一组选项，这在表示状态、类别或其他固定集合时非常有用。了解这些实现细节有助于在设计数据库和开发应用时更有效地使用枚举类型。

8.8. Geometric Types

PostgreSQL 中支持的几种几何数据类型。这些类型允许用户在数据库中存储和操作二维空间的几何形状，如点、线、多边形等。这些几何类型为开发涉及空间数据的应用程序提供了强大的工具。

1. 几何类型概览

PostgreSQL 支持以下几种主要的几何数据类型：

• 点（Point）: 表示二维平面上的一个点，使用 (x, y) 坐标表示。
• 线（Line）: 表示无限长的直线，通常以线性方程 Ax + By + C = 0 的形式表示。
• 线段（LSEG）: 表示由两个端点定义的有限长度的直线段。
• 盒子（Box）: 表示由对角线上的两个点定义的矩形区域。
• 路径（Path）: 表示一系列连接的点，可以是开放的（不形成闭合环路）或闭合的（形成环路）。
• 多边形（Polygon）: 类似于闭合路径，但专门用于表示多边形区域。
• 圆（Circle）: 由中心点和半径定义的圆形区域。

2. 使用几何类型

这些几何类型可以用于各种空间计算和查询，如计算两点之间的距离、判断点是否在多边形内、计算两个几何形状的交集等。
3. 示例

创建包含几何类型的表：

CREATE TABLE geometries (
    point_col POINT,
    line_col LINE,
    lseg_col LSEG,
    box_col BOX,
    path_col PATH,
    polygon_col POLYGON,
    circle_col CIRCLE

);
复制

插入几何数据：

INSERT INTO geometries (point_col, line_col, lseg_col, box_col, path_col, polygon_col, circle_col)
VALUES

('(0,0)', '{1, -1, 0}', '((0,0),(1,1)), '((0,0),(1,1)), [(0,0),(1,1),(1,0)], '((0,0),(1,1),(1,0)), '<(0,0),1>');
复制

执行几何运算：

SELECT point_col <-> '(2,2) AS distance_to_point FROM geometries;
复制

这个查询计算表中每个点与点 (2,2) 之间的距离。
4. 几何函数和操作符

PostgreSQL 提供了一系列的几何函数和操作符来支持对几何类型的数据进行操作和查询，如计算两点之间的距离、判断几何形状是否相交等。
几何类型为处理空间数据提供了强大的功能，适用于需要空间关系和几何计算的应用场景，如地图服务、空间分析等。

8.8.1. Points

PostgreSQL 中点（Point）类型的使用。点类型是几何类型中的基础，用于在二维平面上表示一个位置，由一对 X 和 Y 坐标组成。

1. 点的表示

在 PostgreSQL 中，点可以通过 (x, y) 的形式来表示，其中 x 和 y 是浮点数，分别代表点在二维平面上的横坐标和纵坐标。
2. 示例

创建一个包含点类型列的表：

CREATE TABLE example (
    location POINT

);
复制

向表中插入点数据：

INSERT INTO example (location) VALUES ((1.0, 2.0)');
复制

查询表中的点数据：

SELECT location FROM example;
复制

3. 点的操作

PostgreSQL 提供了多种操作符和函数来对点进行操作，例如：

• 距离运算符 <->：计算两个点之间的距离。

  
  SELECT '(1,2)::POINT <-> '(4,6)::POINT AS distance;
  复制

• 点的相等运算符 =：判断两个点是否相同（即 X 坐标和 Y 坐标都相同）。

  
  SELECT '(1,2)::POINT = '(1,2)::POINT AS are_equal;
  复制

应用场景

点类型在处理需要空间位置信息的应用中非常有用，比如地理信息系统（GIS）、地图应用、空间分析等领域。通过使用点类型，可以简洁地表示和操作空间位置信息。

注意事项

• 在进行空间计算时，应注意坐标系的选择和应用场景的特定需求，以确保计算结果的准确性。
• 点类型的操作和函数可以与其他几何类型结合使用，以实现更复杂的空间分析和处理。
  点类型是 PostgreSQL 中几何类型体系的基础，提供了对二维空间位置的基本表示和操作能力。

8.8.2. Lines

PostgreSQL 中线（Line）类型的使用。在 PostgreSQL 中，线（Line）类型用于表示二维平面上的无限长直线。每条直线在数学上可以通过线性方程 Ax + By + C = 0 来定义，其中 A、B 和 C 是线的系数，而 x 和 y 表示平面上的坐标。

1. 线的表示

直线在 PostgreSQL 中可以通过 {A,B,C} 的形式来表示，对应于线性方程 Ax + By + C = 0 中的系数。需要注意的是，A 和 B 不能同时为 0，因为这将无法定义一个有效的直线。
2. 示例

创建一个包含线类型列的表：

CREATE TABLE example (
    line_col LINE

);
复制

向表中插入线数据：

INSERT INTO example (line_col) VALUES ({1, -1, 0}');
复制

这个示例中的线对应于方程 x - y + 0 = 0，或简化为 x = y，表示一个通过原点，倾斜角为 45 度的直线。
3. 线的操作

虽然 PostgreSQL 支持线类型，但在实际应用中，线段（LSEG）和路径（PATH）类型往往更为常用，因为它们可以表示有限的直线部分。无限长的直线在某些特定的数学和物理模型中有应用，但在处理现实世界的地理或空间数据时，有界的几何类型更加实用。
4. 注意事项

• 直线类型在 PostgreSQL 中不如点（POINT）、线段（LSEG）和多边形（POLYGON）等其他几何类型常用。
• 直线类型的使用场景相对有限，主要用于特定的数学和物理计算中。
• 直线与直线之间的交点计算是直线类型的一个典型应用。
  直线类型提供了一种在二维空间中表示无限长直线的方法，尽管它在数据库应用中的使用不如其他有界几何类型频繁，但它在需要精确表示直线方程时仍然是一个有用的工具。

8.8.3. Line Segments

PostgreSQL 中线段（LSEG）类型的使用。线段类型用于表示二维平面上有两个端点的直线片段。与无限长的直线（LINE）类型不同，线段（LSEG）类型表示的是一条有限长度的直线。

1. 线段的表示

在 PostgreSQL 中，线段通过一对坐标点来定义，这对坐标点表示线段的两个端点。线段可以通过以下任一形式来表示：

• 使用方括号和圆括号：[(x1, y1), (x2, y2)]
• 使用圆括号：((x1, y1), (x2, y2))
• 直接使用坐标点，不加任何括号：(x1, y1), (x2, y2) 或 x1, y1, x2, y2
  其中，(x1, y1) 和 (x2, y2) 分别是线段的起点和终点的坐标。

2. 示例

创建一个包含线段类型列的表：

CREATE TABLE example (
    lseg_col LSEG

);
复制

向表中插入线段数据：

INSERT INTO example (lseg_col) VALUES ([(0,0), (1,1)]);
复制

这个示例中的线段从点 (0,0) 到点 (1,1)，表示一条从原点出发，倾斜 45 度，到达 (1,1) 的有限直线段。
3. 线段的操作

PostgreSQL 提供了多种操作符和函数来对线段进行操作，例如：

• 距离操作符 <->：计算两个几何对象之间的最短距离。当用于两个线段时，它返回这两个线段之间的最短距离。

  
  SELECT [(0,0),(1,1)]::LSEG <-> [(2,2),(3,3)]::LSEG AS distance;
  复制

• 相交操作符 ?#：判断两个线段是否相交。

  
  SELECT [(0,0),(1,1)]::LSEG ?# [(1,1),(2,0)]::LSEG AS intersect;
  复制

4. 应用场景

线段类型在处理需要具体起点和终点的直线情况时非常有用，例如在地图应用、空间分析、计算机图形学和任何需要具体直线片段的场景中。
线段类型提供了一种在二维空间中表示和操作有限长度直线的能力，使得处理涉及直线片段的空间数据变得更加灵活和强大。

8.9. Network Address Types

PostgreSQL 中用于存储网络地址的数据类型。这些类型包括用于存储 IPv4 和 IPv6 地址以及子网掩码的 cidr 和 inet 类型，以及用于存储 MAC 地址的 macaddr 和 macaddr8 类型。这些数据类型提供了专门的功能来处理网络地址，比如地址的格式验证、地址范围的查询和比较等。

1. cidr 类型

• cidr 类型用于存储一个 IP 地址和它的网络掩码（子网掩码），主要用于表示网络地址。
• cidr 类型严格要求网络地址部分与掩码相匹配，即地址中的主机部分必须为零。

2. inet 类型

• inet 类型与 cidr 类型相似，也用于存储 IP 地址和网络掩码，但它允许地址中包含主机部分，因此可以用来存储单个 IP 地址。
• inet 类型可以存储 IPv4 或 IPv6 地址。

3. macaddr 和 macaddr8 类型

• macaddr 类型用于存储 6 字节的 MAC 地址，通常用于识别网络设备的物理硬件地址。
• macaddr8 类型是对 macaddr 类型的扩展，支持存储 8 字节的 MAC 地址，包括 EUI-64 格式的地址。

4. 使用示例

创建一个包含网络地址类型列的表：

CREATE TABLE network_hosts (
    host_name VARCHAR(100),
    ip_address inet,
    subnet cidr,
    mac_address macaddr

);
复制

向表中插入数据：

INSERT INTO network_hosts (host_name, ip_address, subnet, mac_address)
VALUES (server1, 192.168.1.10/24, 192.168.1.0/24, 08:00:27:53:8b:dc);
复制

查询数据：

SELECT * FROM network_hosts WHERE ip_address << subnet;
复制

这个查询查找位于指定子网中的主机。
5. 特点和用途

• 使用这些网络地址类型而不是简单的文本类型存储网络地址，可以利用 PostgreSQL 提供的专门功能，如自动验证地址格式、执行网络相关的操作和比较等。
• 这些类型非常适合于需要处理大量网络配置信息的应用，例如网络管理系统、访问控制列表和 IP 地址管理（IPAM）系统。
  网络地址类型是 PostgreSQL 提供的强大工具，使得数据库能够更有效地存储、查询和操作网络地址数据。

8.10. Bit String Types

PostgreSQL 中用于存储位串（bit strings）的数据类型。位串是由二进制数字（0 和 1）组成的序列，适用于需要精确控制每个位（bit）的场景，如权限标志、状态标志等。
PostgreSQL 提供了两种位串类型：

1. bit(n) 类型

• bit(n) 类型用于存储固定长度的位串。在这种类型中，n 指定了位串的长度。如果插入的位串长度小于 n，则在左侧用 0 填充至指定长度；如果超过 n，则会引发错误。
• 示例：bit(5) 类型的列可以存储长度恰好为 5 的位串，如 10110。

2. bit varying(n) 类型

• bit varying(n) 或 varbit(n) 类型用于存储可变长度的位串，最大长度为 n。这种类型允许存储的位串长度小于或等于 n，不需要用 0 填充至最大长度。
• 示例：bit varying(5) 类型的列可以存储长度为 1 到 5 之间的位串，如 10 或 11001。

3. 使用示例

创建包含位串类型列的表：

CREATE TABLE bit_example (
    fixed_bit bit(5),
    variable_bit bit varying(5)
);
复制

向表中插入数据：

INSERT INTO bit_example (fixed_bit, variable_bit) VALUES (101, 110);
复制

在这个示例中，fixed_bit 列将存储 00101（左侧用 0 填充至长度 5），而 variable_bit 列将存储 110。
4. 操作和函数

PostgreSQL 提供了一系列操作符和函数来操作位串，包括位串之间的 AND（&）、OR（|）、NOT（~）运算，以及位串的截取和长度测量等。
5. 应用场景

位串类型适用于需要精确控制每个位的应用场景，例如：

• 实现简单的权限系统，每个位代表一个特定的权限。
• 存储和处理二进制协议的标志位。
  使用位串类型可以提高数据存储的效率，尤其是在处理大量二进制数据时，同时也使得对这些数据的操作更为直观。

8.11. Text Search Types

PostgreSQL 中用于全文搜索的数据类型，这些类型是为了支持对文本数据的高效搜索而设计的。全文搜索允许你查询包含某些词语的文档，而不仅仅是检查数据是否匹配一个简单的模式。这对于处理大量文本数据，如文章、评论、博客帖子等非常有用。
PostgreSQL 提供了两种特殊的数据类型用于全文搜索：

1. tsvector

• tsvector 类型用于存储文本数据的词汇化版本，即将文本分解为词汇（词条或词元）的集合，并去除所有的格式和停用词（如“the”、“a”等常见但对搜索不重要的词）。tsvector 也为每个词汇存储位置信息，这对于排名结果很有用。
• 示例：to_tsvector(english, The quick brown fox jumped over the lazy dog.') 会产生一个 tsvector，包含文本中的关键词及其位置。

2. tsquery

• tsquery 类型用于存储查询文本，这可以是单个词汇或使用布尔运算符（AND、OR、NOT）和短语搜索构造的复杂查询。tsquery 可以用来与 tsvector 类型的数据进行匹配，以找出满足查询条件的文本数据。
• 示例：fox & dog::tsquery 是一个简单的查询，查找同时包含 “fox” 和 “dog” 的文档。

3. 全文搜索的操作和函数

PostgreSQL 提供了一系列的操作和函数来支持全文搜索，包括：

• 使用 @@ 操作符将 tsvector 与 tsquery 进行匹配，判断文本是否满足查询条件。
• 使用 to_tsvector 函数将文本转换为 tsvector。
• 使用 to_tsquery 函数将查询字符串转换为 tsquery。
• 使用 rank 函数对搜索结果进行排名。

4. 应用场景

全文搜索类型非常适合构建搜索引擎、支持网站内搜索、分析用户生成内容等场景，它能够提供比传统的 LIKE 或正则表达式搜索更加强大和灵活的搜索能力。
5. 示例

假设有一个包含文章内容的表 articles，你可以使用全文搜索来找出包含特定关键词的文章：

SELECT title

FROM articles

WHERE to_tsvector(english, content) @@ to_tsquery(english, PostgreSQL & search);
复制

这个查询会返回所有在其内容中同时包含 “PostgreSQL” 和 “search” 的文章标题。
全文搜索类型是 PostgreSQL 提供的强大文本处理工具，使得在大量文本数据中进行高效、灵活的搜索成为可能。

8.12. UUID Type

PostgreSQL 中的 UUID（Universally Unique Identifier，通用唯一识别码）类型。UUID 是一种用于生成唯一标识符的标准，广泛用于数据库和分布式系统中，以确保在全球范围内的唯一性。UUID 的一个常见用途是作为数据库记录的主键，特别是在分布式数据库系统中，它可以避免主键冲突的问题。

1. UUID 的特点

• UUID 是一个 128 位的长数字，通常以 32 个十六进制数字表示，格式为 8-4-4-4-12（例如 550e8400-e29b-41d4-a716-446655440000）。
• UUID 的生成不依赖于中心机构或数据库，因此可以在应用程序中独立生成，而不用担心重复。
• 使用 UUID 作为主键可以提高数据库的可扩展性和灵活性。

2. UUID 类型的使用

在 PostgreSQL 中，可以使用 uuid 类型来存储 UUID 值：

CREATE TABLE example_table (
    id UUID PRIMARY KEY,
    data TEXT

);
复制

插入包含 UUID 的记录：

INSERT INTO example_table (id, data) VALUES (550e8400-e29b-41d4-a716-446655440000, Example data);
复制

3. 生成 UUID

PostgreSQL 提供了函数来生成 UUID，但这些函数通常依赖于扩展，如 uuid-ossp 和 pgcrypto。例如，使用 uuid-ossp 扩展生成 UUID：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
复制

这将生成一个版本 4 的随机 UUID。
4. UUID 的优点

• 唯一性：UUID 提供了极高的唯一性保证，几乎可以忽略生成重复的可能性。

• 无需中心生成：UUID 可以在应用层生成，不需要数据库交互，有助于提高性能和降低系统复杂性。

• 适合分布式系统：在分布式系统中，UUID 可以避免主键冲突，简化数据的合并和同步。
  5.注意事项

• 使用 UUID 作为主键会增加存储空间的需求，因为它比传统的整数主键占用更多的字节。

• UUID 的索引和查询性能可能略低于整数类型的主键。
  UUID 类型是 PostgreSQL 中处理全局唯一标识符的强大工具，特别适用于需要高度唯一性保证的应用场景。

8.13. XML Type

PostgreSQL 中的 XML 数据类型。这个数据类型用于存储和查询符合 XML 标准的数据。它允许用户以结构化的方式存储文档和片段，同时提供了一系列的 XML 处理函数和操作符，使得在数据库层面上能够有效地处理 XML 数据。

1. XML 数据类型的特点

• 存储和查询 XML 文档：可以存储完整的 XML 文档或者 XML 内容片段。
• 数据完整性：在存储时，XML 数据会被检查是否为良构的 XML，确保数据的正确性。
• 支持 XPath 和 XQuery：PostgreSQL 提供了对 XPath 表达式的支持，允许用户执行复杂的查询和数据提取操作。
• 转换功能：提供了将文本数据转换为 XML 类型的函数，以及将 XML 数据转换回文本的功能。

2. 使用 XML 类型

创建包含 XML 类型列的表：

CREATE TABLE example (
    data XML

);
复制

插入 XML 数据：

INSERT INTO example (data) VALUES (<note><to>User</to><message>Hello, World!</message></note>');
复制

查询 XML 数据：

SELECT data FROM example WHERE xpath('/note/to/text()', data) = ARRAY[User];
复制

3. XML 数据的生成和序列化

• 生成 XML 数据：可以使用 xmlparse 函数从文本字符串生成 XML 类型的数据，确保数据在插入时是良构的。
• 序列化 XML 数据：xmlserialize 函数允许将 XML 类型的数据转换回文本字符串，方便输出和显示。

4. 注意事项

• 使用 XML 数据类型时，应确保数据的良构性和有效性，以避免存储或查询时出现错误。
• 在处理大型 XML 文档时，性能可能成为考虑因素，因为 XML 数据的解析和查询可能比简单文本操作更耗时。
  XML 数据类型为在 PostgreSQL 中处理复杂的 XML 数据提供了强大的工具，使得数据库不仅能够存储结构化的 XML 数据，还能进行高效的查询和处理。

8.14. JSON Types

PostgreSQL 中用于存储 JSON（JavaScript Object Notation）数据的数据类型。具体来说，介绍了两种 JSON 数据类型：json 和 jsonb。这两种类型都可以用来存储 JSON 数据，但在处理方式和性能上有所不同。
json 类型存储的是输入文本的精确副本，每次处理时都需要重新解析；而 jsonb 类型则以一种分解的二进制格式存储，这使得输入时稍慢（因为有额外的转换开销），但处理起来快得多，因为不需要重新解析。jsonb 类型还支持索引，这是 json 类型不具备的。
jsonb 类型不保留空格，不保留对象键的顺序，并且不保留重复的对象键。如果在输入时指定了重复的键，只会保留最后一个键值对。相比之下，json 类型会保留输入文本的所有细节，包括空格和键的顺序。
此外，JSONB 类型支持的一些操作，如数据的插入、查询、修改和删除，以及如何使用索引来提高查询性能。也简要介绍了用于处理 JSON 数据的一些 PostgreSQL 函数和操作符，如用于提取 JSON 对象中元素的操作符和用于构建 JSON 对象的函数。

8.15. Arrays

PostgreSQL 中数组数据类型的使用。在 PostgreSQL 中，数组可以存储一种类型的多个值，并且这些值可以是任何 PostgreSQL 支持的数据类型，包括用户自定义类型、枚举类型、复合类型和域类型等。数组是多维的，意味着你可以有多维数组，例如二维数组或三维数组。

8.15.1 声明数组类型

你可以在创建表时声明数组类型的列，方法是在数据类型后面加上方括号 []。例如，可以创建一个包含整数数组的列，或者包含文本数组的列。如果你想指定数组的维度或长度，也可以在方括号中指定，但 PostgreSQL 并不强制这些限制。

8.15.2 数组值的输入

数组值可以通过使用花括号 {} 并用逗号 , 分隔每个元素来输入。对于文本和字符串类型的数组，如果数组元素包含花括号、逗号或其他特殊字符，你需要使用双引号 " 将这些元素括起来。如果数组元素本身包含双引号或反斜杠，你需要适当地转义它们。

8.15.3 访问数组

你可以通过下标访问数组中的元素，下标从 1 开始。你也可以访问数组的子数组或特定范围的元素。PostgreSQL 提供了一系列函数和操作符来处理数组，包括获取数组长度、添加或删除元素、比较数组等。

8.15.4 修改数组

数组的特定元素可以通过下标直接修改。此外，你可以使用数组的连接操作符 || 来添加元素到数组的开头或结尾，或者连接两个数组。

8.15.5 在数组中搜索

PostgreSQL 提供了操作符和函数来搜索数组中的元素，包括检查数组是否包含特定的元素、获取满足条件的元素下标等。

8.15.6 数组的输入和输出语法

数组的输入和输出遵循特定的语法规则。输出时，数组元素将根据其数据类型进行格式化，并用花括号和逗号分隔。输入数组时，你需要遵循相应的语法来确保数组被正确解析。
总的来说，数组是 PostgreSQL 中强大而灵活的数据类型，允许你在单个列中存储和操作多个值。通过使用数组，你可以简化数据模型并避免某些类型的表连接，从而可能提高查询性能。

8.16. Composite Types

PostgreSQL 中复合类型的使用。复合类型，也称为行类型或记录类型，是一种可以封装多个字段（每个字段都有自己的数据类型）的数据结构。复合类型可以用于表示表中的一行或者函数返回多个值的情况。

8.16.1 声明复合类型

你可以使用 CREATE TYPE 命令来声明一个新的复合类型。声明复合类型时，你需要指定每个字段的名称和数据类型。例如：

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

);
复制

这个例子创建了一个名为 complex 的复合类型，它有两个字段：r 和 i，它们的数据类型都是 double precision。

8.16.2 构造复合值

构造复合类型的值时，可以使用行构造器 ROW 或者直接使用圆括号 ()，并在里面填充对应字段的值，字段值之间用逗号分隔。例如：

SELECT ROW(1.0, 2.0) :: complex;
复制

或者：

SELECT (1.0, 2.0) :: complex;
复制

这两个例子都构造了一个 complex 类型的值。

8.16.3 访问复合类型

你可以使用点符号 . 来访问复合类型中的字段，就像访问表中的列一样。例如，如果你有一个复合类型的列 comp_col，你可以这样访问它的 r 字段：

SELECT comp_col.r FROM your_table;
复制

8.16.4 修改复合类型

如果需要修改复合类型的某个字段，可以使用点符号 . 来指定需要修改的字段。例如：

UPDATE your_table SET comp_col.r = new_value WHERE ...;
复制

8.16.5 在查询中使用复合类型

复合类型可以在查询中广泛使用，包括作为函数的返回类型，或者在 SELECT、INSERT 和 UPDATE 语句中使用。复合类型提供了一种灵活的方式来处理多个相关数据作为一个整体。

8.16.6 复合类型的输入和输出语法

复合类型的值在输入和输出时使用圆括号 () 包围字段值，字段值之间用逗号 , 分隔。如果字段值中包含特殊字符（如逗号、圆括号等），则需要用双引号 " 对字段值进行引用，并适当使用转义。
复合类型是 PostgreSQL 中一种强大的数据类型，它允许用户定义包含多个字段的自定义数据类型，从而在数据库设计和函数编程中提供了更高的灵活性和表达能力。

8.17. Range Types

PostgreSQL 中的范围类型功能。范围类型允许你在单个数据类型中存储和操作一个值的范围，例如日期或数字的范围。这对于需要处理连续值范围的应用程序特别有用，如预订系统、时间表安排或任何需要界定开始和结束值的场景。

8.17.1 内置的范围类型

PostgreSQL 提供了几种内置的范围类型，包括：

• int4range：整数范围类型。
• int8range：大整数范围类型。
• numrange：数值范围类型，可以包含小数。
• tsrange：不带时区的时间戳范围类型。
• tstzrange：带时区的时间戳范围类型。
• daterange：日期范围类型。
  对应的，每个范围类型还有相应的多范围类型，如 int4multirange 等，用于表示非连续的多个范围。

8.17.2 范围类型的例子

范围类型可以用于表示例如会议室预订的时间段：

CREATE TABLE reservation (
    room int,
    during tsrange

);
复制

你可以插入一个时间段，表示会议室被预订的时间：

INSERT INTO reservation VALUES (1108, '[2020-01-01 14:30, 2020-01-01 15:30)');
复制

8.17.3 包含和排斥边界

范围类型可以指定边界是包含（inclusive）还是排斥（exclusive）。这是通过在范围的表示中使用不同的括号来实现的：[ 和 ] 表示包含边界，( 和 ) 表示排斥边界。

8.17.4 无限（无界）范围

范围的边界可以省略，表示该边界无限。例如，(,3] 表示小于或等于 3 的所有值的范围。

8.17.5 范围的输入/输出

范围的文本表示遵循上述规则，使用 [、]、( 和 ) 来表示边界的包含或排斥，以及使用逗号分隔范围的起始和结束值。

8.17.6 构建范围和多范围

PostgreSQL 提供了构造函数来创建范围和多范围类型的值。例如，使用 numrange 函数创建一个数值范围：

SELECT numrange(1.0, 10.0);
复制

8.17.7 离散范围类型

对于离散的值类型（如整数或日期），PostgreSQL 允许将这些值类型的范围视为离散范围，这意味着可以对范围进行某些特定的操作，如调整边界的包含或排斥。

8.17.8 定义新的范围类型

用户可以定义自己的范围类型，以适应特定的应用需求，例如创建一个浮点数范围类型。

8.17.9 索引

范围类型支持 GiST 和 SP-GiST 索引，这可以加速范围查询，如检查两个范围是否重叠。

8.17.10 范围类型约束

你可以使用排除约束来确保表中的范围值不重叠，这在管理具有时间或空间约束的资源时特别有用。

8.18. Domain Types

PostgreSQL 中域类型的概念和用法。域类型（Domain）是基于现有数据类型，但附加了一些约束条件的一种自定义数据类型。这些约束条件可以限制域类型的值必须满足特定的规则，从而使得数据更加符合业务逻辑的要求。

8.18.1 定义域类型

使用 CREATE DOMAIN 语句可以定义一个新的域类型。在定义域类型时，你需要指定基础数据类型以及一系列的约束条件（CHECK 约束）。例如，如果你想创建一个只接受正整数的域类型，可以这样做：

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

这里，posint 是新创建的域类型名称，基于 integer 类型，并添加了一个约束条件，确保所有的 posint 类型的值都必须大于 0。

8.18.2 使用域类型

一旦定义了域类型，就可以像使用普通数据类型一样在表定义或函数参数中使用它。例如，使用上面定义的 posint 类型创建一个表：

CREATE TABLE mytable (
    id posint

);
复制

在插入或更新数据时，如果数据违反了域类型的约束条件，PostgreSQL 会抛出错误。

8.18.3 域类型的优势

域类型提供了一种在数据库级别强制执行数据完整性的方法。通过在数据类型上定义额外的约束，可以保证数据的正确性和一致性，减少应用程序代码中进行数据验证的需要。域类型使得数据模型更加严格和清晰，有助于维护数据质量。

8.18.4 注意事项

• 域类型的约束是在数据插入或更新到数据库时检查的。
• 域类型的约束可以包括多个 CHECK 约束，为数据提供多层次的验证。
• 域类型可以基于 PostgreSQL 支持的任何内置或自定义数据类型。
• 如果需要修改域类型的定义（例如添加或修改约束），可以使用 ALTER DOMAIN 语句。
  域类型是 PostgreSQL 提供的一种强大工具，用于实现数据完整性和自定义数据验证逻辑。通过使用域类型，开发者可以在数据库层面确保数据的有效性，从而提高应用程序的健壮性和可靠性。

8.19. Object Identifier Types

PostgreSQL 中用于标识数据库内部对象（如表、函数、操作符等）的特殊数据类型。这些类型主要用于系统目录表中，并且在某些情况下也可用于普通 SQL 查询中，以便于引用数据库对象而不需要使用字符串名称。以下是一些主要的对象标识符类型：

oid

• 类型: oid
• 描述: 表示对象标识符，是 PostgreSQL 系统内部用于唯一标识对象的数字。
• 用途: 用于许多系统表中作为主键。

regclass

• 类型: regclass
• 描述: 用于引用表或视图。
• 用途: 可以通过表名获取其 oid，或者反过来，通过 oid 获取表名。

regproc

• 类型: regproc
• 描述: 用于引用函数。
• 用途: 可以通过函数名获取其 oid，或者反过来，通过 oid 获取函数名。

regoper

• 类型: regoper
• 描述: 用于引用操作符。
• 用途: 通过操作符名获取其 oid。

regtype

• 类型: regtype
• 描述: 用于引用数据类型。
• 用途: 可以通过类型名获取其 oid，或者反过来，通过 oid 获取类型名。

regnamespace

• 类型: regnamespace
• 描述: 用于引用命名空间（模式）。
• 用途: 通过命名空间名获取其 oid。

regrole

• 类型: regrole
• 描述: 用于引用数据库角色。
• 用途: 通过角色名获取其 oid。

regconfig

• 类型: regconfig
• 描述: 用于引用全文搜索配置。
• 用途: 通过配置名获取其 oid。
  这些类型在实际应用中可以简化数据库对象引用的处理。例如，使用 regclass 类型可以方便地在触发器或函数中引用表，而不必担心表名变更带来的影响。同时，这些类型还能帮助建立起对象之间的依赖关系，使得数据库更加稳定。
  这些对象标识符类型还有一些其他的用途和特性，例如它们可以用于查询系统目录表来获取数据库元数据信息，或者用于某些数据库管理任务中。使用这些类型时，需要注意它们通常不适用于普通的业务数据存储，而是更多地服务于数据库内部管理和元数据操作。

8.20. pg_lsn Type

PostgreSQL 中用于表示 WAL（Write-Ahead Logging）位置的特殊数据类型 pg_lsn。WAL 是 PostgreSQL 用于数据恢复和复制的一种日志记录机制，它记录了数据库变更的所有操作。pg_lsn 类型用于标识 WAL 中的特定位置，通常用于复制和数据恢复相关的操作中。

特性

• 内部表示: pg_lsn 类型在内部表示为一个 64 位整数，代表在 WAL 中的字节位置。
• 文本表示: 文本形式的 pg_lsn 值由两个十六进制数字组成，这两部分通过斜杠分隔，例如 16/B374D848。这种表示法方便人类阅读和理解 WAL 中的位置。
• 操作和函数: pg_lsn 类型支持比较操作（如等于、不等于、大于、小于等），还支持加减操作，允许计算两个 pg_lsn 值之间的差距，或者将一个数字加到 pg_lsn 上以得到新的位置。

应用场景

• 数据复制: 在 PostgreSQL 的流复制中，pg_lsn 类型用于标识主服务器上的 WAL 位置，从而确定从服务器需要从哪里开始复制数据。
• 点时间恢复（PITR）: pg_lsn 类型用于确定恢复到特定时间点所需的 WAL 位置。
• 监控和诊断: 管理员可以使用 pg_lsn 类型相关的函数来监控数据库的复制延迟或者 WAL 的生成速度。

注意事项

• 数据类型转换: 虽然 pg_lsn 在内部是一个 64 位整数，但直接将其转换为整数类型进行操作可能会导致意义不明确或错误，因此推荐使用专门为 pg_lsn 设计的函数和操作符。
• 使用场景: pg_lsn 类型主要用于高级数据库管理任务，如复制配置、备份和恢复。普通应用开发中很少直接使用。
  pg_lsn 类型为数据库管理员提供了一个强大的工具，用于精确控制和监控 PostgreSQL 数据库的 WAL 处理过程。通过合理利用 pg_lsn 类型，可以实现高效的数据备份、恢复和复制管理。

8.21. Pseudo-Types

PostgreSQL 中的伪类型（Pseudo-Types）。伪类型是一组特殊的数据类型，它们不能直接用作表列的数据类型，但可以用在函数声明中，用于指定函数的参数类型或返回类型。伪类型主要用于特殊的编程情况，其中函数的行为并不符合普通的单一数据类型返回或接收模式。
以下是一些常见的伪类型及其用途：

any

表示函数可以接受任何数据类型的参数。

anyelement

用于泛型函数，表示函数可以接受任何数据类型的参数，并且所有 anyelement 类型的参数和返回值必须是相同的数据类型。

anyarray

表示函数接受任何数组数据类型的参数。

anyenum

表示函数接受任何枚举类型的参数。

anyrange

表示函数接受任何范围类型的参数。

void

表示函数不返回任何值。

record

表示函数可以返回一个行类型，这个行类型在函数被调用时是动态确定的。

cstring

用于 C 函数接口，表示函数接受或返回一个以 null 结尾的 C 字符串。

internal

表示函数参数或返回类型是 PostgreSQL 内部使用的数据类型。

trigger

用于触发器函数，表示该函数是一个触发器。
这些伪类型提供了额外的灵活性，允许开发者定义更通用或特殊用途的函数。例如，使用 anyelement 或 anyarray 可以创建一个泛型函数，该函数可以接受任何类型的输入并执行操作，而不需要为每种数据类型编写专门的函数版本。
伪类型在编写涉及多种数据类型的高级数据库操作和自定义函数时非常有用，但它们的使用需要对 PostgreSQL 的类型系统有深入的理解。