PostgreSQL - 数据类型

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


这有几个好处

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

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

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

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


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

数字类型

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

名称存储大小描述范围
smallint2 字节小范围整数-32768 至 +32767
integer4字节整数的典型选择-2147483648 至 +2147483647
bigint8 字节大范围整数-9223372036854775808 至 9223372036854775807
decimal多变的用户指定精度,精确小数点前极多 131072 位;小数点后极多 16383 位
numeric多变的用户指定精度,精确小数点前极多 131072 位;小数点后极多 16383 位
real4字节可变精度,不精确6 位小数精度
double precision8 字节可变精度,不精确15位十进制数字精度
smallserial2 字节自增小整数1 至 32767
serial4字节自增整数1 至 2147483647
bigserial8 字节大自增整数1 至 9223372036854775807

货币类型

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

名称存储大小描述范围
money8 字节货币金额-92233720368547758.08 至 +92233720368547758.07

字符类型

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

编号名称和描述
1

character varying(n), varchar(n)

有限制的可变长度

2

character(n), char(n)

固定长度,空白填充

3

text

可变无限长度


二进制数据类型

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

姓名存储大小描述
bytea1 或 4 个字节加上实际的二进制字符串变长二进制串


日期/时间类型

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

姓名存储大小描述低价值高价值

timestamp [(p)]

[without time

zone ]

8 字节日期和时间(无时区)公元前 4713 年公元 294276 年
TIMESTAMPTZ8 字节日期和时间,带时区公元前 4713 年公元 294276 年
date4字节日期(没有时间)公元前 4713 年公元5874897
time [ (p)] [8 字节时间(无日期)00:00:0024:00:00

time [ (p)] with

time zon

12 字节仅限一天中的时间,带时区00:00:00+145924:00:00-1459

interval [fields ]

[(p) ]

12 字节时间间隔-17.8亿年17.8亿年

布尔类型

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

姓名存储大小描述
boolean1 字节真假状态

枚举类型

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

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

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

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


几何类型

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

名称存储大小表示描述
point16 字节平面上的点(x,y)
line32 字节无限行(未完全实现)((x1,y1),(x2,y2))
lseg32 字节有限线段((x1,y1),(x2,y2))
box32 字节长方形盒子((x1,y1),(x2,y2))
path16+16n 字节闭合路径(类似于多边形)((x1,y1),...)
path16+16n 字节打开路径[(x1,y1),...]
polygon40+16n多边形(类似于封闭路径)((x1,y1),...)
circle24 字节圆圈<(x,y),r>(中心点和半径)

网络地址类型

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

名称存储大小描述
cidr7 或 19 字节IPv4 和 IPv6 网络
inet7 或 19 字节IPv4 和 IPv6 主机和网络
macaddr6 字节MAC地址

位串类型

位串类型用于存储位掩码。它们是 0 或 1。有两种 SQL 位类型:bit(n)和bitchanged(n),其中 n 是一个正整数。


文本搜索类型

此类型支持全文搜索,即搜索一组自然语言文档以找到与查询极匹配的文档的活动。这有两种数据类型

编号名称和描述
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 Object Notation)数据。 这样的数据也可以存储为文本,但是json数据类型具有检查每个存储值是否为有效json值的优点。 还有一些相关的支持函数可用,可以直接用于处理JSON数据类型,如下所示。

例子示例结果
array_to_json('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]
row_to_json(row(1,'foo')){"f1":1,"f2":"foo"}

数组类型

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 别名类型没有自己的操作。

名称参考描述值示例
oid任何数字对象标识符564182
regprocpg_proc函数名
regprocedurepg_proc带参数类型的函数总和(整数4)
regoperpg_operator操作员姓名+
regoperatorpg_operator带参数类型的运算符*(integer,integer) 或 -(NONE,integer)
regclasspg_class关系名称pg_type
regtypepg_type数据类型名称整数
regconfigpg_ts_config文本搜索配置英语
regdictionarypg_ts_dict文本搜索词典简单的


伪类型

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

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

编号名称和描述
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

表示函数不返回任何值。