PostgreSQL - 数据类型

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

这有几个好处

• 一致性- 针对相同数据类型的列的操作给出一致的结果并且通常是极快的。

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

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

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

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

数字类型

数字类型由两字节、四字节和八字节整数、四字节和八字节浮点数以及可选精度的小数组成。下表列出了可用的类型。

货币类型

货币类型以固定的分数精度存储货币数量。 数字、int和bigint数据类型的值可以强制转换为金钱。 不建议使用浮点数来处理货币，因为可能会出现舍入错误。

字符类型

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

二进制数据类型

所述BYTEA数据类型允许的二进制串的存储如下面给出的表中。

日期/时间类型

PostgreSQL 支持全套 SQL 日期和时间类型，如下表所示。日期是根据公历计算的。此处，除日期类型外，所有类型的分辨率均为1 微秒/14 位，分辨率为day。

布尔类型

PostgreSQL 提供了标准的 SQL 类型 Boolean。布尔数据类型可以具有true、false状态和第三个状态unknown，它由 SQL 空值表示。

枚举类型

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

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

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

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

几何类型

几何数据类型表示二维空间对象。极基本的类型，即点，构成了所有其他类型的基础。

网络地址类型

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

位串类型

位串类型用于存储位掩码。它们是 0 或 1。有两种 SQL 位类型：bit(n)和bitchanged(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 Object Notation)数据。 这样的数据也可以存储为文本，但是json数据类型具有检查每个存储值是否为有效json值的优点。 还有一些相关的支持函数可用，可以直接用于处理JSON数据类型，如下所示。

数组类型

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

数组声明

数组类型可以声明为

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

或使用关键字“ARRAY”作为

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

插入值

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

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

访问数组

访问数组的示例如下所示。下面给出的命令将选择第二季度比第四季度储蓄更多的人。

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

修改数组

修改数组的示例如下所示

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

或使用 ARRAY 表达式语法：

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

搜索数组

搜索数组的示例如下所示

SELECT * FROM pgccc_monthly_savings 
WHERE saving_per_quarter[1] = 10000 
ORsaving_per_quarter[2] = 10000 
ORsaving_per_quarter[3] = 10000 
ORsaving_per_quarter[4] = 10000;

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

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

复合类型

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

复合类型的声明

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

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

此数据类型可用于创建表，如下所示

CREATE TABLE pgccc_hand (   
item inventory_item,  
 count integer);

复合值输入

复合值可以作为文字常量插入，将字段值括在括号内并用逗号分隔它们。一个例子如下所示

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

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

访问复合类型

要访问复合列的字段，请使用点后跟字段名称，这与从表名称中选择字段非常相似。例如，要从我们的 pgccc_hand 示例表中选择一些子字段，查询将如下所示

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

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

SELECT (pgccc_hand.item).name 
FROM pgccc_hand 
WHERE (pgccc_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 类型系统包含许多特殊用途的条目，统称为伪类型。伪类型不能用作列数据类型，但可用于声明函数的参数或结果类型。

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