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

由： 桧柳 桧柳
Hours 更新

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

数据类型 PostgreSQL

PostgreSQL 支持以下数据类型：

  • 文本类型
  • 数字类型
  • 日期和时间
  • XML
  • JSON
  • 布尔
  • 位
  • 二进制数据
  • 网络
  • 阵列
  • 创建你的数据类型
  • 布尔
  • 颞
  • UUID
  • 排列
  • JSON
  • 用于存储网络地址和几何数据的特殊数据类型。

来学习吧 PostgreSQL 数据类型详细

字符数据类型

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

姓名 描述
varchar(n) 允许你声明有限制的可变长度
字符(n) 固定长度，空白填充
文本 使用可以使用此数据类型来声明无限长度的变量

数字数据类型

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

  • 整数
  • 浮点数字
姓名 店铺规模 范围
Smallint 2字节 32768 + 32767
整数 4字节 2147483648 + 2147483647
比金特 8字节 9223372036854775808到9223372036854775807
十进制 变量 如果你将其声明为十进制数据类型，则范围从小数点前的 131072 位到小数点后的 16383 位
数字 变量 如果将其声明为数字，则可以包含小数点前最多 131072 位数字到小数点后最多 16383 位数字
真实 4字节 6 位小数精度
翻番 8字节 15 位小数精度

二进制数据类型

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

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

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

姓名 收纳尺寸 描述
字节 1 到 4 个字节加上二进制字符串的大小 可变长度二进制字符串

相关文章

网络地址类型

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

姓名 尺码 描述
苹果酒 7 或 19 个字节 IPV4 和 IPv6 网络
INET 7 或 19 个字节 IPV4 和 IPV5 主机和网络
mac地址 6字节 MAC地址

使用网络地址类型具有以下优点

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

文本搜索类型

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

  • Tsvector 文本搜索 PostgreSQL 变量类型以针对文本搜索优化的形式表示文档
  • 查询类型文本搜索存储需要搜索的关键字

日期/时间数据类型

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

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

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

时间数据类型

姓名 尺码 范围 分辨率
无时区的时间戳 8字节 公元前 4713 年至公元 294276 年 1微秒/14位数字
带时区的时间戳 8字节 公元前 4713 年至公元 294276 年 1微秒/14位数字
日期 4字节 公元前 4713 年至公元 294276 年 一天
无时区的时间 8字节 00:00:00 to 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，7 月 XNUMX 日，任意日期样式（推荐格式）
2025 年 9 月 7 日 7 月 XNUMX 日，任意日期格式
9/7/2025 7 月 9 日与 MDY 一起，XNUMX 月 XNUMX 日与 DMY 一起
9/7/25 7 年 2025 月 XNUMX 日，与 MDY
2025-9-7 7 月 XNUMX 日，任意日期格式
7 年 2018 月 XNUMX 日 7 月 XNUMX 日，任意日期格式
7-9-25 7 年 2025 月 XNUMX 日，YMD
20250907 ISO 8601,7 Sep 20225 在任何模式下
2025.250 年份和日期，本例中为 7 年 2025 月 XNUMX 日
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，与美国东部时间晚上 11:19 相同
23：19 EST 时区指定为 EST，与 EST 晚上 11:19 相同

布尔类型

布尔数据类型可以保存

  • 真
  • 假
  • 空

值。

你使用一个 布尔 or 布尔 关键字来声明具有布尔数据类型的列。

当你将值插入布尔列时，Postgre 会将值转换为

  • 是
  • y
  • 1
  • t
  • true

进入1。

虽然像

  • 没有
  • N
  • 0
  • F
  • 假

转换为 0

在选择数据时，值再次转换回是、真、y 等。

几何数据类型

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

姓名 储存空间 代表性 描述
点 16字节 平面上的点 （X，Y）
Line 32字节 无限线 ((xl.yl).(x2.y2))
LSEG 32字节 有限线段 ((xl.yl).(x2.y2))
Box 32字节 矩形 Box ((xl.yl).(x2.y2))
途径 16n + 16n 字节 关闭和打开路径 ((xl.yl),…)
Polygon 40 + 16n 字节 Polygon [(xl.yl)……]
圆 24字节 圆 <(xy).r>（中心点和半径）

枚举类型

枚举 PostgreSQL 数据类型对于表示很少变化的信息（例如国家代码或分支机构 ID）很有用。枚举数据类型在带有外键的表中表示，以确保数据完整性。

例如：

在人口统计数据库中，头发颜色相当稳定

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

枚举类型

范围类型

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

Postgre 已构建了如下范围类型

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

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 数据类型是 jsonb，除非有一些特殊需要使用 JSON 数据类型。

例如：

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 数据类型.

姓名 描述
任何 函数接受所有输入数据类型。
数组 该函数接受任何数组数据类型。
任意元素 该函数接受任何数据类型。
任何枚举 该函数接受任何枚举数据类型。
非数组 该函数接受任何非数组数据类型。
字符串 函数接受或返回以空字符结尾的 C 字符串。
内置 内部函数接受或返回服务器内部数据类型。
Language_handler 它被声明为返回语言处理程序。
Record 查找返回未指定行类型的函数。
触发端口 触发函数用于返回触发器。

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

使用数据类型的最佳实践

  • 使用“文本”数据类型，除非你想限制输入
  • 切勿使用“char”。
  • 整数使用“int”。只有当你有非常大的数字时才使用 bigint
  • 几乎总是使用“数字”
  • 使用浮动 PostgreSQL 如果你有 IEEE 754 数据源

总结

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

你可能会喜欢：