PostgreSQL 数据类型

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

这样做有以下几个好处：

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

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

• 紧凑 - 由于列只能存储一种类型的值，所以以紧凑的方式存储。

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

PostgreSQL支持广泛的数据类型。此外，用户可以使用CREATE TYPE SQL命令创建自定义数据类型。PostgreSQL中有不同类别的数据类型，下面将对它们进行讨论。

数值类型

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

货币类型

货币类型是一个带有固定小数精度的货币金额。可以将数值、整数和大整数数据类型转换为货币类型。不推荐使用浮点数处理货币，因为可能会出现舍入误差。

字符类型

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

二进制数据类型

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

日期/时间类型

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

布尔类型

PostgreSQL提供了标准的SQL类型布尔。布尔数据类型可以有以下三种状态：真（true），假（false）和第三种状态未知（unknown），该状态由SQL的null值表示。

枚举类型

枚举（enum）类型是由静态有序值组成的数据类型。它们相当于许多编程语言中支持的枚举类型。

与其他类型不同，枚举类型需要使用CREATE TYPE命令创建。该类型用于存储静态有序的一组值。例如，指南针方向，即北，南，东和西，或以下示例中的星期几−

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

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

几何类型

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

网络地址类型

PostgreSQL提供了用于存储IPv4、IPv6和MAC地址的数据类型。在存储网络地址时，最好使用这些类型而不是普通的文本类型，因为这些类型提供了输入错误检查和特殊的操作符和函数。

位字符串类型

位字符串类型用于存储位掩码。它们的值要么是0，要么是1。有两种SQL位类型： 位(n) 和 位可变(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对象标记）数据。此类数据也可以存储为文本，但是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 - bigint范围

• numrange - 数字范围

• tsrange - 没有时区的时间戳范围

• tstzrange - 带有时区的时间戳范围

• daterange - 日期范围

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

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

对象标识符类型

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

伪类型

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

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