PostgreSQL 数据类型

PostgreSQL 数据类型

数据类型指定要在表字段中存储的数据类型。创建表时，对于每一列，您必须使用一种数据类型。它标识特定类型的数据，如整数、布尔值、浮点数等。

在本节中，我们将讨论 PostgreSQL 中使用的各种数据类型。

在PostgreSQL 中，每个数据库表都有很多列，并且每一列都有精确的数据类型。它支持广泛的数据类型。

此外，用户可以使用 CREATE TYPE SQL命令创建他们的自定义数据类型。而这些数据类型具有以下优点：

  • Performance:如果我们正确有效地使用这些数据类型来存储数据值，它会提高我们的性能。
  • Validation:数据类型的正确使用涉及数据的验证和数据类型范围之外的数据的消除。
  • Compactness:它有效地存储，因为一列可以存储单一类型的值。
  • Consistency：相同数据类型的列的矛盾活动提供可靠的结果并且通常是最快的。

在 PostgreSQL 中，我们有很多数据类型集；让我们一一了解：

PostgreSQL 数据类型

  • Numeric datatype
  • Character datatype
  • Date/time datatype
  • Monetary data type
  • Binary data type
  • Boolean data type
  • Enumerated data type
  • Geometric data type
  • Text search data type
  • UUID data type
  • Network address type
  • JSON data type
  • Bit string type
  • XML data type
  • Range data type
  • Arrays
  • Composite data type
  • Object identifiers type
  • Pseudo data type
  • pg-Isn data type

数字数据类型

数值数据类型用于指定表中的数值数据。它包含以下内容：

  • 四字节和八字节浮点数
  • 二、四和八字节整数
  • 可选精度小数。

下表包含PostgreSQL 支持的所有数字数据类型：

名称 描述 存储大小 范围
smallint 存储整数，小范围。 2bytes -32768 至 +32767
integer 存储整数。当你想存储典型的整数时使用它。 4bytes -2147483648 至 +2147483647
bigint 存储整数，大范围。 8bytes -9223372036854775808 至 9223372036854775807
decimal 用户指定的精度，精确 variable 小数点前最多 131072 位；小数点后最多 16383 位。
numeric 用户指定的精度，精确 variable 小数点前最多 131072 位；小数点后最多 16383 位。
real 可变精度，不精确 4bytes 6 位十进制数字精度。
double precision 可变精度，不精确 8bytes 15位十进制数字精度
serial 自增整数 4bytes 1 至 2147483647
bigserial 大的自动递增整数 8bytes 1 至 9223372036854775807

字符数据类型

在 PostgreSQL 中，我们有各种通用的字符数据类型，这些数据类型用于表示字符类型值。

下表包含PostgreSQL 支持的所有字符数据类型：

数据类型 解释
字符（大小） 这里的大小是要存储的字符数。定长字符串。右侧填充空格以达到相同大小的字符。
字符（大小） 这里的大小是要存储的字符数。定长字符串。右侧填充空格以达到相同大小的字符。
变量（大小） 这里的大小是要存储的字符数。变长字符串。
字符变化（大小） 这里的大小是要存储的字符数。变长字符串。
文本 变长字符串。

日期/时间数据类型

PostgreSQL 支持完整的 SQL日期和时间数据类型集。日期/时间数据类型用于表示使用日期和时间值的列。根据公历计算日期。

下表包含PostgreSQL 支持的所有日期/时间数据类型：

名称 描述 存储大小 最小值 最大值 解析度
时间戳 [ (p) ] [ 不带时区 ] 日期和时间（无时区） 8 字节 公元前 4713 年 294276 广告 1 微秒 / 14 位
时间戳 [ (p) ] 带时区 日期和时间，带时区 8 字节 公元前 4713 年 294276 广告 1 微秒 / 14 位
日期 日期（没有时间） 4字节 公元前 4713 年 5874897广告 1天
时间 [ (p) ] [ 不带时区 ] 时间（无日期） 8 字节 00:00:00 24:00:00 1 微秒 / 14 位
时间 [ (p) ] 带时区 仅限一天中的时间，带时区 12 字节 00:00:00+1459 24:00:00-1459 1 微秒 / 14 位
间隔 [ 字段 ] [ (p) ] 时间间隔 12 字节 -17.8亿年 17.8亿年 1 微秒 / 14 位

货币类型：

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

二进制数据类型

该bytea的数据类型允许二进制字符串的存储，我们可以在下面的表格中看到：

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

boolean

PostgreSQL 包含标准 SQL 类型boolean；Boolean 类型有许多状态，例如true 、 false和第三个状态unknown，由 SQL 空值表示。

名称 描述 存储大小
boolean 它指定真或假的状态。 1 字节

Boolean 数据类型输入函数接收这些真假状态的字符串演示：

PostgreSQL 数据类型

枚举类型

在 PostgreSQL 中，枚举数据类型包括一组静态的、有序的值。

它类似于与各种编程语言兼容的枚举类型。枚举数据类型在带有外键的表中表示，以确保数据完整性。

例如：

CREATE TYPE fruits_name AS ENUM  
('Mango','Apple','Orange','Strawberry')

几何数据类型

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

Name Storage size Representation Description
point 16 bytes point on a plane (x,y)
line 32 bytes infinite line (not fully implemented) ((x1,y1),(x2,y2))
lseg 32 bytes finite line segment ((x1,y1),(x2,y2))
box 32 bytes rectangular box ((x1,y1),(x2,y2))
path 16+16n bytes closed path (similar to polygon) ((x1,y1),...)
path 16+16n bytes open path [(x1,y1),...]
polygon 40+16n polygon (similar to closed path) ((x1,y1),...)
circle 24 bytes circle <(x,y),r> (center point and radius)

文本搜索数据类型

在 PostgreSQL 中，全文搜索数据类型用于搜索自然语言文档的集合。我们有两类与全文搜索兼容的数据类型。

数据类型 描述
tsvector 它用于以表格形式显示文档，从而增强文本搜索。
tsquery 它用于表示文本查询。

UUID 数据类型

UUID 代表通用唯一标识符，是由算法创建的 128 位数量。它是最适合主键的数据类型。UUID 被写成一组小写的十六进制数字，通过多组以连字符分隔。

例如：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

注意：PostgreSQL 还同意采用 UUID 输入的替代形式，例如无连字符、全部大写、大括号等。

网络地址数据类型

PostgreSQL 提供了存储Mac 地址、IPv4 和 IPv6 的数据类型，如下表所示。它增强了除纯文本类型之外的这些类型以包含网络地址，因为这些类型提供输入错误检查以及特定的功能和运算符。

下表包含PostgreSQL 支持的所有网络地址数据类型：

数据类型 描述 存储大小
inet 它存储 IPv4 和 IPv6 主机和网络。 7 或 19 字节
cidr 它用于存储 IPv4 和 IPv6 网络。 7 或 19 字节
macaddr 它存储 MAC 地址。 6 字节

JSON 数据类型

PostgreSQL 提供了两种数据类型来存储 JSON（JavaScript Object Notation）数据。

  • JSON
  • JSONB

JSON

它是具有JSON验证的文本数据类型的扩展。在这种情况下，我们可以快速插入数据，但数据检索相对较慢。它以包含空格的方式保存输入的数据。它还在对数据检索进行再处理。

JSONB

它是JSON数据的二进制表示。它还与索引兼容，并且还改进了空格以使检索更快。在这种情况下，插入速度较慢，但数据检索速度较快，并且需要对数据检索进行重新处理。

位串类型

位字符串数据类型包含两类字符串，即1 和 0。可以在这些字符串的帮助下存储位掩码。在此，我们有两种SQL 位，例如：

bit varying(n)
bit (n)

这里，n是正整数。

XML 类型

在 PostgreSQL 中，XML 数据类型用于存储XML数据。XML 数据类型的功能是检查输入的 XML 是否格式良好，并且还有支持函数对其执行类型安全操作。

例如：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>PostgreSQL</title><chapter>...</chapter></book>')

范围类型

这些数据类型用于显示某些元素类型的值范围，称为范围的子类型。它还表示单个范围值中的多个值元素。在这里，我们还可以创建我们的范围类型。

在 PostgreSQL 中，我们有以下内置范围类型：

内置范围类型 描述
tsrange 不带时区的时间戳范围
tstzrange 带时区的时间戳范围
daterange 日期范围
int4range 整数范围
int8range bigint 的范围
numrange 数值范围

数组类型

在这方面，PostgreSQL 提供了一列表作为可变长度和多维数组。我们可以创建任何用户定义的基本类型、内置、复合和枚举类型数组。

在这里，我们可以对数组进行声明、插入、访问、修改和搜索等各种操作。

复合类型

在 PostgreSQL 中，复合数据类型用于将行或记录的结构表示为文件名和数据类型的列表。

伪数据类型

在 PostgreSQL 中，数据类型是伪类型，用于包含许多特殊用途的条目。它用于声明结果类型或函数的参数，但不兼容用作列数据类型。

下表包含了PostgreSQL 支持的一些常用伪数据类型：

伪数据类型 描述
any 它表明函数接受任何输入数据类型。
anyelement 它接受任何数据类型。
anyarray 它显示了一个接受任何数组数据类型的函数
anyenum 它接受任何枚举数据类型
anyrange 它接受任何范围的数据类型
cstring 它用于指定函数接受或返回以空字符结尾的 C 字符串。
language_handler 程序语言调用处理程序被声明为返回 language_handler。
fdw_handler fdw（外部数据包装器）处理程序被声明为返回 fdw_handler。
record 它用于指定接受或返回未指定行类型的函数。
trigger 它被声明为返回触发器。
pg_ddl_command 它用于表示可用于事件触发器的 DDL 命令。
void 它用于指定函数不返回任何值。

对象标识符 (OID) 类型

这些类型的数据类型用作多个系统表的主键。的OID类型表示一个对象标识符，目前作为一个无符号的四字节整数来实现的。在庞大的数据库中，甚至在大型的单个表中，它都不足以提供数据库范围的个性化。

对象标识符用于引用系统表。除了比较之外，oid 类型本身几乎没有可以转换为整数的操作，并且可以使用标准整数运算符进行操作。

下表包含PostgreSQL 支持的所有对象标识符数据类型：

Name Description References Value Example
oid numeric object identifier Any 564182
regproc function name pg_proc Sum
regprocedure function with argument types pg_proc sum(int4)
regoper operator name pg_operator +
regoperator operator with argument types pg_operator *(integer,integer) or -(NONE,integer)
regclass relation name pg_class pg_type
regtype data type name pg_type Integer
regnamespace namespace name pg_namespace pg_catalog
regconfig text search configuration pg_ts_config English
regdictionary text search dictionary pg_ts_dict Simple

pg_lsn 类型

所述pg_lsn数据类型可以被用来存储日志序列号（LSN）的数据，一个指针到XLOG的位置。它用于表示 XLogRecPtr 和 PostgreSQL 的内部系统类型。pg_lsn 类型与标准比较运算符兼容，例如> 和 =。

笔记：

在使用数据类型时，我们可以参考以下几点：

  • 如果我们有一个 IEEE 754 数据源，我们可以使用float数据类型
  • 对于整数数据类型，我们可以使用int。
  • 永远不要使用char。
  • 如果我们想限制输入，我们可以应用text数据类型。
  • 当我们有大量数据时，我们只能使用bigint。

概述

  • 该character data types用于存储文本值。
  • Network address type用于优化网络的数据的存储。
  • 甲binary string是一个字节或八位位组的分类。
  • PostgreSQL 提供了两种不同类型的数字，例如Floating-point numbers 和 integers。
  • range 的数据类型是用来显示一个范围内的一些元件类型的值的。
  • Boolean数据类型有三个值，如真，假，和Null。
  • UUID数据类型是一个128位的数量，这是由一个算法制成。
  • 该composite数据类型用于表示一个行或记录的结构。
  • PostgreSQL，the enumerated Data类型，很少用于展示修改后的信息，如分支 id 或国家代码。
  • 为了以多种格式定义日期和时间信息，PostgreSQL 提供了Date and Time数据类型。
  • object identifiers的数据类型表示的对象标识符。
  • PostgreSQL 设计了text search data 以支持全文搜索。
  • Geometric 数据类型用于表示二维空间对象。
  • 在 PostgreSQL 中，pseudo type用于许多特殊用途的条目。
  • 所述pg_lsn数据类型用于存储日志序列号（LSN）的数据。