PostgreSQL 数据类型：字节、数值、字符、二进制

PostgreSQL 为用户提供了丰富的原生数据类型。用户可以借助 CREATE TYPE 命令添加新类型。它还可以使查询更简单、更具可读性。

让我们详细研究 PostgreSQL 数据类型

字符数据类型

PostgreSQL 支持用于存储文本值的字符数据类型。PostgreSQL 使用相同的内部结构构建字符数据类型。PostgreSQL 提供三种字符数据类型：CHAR(n)、VARCHAR(n) 和 TEXT。

名称	描述
varchar(n)	允许您声明带有长度限制的可变长度
Char(n)	固定长度，用空格填充
文本	您可以使用此数据类型声明一个长度无限制的变量

数字数据类型

PostgreSQL 支持两种不同的数字类型

• 整数
• 浮点数

名称	存储大小	范围
smallint	2 字节	-32768 至 +32767
integer	4 字节	-2147483648 至 +2147483647
bigint	8 字节	-9223372036854775808 至 9223372036854775807
decimal	可变	如果将其声明为 decimal 数据类型，则小数点前范围为 131072 位，小数点后范围为 16383 位
numeric	可变	如果将其声明为数字，则小数点前可包含多达 131072 位数字，小数点后可包含 16383 位数字
real	4 字节	6 位小数精度
double	8 字节	15 位小数精度

二进制数据类型

二进制字符串是字节或八位字节的序列。Postgres 二进制数据类型分为两种。

• 二进制字符串允许存储零值
• 不可打印的八位字节

字符字符串不允许零八位字节，并且还根据数据库的字符集编码规则禁止任何其他无效的八位字节值和序列。

名称	存储大小	描述
Byte	1 至 4 字节加上二进制字符串的大小	可变长度二进制字符串

网络地址类型

许多应用程序会存储网络信息，例如用户或传感器的 IP 地址。PostgreSQL 拥有三种原生类型，可帮助您优化网络数据。

名称	大小	描述
cider	7 或 19 字节	IPV4 和 IPv6 网络
Inet	7 或 19 字节	IPV4 和 IPV5 主机和网络
macaddr	6 字节	MAC 地址

使用网络地址类型的优点如下：

1. 节省存储空间
2. 输入错误检查
3. 按子网搜索数据的函数等

文本搜索类型

PostgreSQL 提供了两种旨在支持全文搜索的数据类型。全文搜索是通过自然语言文档集合进行搜索，以查找与查询最匹配的文档。

• Tsvector 文本搜索 PostgreSQL 可变类型以一种优化过的形式表示文档，以便进行文本搜索
• 查询类型文本搜索存储需要搜索的关键字

日期/时间数据类型

PostgreSQL 时间戳提供微秒精度而不是秒精度。此外，您还可以选择是否存储时区。PostgreSQL 在输入时会将带时区的时间戳转换为 UTC 并进行存储。

日期和时间输入接受各种格式，包括传统的 Postgres、ISO 8601。SQL 兼容等。

PostgreSQL 支持日/月/年排序。支持的格式为 DMY、MDY、YMD

时间数据类型

名称	大小	范围	解决方案
不带时区的时间戳	8 字节	公元前 4713 年至公元 294276 年	1 微秒/14 位数
带时区的时间戳	8 字节	公元前 4713 年至公元 294276 年	1 微秒/14 位数
date	4 字节	公元前 4713 年至公元 294276 年	一天
不带时区的时间	8 字节	00:00:00 至 24:00:00	1 微秒/14 位数
带时区的时间	12 字节	00:00:00 + 1459 至 24:00:00-1459	1 微秒/14 位数
间隔	12 字节	-178000000 至 178000000 年	1 微秒/14 位数

示例

输入	描述
2025-09-07	ISO 8601，9 月 7 日，采用任何日期样式（推荐格式）
2025 年 9 月 7 日	9 月 7 日，采用任何日期样式
9/7/2025	9 月 7 日（MDY），7 月 9 日（DMY）
9/7/25	2025 年 9 月 7 日（MDY）
2025-9月-7	9 月 7 日，采用任何日期样式
9月-7-2018	9 月 7 日，采用任何日期样式
7-9月-25	2025 年 9 月 7 日（YMD）
20250907	ISO 8601，2025 年 9 月 7 日，任何模式
2025.250	年份和一年中的第几天，在此情况下为 2025 年 9 月 7 日
J25250	儒略日

时间/带时区的时间输入

输入	描述
11:19:38.507 11:19:38 11:19 111938	ISO 8601
上午 11:19	与 11:19 相同
下午 11:19	与 23:19 相同
23:19-3 23:19-03:00 231900-03	ISO 8601，与 EST 下午 11:19 相同
23:19 EST	指定为 EST 的时区，与 EST 下午 11:19 相同

布尔类型

布尔数据类型可以包含

• 真
• 假
• null

值。

您使用 bool 或 boolean 关键字来声明布尔数据类型的列。

当您将值插入布尔列时，Postgre 会将类似

• 是
• ÿ
• 1
• t
• 真

的值转换为 1。

而类似

• 否
• N
• 0
• F
• 假

的值则转换为 0

在选择数据时，这些值又会转换回 yes、true、y 等。

几何数据类型

几何数据类型表示二维空间对象。它们有助于执行旋转、缩放、平移等操作。

名称	存储大小	表示	描述
点	16 字节	平面上的点	(x,y)
直线	32 字节	无限直线	((xl.yl ).(x2.y2))
Lseg	32 字节	有限线段	((xl.yl ).(x2.y2))
Box	32 字节	矩形框	((xl.yl ).(x2.y2))
路径	16n + 16n 字节	闭合和开放路径	((xl.yl),…)
Polygon	40 + 16n 字节	Polygon	[(xl.yl)….]
Circle	24 字节	Circle	<(x.y).r> (中心点和半径)

枚举类型

PostgreSQL 的枚举数据类型有助于表示很少更改的信息，例如国家代码或分支 ID。枚举数据类型在表中表示，并使用外键来确保数据完整性。

示例

发色在人口统计数据库中相当静态

CREATE TYPE hair_color AS ENUM
('brown','black','red','grey','blond')

范围类型

许多业务应用程序需要范围中的数据。通常，定义两个列（例如：开始日期、结束日期）来处理范围。这既低效又难以维护。

Postgre 内置了以下范围类型

• int4range — 显示整数范围
• int8range — 显示 bigint 范围
• numrange — 显示数字范围
• tstrange — 帮助您显示不带时区的时间戳
• strange — 允许您显示带时区的时间戳
• date range — 日期范围

UUID 类型

通用唯一标识符（UUID）是一个由算法生成的 128 位量。世界上的其他人使用相同的算法生成相同标识符的可能性非常小。因此，对于分布式系统，这些标识符是理想的选择，因为它们在单个数据库中提供唯一性。UUID 写为一组小写十六进制数字，各组之间用连字符分隔。

PostgreSQL 具有原生的 UUID 数据类型，占用 16 字节存储空间。UUID 是主键的理想数据类型。

示例

d5f28c97-b962-43be-9cf8-ca1632182e8e

Postgre 也接受 UUID 的其他输入形式，例如全大写、无连字符、大括号等。

XML 类型

PostgreSQL 允许您将 XML 数据存储在数据类型中，但这不过是文本数据类型的扩展。但其优点在于它会检查输入的 XML 是否格式正确。

示例

XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><chapter>Data Type</chapter><chapter>...</chapter></tutorial>')

JSON 类型

为存储 JSON 数据，PostgreSQL 提供了 2 种数据类型

1. JSON
2. JSONB

json	Jsonb
文本数据类型的简单扩展，具有 JSON 验证功能	JSON 数据的二进制表示
插入速度快，但数据检索相对较慢。	插入速度慢，但选择（数据检索）速度快
以输入的原始格式保存数据，包括空格。	支持索引。可能会优化空格以加快检索速度。
数据检索时需要重新处理	数据检索时无需重新处理

除非有特殊需求使用 JSON 数据类型，否则大多数广泛使用的 JSON 数据类型是 jsonb。

示例

CREATE TABLE employee (
  id integer NOT NULL,
  age  integer NOT NULL,
  data jsonb
);

INSERT INTO employee VALUES (1, 35, '{"name": "Tom Price", "tags": ["Motivated", "SelfLearner"], "onboareded": true}'); 

伪类型

PostgreSQL 有许多特殊的条目，称为伪类型。您不能将伪类型用作 PostgreSQL 列类型。它们用于声明函数的参数或返回类型。

每种可用的伪类型在函数的行为文档与仅接受或返回特定SQL 数据类型的值不对应的情况下都很有用。

名称	描述
Any	函数接受所有输入数据类型。
An array	函数接受任何数组数据类型。
Any element	函数接受任何数据类型。
Any enum	函数接受任何枚举数据类型。
Nonarray	函数接受任何非数组数据类型。
Cstring	函数接受或返回以 null 结尾的 C 字符串。
内置	Internal 函数接受或返回服务器内部数据类型。
Language_handler	它被声明为返回语言处理器。
Record	查找返回未指定行类型的函数。
触发器	触发器函数用于返回触发器。

使用此函数的用户需要确保在使用伪类型作为参数类型时，该函数能安全地运行。

使用数据类型的最佳实践

• 除非您想限制输入，否则请使用“text”数据类型
• 切勿使用“char”。
• 整数使用“int”。只有在处理非常大的数字时才使用 bigint
• 几乎所有时候都使用“numeric”
• 如果您的 IEEE 754 数据源，请在 PostgreSQL 中使用 float

摘要

• PostgreSQL 为用户提供了丰富的原生数据类型
• PostgreSQL 支持用于存储文本值的字符数据类型
• PostgreSQL 支持两种数字类型：1. 整数，2. 浮点数
• 二进制字符串是字节或八位字节的序列
• PostgreSQL 拥有网络地址类型，可帮助您优化网络数据的存储
• 文本搜索 PostgreSQL 数据结构旨在支持全文搜索
• 日期/时间 PSQL 数据类型允许以各种格式存储日期和时间信息
• 布尔 PostgreSQL 字段类型可以包含三种值：1. True 2. False 3. Null
• 几何 PostgreSQL 数据类型表示二维空间对象
• PostgreSQL 中的枚举数据类型有助于表示很少更改的信息，例如国家代码或分支 ID
• 通用唯一标识符（UUID）是一个由算法生成的 128 位量
• PostgreSQL 有许多特殊的条目，称为伪类型
• 除非您想限制输入，否则最佳实践是使用“text”数据类型