PostgreSQL – 数据类型
PostgreSQL – 数据类型
在本章中，我们将讨论 PostgreSQL 中使用的数据类型。创建表时，为每一列指定一种数据类型，即您希望在表字段中存储什么样的数据。
这有几个好处 –
-
一致性– 针对相同数据类型的列的操作给出一致的结果并且通常是最快的。
-
验证– 正确使用数据类型意味着对数据进行格式验证并拒绝数据类型范围之外的数据。
-
紧凑性– 由于列可以存储单一类型的值，因此它以紧凑的方式存储。
-
性能– 正确使用数据类型可以最有效地存储数据。可以快速处理存储的值，从而提高性能。
PostgreSQL 支持多种数据类型。此外，用户可以使用CREATE TYPE SQL 命令创建自己的自定义数据类型。PostgreSQL 中有不同类别的数据类型。它们将在下面讨论。
数字类型
数字类型由两字节、四字节和八字节整数、四字节和八字节浮点数以及可选精度的小数组成。下表列出了可用的类型。
| Name | 存储大小 | 描述 | 范围 |
|---|---|---|---|
| smallint | 2 字节 | 小范围整数 | -32768 至 +32767 |
| integer | 4字节 | 整数的典型选择 | -2147483648 至 +2147483647 |
| bigint | 8 字节 | 大范围整数 | -9223372036854775808 至 9223372036854775807 |
| decimal | 多变的 | 用户指定精度，精确 | 小数点前最多 131072 位；小数点后最多 16383 位 |
| numeric | 多变的 | 用户指定精度，精确 | 小数点前最多 131072 位；小数点后最多 16383 位 |
| real | 4字节 | 可变精度，不精确 | 6 位小数精度 |
| double precision | 8 字节 | 可变精度，不精确 | 15位十进制数字精度 |
| smallserial | 2 字节 | 自增小整数 | 1 至 32767 |
| serial | 4字节 | 自增整数 | 1 至 2147483647 |
| bigserial | 8 字节 | 大自增整数 | 1 至 9223372036854775807 |
货币类型
的钱类型存储与一个固定的分数精确度的货币金额。该值的数字，int和BIGINT数据类型可以转换为金钱。由于存在舍入错误的可能性，不建议使用浮点数来处理货币。
| Name | 存储大小 | 描述 | 范围 |
|---|---|---|---|
| money | 8 字节 | 货币金额 | -92233720368547758.08 至 +92233720368547758.07 |
字符类型
下表列出了 PostgreSQL 中可用的通用字符类型。
| S. No. | 名称和描述 |
|---|---|
| 1 |
character varying(n), varchar(n) 有限制的可变长度 |
| 2 |
character(n), char(n) 固定长度，空白填充 |
| 3 |
text 可变无限长度 |
二进制数据类型
所述BYTEA数据类型允许的二进制串的存储如下面给出的表中。
| Name | 存储大小 | 描述 |
|---|---|---|
| bytea | 1 或 4 个字节加上实际的二进制字符串 | 变长二进制串 |
日期/时间类型
PostgreSQL 支持全套 SQL 日期和时间类型，如下表所示。日期是根据公历计算的。这里，所有类型的分辨率都是1 微秒/14 位，但日期类型除外，其分辨率是day。
| Name | 存储大小 | 描述 | 低价值 | 高价值 |
|---|---|---|---|---|
| timestamp [(p)] [without time zone ] | 8 字节 | 日期和时间（无时区） | 公元前 4713 年 | 公元 294276 年 |
| TIMESTAMPTZ | 8 字节 | 日期和时间，带时区 | 公元前 4713 年 | 公元 294276 年 |
| date | 4字节 | 日期（没有时间） | 公元前 4713 年 | 公元5874897 |
| time [ (p)] [ without time zone ] | 8 字节 | 时间（无日期） | 00:00:00 | 24:00:00 |
| time [ (p)] with time zone | 12 字节 | 仅限一天中的时间，带时区 | 00:00:00+1459 | 24:00:00-1459 |
| interval [fields ] [(p) ] | 12 字节 | 时间间隔 | -17.8亿年 | 17.8亿年 |
布尔类型
PostgreSQL 提供了标准的 SQL 类型 Boolean。布尔数据类型可以具有true、false状态和第三个状态unknown，它由 SQL 空值表示。
| Name | 存储大小 | 描述 |
|---|---|---|
| boolean | 1 字节 | 真假状态 |
枚举类型
枚举 (enum) 类型是包含一组静态有序值的数据类型。它们等同于许多编程语言支持的枚举类型。
与其他类型不同，枚举类型需要使用 CREATE TYPE 命令创建。此类型用于存储一组静态、有序的值。例如指南针方向，即北、南、东和西或一周中的几天，如下所示 –
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
枚举一旦创建，就可以像任何其他类型一样使用。
几何类型
几何数据类型表示二维空间对象。最基本的类型，即点，构成了所有其他类型的基础。
| Name | 存储大小 | 表示 | 描述 |
|---|---|---|---|
| point | 16 字节 | 平面上的点 | (x,y) |
| line | 32 字节 | 无限行（未完全实现） | ((x1,y1),(x2,y2)) |
| lseg | 32 字节 | 有限线段 | ((x1,y1),(x2,y2)) |
| box | 32 字节 | 长方形盒子 | ((x1,y1),(x2,y2)) |
| path | 16+16n 字节 | 闭合路径（类似于多边形） | ((x1,y1),…) |
| path | 16+16n 字节 | 打开路径 | [(x1,y1),…] |
| polygon | 40+16n | 多边形（类似于封闭路径） | ((x1,y1),…) |
| circle | 24 字节 | 圆圈 | <(x,y),r>（中心点和半径） |
网络地址类型
PostgreSQL 提供数据类型来存储 IPv4、IPv6 和 MAC 地址。最好使用这些类型而不是纯文本类型来存储网络地址，因为这些类型提供输入错误检查和专门的运算符和函数。
| Name | 存储大小 | 描述 |
|---|---|---|
| cidr | 7 或 19 字节 | IPv4 和 IPv6 网络 |
| inet | 7 或 19 字节 | IPv4 和 IPv6 主机和网络 |
| macaddr | 6 字节 | MAC地址 |
位串类型
位串类型用于存储位掩码。它们是 0 或 1。有两种 SQL 位类型：bit(n)和bit changed(n)，其中 n 是一个正整数。
文本搜索类型
这种类型支持全文搜索，这是搜索自然语言文档集合以找到最匹配查询的那些的活动。这有两种数据类型 –
| S. No. | 名称和描述 |
|---|---|
| 1 |
tsvector 这是一个不同单词的排序列表，这些单词已被规范化以合并同一单词的不同变体，称为“词素”。 |
| 2 |
tsquery 这存储了要搜索的词素，并按照布尔运算符 & (AND), | 将它们组合起来。（或），和！（不是）。括号可用于强制对运算符进行分组。 |
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数据类型如下。
| Example | 示例结果 |
|---|---|
| array_to_json(‘{{1,5},{99,100}}’::int[]) | [[1,5],[99,100]] |
| row_to_json(row(1,’foo’)) | {“f1″:1,”f2″:”foo”} |
数组类型
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 别名类型没有自己的操作。
| Name | 参考 | 描述 | 值示例 |
|---|---|---|---|
| oid | 任何 | 数字对象标识符 | 564182 |
| regproc | pg_proc | 函数名 | 和 |
| regprocedure | pg_proc | 带参数类型的函数 | 总和（整数4） |
| regoper | pg_operator | 操作员姓名 | + |
| regoperator | pg_operator | 带参数类型的运算符 | *(integer,integer) 或 -(NONE,integer) |
| regclass | pg_class | 关系名称 | pg_type |
| regtype | pg_type | 数据类型名称 | 整数 |
| regconfig | pg_ts_config | 文本搜索配置 | 英语 |
| regdictionary | pg_ts_dict | 文本搜索词典 | 简单的 |
伪类型
PostgreSQL 类型系统包含许多特殊用途的条目，统称为伪类型。伪类型不能用作列数据类型，但可用于声明函数的参数或结果类型。
下表列出了现有的伪类型。
| S. No. | 名称和描述 |
|---|---|
| 1 |
any 表示函数接受任何输入数据类型。 |
| 2 |
anyelement 表示函数接受任何数据类型。 |
| 3 |
anyarray 表示函数接受任何数组数据类型。 |
| 4 |
anynonarray 表示函数接受任何非数组数据类型。 |
| 5 |
anyenum 表示函数接受任何枚举数据类型。 |
| 6 |
anyrange 表示函数接受任何范围数据类型。 |
| 7 |
cstring 指示函数接受或返回以空字符结尾的 C 字符串。 |
| 8 |
internal 指示函数接受或返回服务器内部数据类型。 |
| 9 |
language_handler 程序语言调用处理程序被声明为返回 language_handler。 |
| 10 |
fdw_handler 外部数据包装处理程序被声明为返回 fdw_handler。 |
| 11 |
record 标识返回未指定行类型的函数。 |
| 12 |
trigger 一个触发器函数被声明为返回触发器。 |
| 13 |
void 表示函数不返回任何值。 |
