PostgreSQL - 数据类型

---

---

在本章中，我们将讨论 PostgreSQL 中使用的数据类型。在创建表时，对于每一列，您需要指定一个数据类型，即您希望在表字段中存储哪种数据。

这带来了以下好处：

• 一致性 - 对相同数据类型的列执行操作会产生一致的结果，并且通常速度最快。

• 验证 - 正确使用数据类型意味着对数据进行格式验证，并拒绝超出数据类型范围的数据。

• 紧凑性 - 由于一列可以存储单一类型的值，因此以紧凑的方式存储。

• 性能 - 正确使用数据类型可以实现最高效的数据存储。存储的值可以快速处理，从而提高性能。

PostgreSQL 支持广泛的数据类型集。此外，用户可以使用 CREATE TYPE SQL 命令创建自己的自定义数据类型。PostgreSQL 中的数据类型分为不同的类别。下面将讨论这些类别。

数值类型

数值类型包括两字节、四字节和八字节整数、四字节和八字节浮点数以及可选精度的十进制数。下表列出了可用的类型。

货币类型

money 类型以固定的分数精度存储货币金额。numeric、int 和 bigint 数据类型的值可以转换为 money。由于可能存在舍入误差，因此不建议使用浮点数处理货币。

字符类型

下表列出了 PostgreSQL 中可用的通用字符类型。

二进制数据类型

bytea 数据类型允许存储二进制字符串，如下表所示。

日期/时间类型

PostgreSQL 支持完整的 SQL 日期和时间类型集，如下表所示。日期根据公历计算。这里，所有类型的分辨率为1 微秒 / 14 位数，除了date类型，其分辨率为天。

布尔类型

PostgreSQL 提供标准的 SQL 类型布尔值。布尔数据类型可以具有true、false两种状态，以及第三种状态unknown，它由 SQL NULL 值表示。

枚举类型

枚举 (enum) 类型是包含一组静态有序值的数据类型。它们等效于许多编程语言中支持的 enum 类型。

与其他类型不同，枚举类型需要使用 CREATE TYPE 命令创建。此类型用于存储一组静态有序的值。例如指南针方向，即 NORTH、SOUTH、EAST 和 WEST，或一周中的日期，如下所示：

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

创建后，枚举类型可以像任何其他类型一样使用。

几何类型

几何数据类型表示二维空间对象。最基本类型点是所有其他类型的基础。

网络地址类型

PostgreSQL 提供数据类型来存储 IPv4、IPv6 和 MAC 地址。最好使用这些类型而不是纯文本类型来存储网络地址，因为这些类型提供了输入错误检查以及专门的运算符和函数。

位字符串类型

位字符串类型用于存储位掩码。它们要么是 0 要么是 1。有两种 SQL 位类型：bit(n) 和 bit varying(n)，其中 n 是一个正整数。

文本搜索类型

此类型支持全文搜索，即搜索自然语言文档集合以找到最匹配查询的文档的活动。为此，有两种数据类型：

UUID 类型

UUID（通用唯一标识符）写成一系列小写十六进制数字，分成几组并用连字符分隔，具体来说是一组八位数字，后跟三组四位数字，然后是一组 12 位数字，总共 32 位数字，表示 128 位。

UUID 的示例为：550e8400-e29b-41d4-a716-446655440000

XML 类型

XML 数据类型可用于存储 XML 数据。要存储 XML 数据，首先必须使用以下函数 xmlparse 创建 XML 值：

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

JSON 类型

json 数据类型可用于存储 JSON（JavaScript 对象表示法）数据。此类数据也可以存储为text，但json数据类型的优势在于它可以检查每个存储的值是否为有效的 JSON 值。还提供了相关的支持函数，可以直接用于处理 JSON 数据类型，如下所示。

数组类型

PostgreSQL 提供了将表的一列定义为可变长度多维数组的机会。可以创建任何内置或用户定义的基本类型、枚举类型或复合类型的数组。

数组声明

数组类型可以声明为

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

或使用关键字“ARRAY”声明为

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

插入值

数组值可以作为文字常量插入，将元素值括在花括号中并用逗号分隔。示例如下所示：

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’); 

访问数组

访问数组的示例如下所示。以下命令将选择储蓄在第二季度比第四季度多的用户。

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

修改数组

修改数组的一个示例如下所示。

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

或者使用 ARRAY 表达式语法 -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

数组搜索

数组搜索的一个示例如下所示。

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

如果已知数组的大小，则可以使用上面给出的搜索方法。否则，以下示例显示了在大小未知时如何搜索。

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

复合类型

此类型表示字段名称及其数据类型的列表，即表中一行或记录的结构。

复合类型的声明

以下示例显示如何声明复合类型

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

此数据类型可用于如下创建表中 -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

复合值输入

复合值可以作为文字常量插入，将字段值括在括号中并用逗号分隔。示例如下所示 -

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

这对上面定义的 inventory_item 有效。只要表达式中有多个字段，ROW 关键字实际上就是可选的。

访问复合类型

要访问复合列的字段，请使用点后跟字段名称，就像从表名中选择字段一样。例如，要从我们的 on_hand 示例表中选择一些子字段，查询将如下所示 -

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

您甚至可以使用表名（例如在多表查询中），如下所示 -

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

范围类型

范围类型表示使用数据范围的数据类型。范围类型可以是离散范围（例如，所有整数 1 到 10）或连续范围（例如，上午 10:00 到上午 11:00 之间的任何时间点）。

可用的内置范围类型包括以下范围 -

• int4range - 整数范围

• int8range - 大整数范围

• numrange - 数值范围

• tsrange - 无时区时间戳范围

• tstzrange - 有时区时间戳范围

• daterange - 日期范围

可以创建自定义范围类型以使新的范围类型可用，例如使用 inet 类型作为基类的 IP 地址范围，或使用 float 数据类型作为基类的浮点范围。

范围类型分别使用 [ ] 和 ( ) 字符支持包含和排除范围边界。例如 '[4,9)' 表示从 4 开始（包括 4）到 9（不包括 9）的所有整数。

对象标识符类型

对象标识符 (OID) 在 PostgreSQL 内部用作各种系统表的主键。如果指定了 WITH OIDS 或启用了 default_with_oids 配置变量，则在这种情况下，OID 将添加到用户创建的表中。下表列出了几个别名类型。OID 别名类型除了专门的输入和输出例程外没有自己的操作。

伪类型

PostgreSQL 类型系统包含许多特殊用途的条目，这些条目统称为伪类型。伪类型不能用作列数据类型，但可以用作函数的参数或结果类型。

下表列出了现有的伪类型。