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。布尔数据类型可以具有truefalse状态和第三个状态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

表示函数不返回任何值。

觉得文章有用?

点个广告表达一下你的爱意吧 !😁