当前位置: 首页 > news >正文

️ PostgreSQL 数据类型

一、数据类型体系概览

PostgreSQL 拥有极其丰富的数据类型系统,既包含标准 SQL 类型,也扩展了许多高级类型。

1.1 数据类型分类总览

分类 主要类型 特点 应用场景
数值类型 整数、小数、浮点数 精确/近似计算 统计、财务、科学计算
字符串类型 CHAR, VARCHAR, TEXT 文本存储与处理 用户信息、文章内容
布尔类型 BOOLEAN 逻辑判断 状态标志、开关设置
日期时间 DATE, TIME, TIMESTAMP 时间处理 日志、事件、计划
二进制数据 BYTEA 原始字节存储 文件、图像、加密数据
枚举类型 ENUM 预定义值集合 状态码、分类标签
几何类型 POINT, LINE, CIRCLE 空间数据 地理位置、图形计算
网络类型 INET, CIDR, MACADDR 网络地址 设备管理、日志分析
JSON 类型 JSON, JSONB 半结构化数据 配置、API 数据
数组类型 type[] 同类型集合 标签、多值属性
范围类型 数值/日期范围 连续区间 时间段、价格区间
特殊类型 UUID, XML, TSVECTOR 特定用途 唯一标识、全文搜索

二、数值类型详解

2.1 整数类型对比

类型 别名 存储空间 范围 描述 推荐场景
smallint int2 2 字节 -32,768 到 32,767 小范围整数 年龄、数量较小计数
integer int4 4 字节 -2.1亿 到 2.1亿 标准整数 ID、计数、大多数数值
bigint int8 8 字节 ±9.2×10¹⁸ 大范围整数 大数据量、金融金额

2.2 自增序列类型

类型 基础类型 存储空间 范围 描述
smallserial smallint 2 字节 1 到 32,767 自增小整数
serial integer 4 字节 1 到 2.1亿 自增整数(推荐)
bigserial bigint 8 字节 1 到 9.2×10¹⁸ 自增大整数

示例:

CREATE TABLE users (id SERIAL PRIMARY KEY,        -- 自增主键age SMALLINT,                 -- 年龄login_count INTEGER,          -- 登录次数big_number BIGINT             -- 大数值
);

2.3 精确小数类型

类型 别名 存储空间 精度控制 描述 适用场景
decimal(p,s) numeric(p,s) 可变 精确 定点小数 财务计算、精确测量
numeric(p,s) decimal(p,s) 可变 精确 定点小数 同 decimal

精度参数说明:

  • p:总位数(1-1000+)
  • s:小数位数
CREATE TABLE financial_data (price DECIMAL(10,2),      -- 价格:总共10位,2位小数tax_rate NUMERIC(5,4),    -- 税率:0.0000 到 0.9999amount NUMERIC            -- 任意精度
);

2.4 浮点数类型

类型 别名 存储空间 精度 范围 特点
real float4 4 字节 6位 ±1.18×10⁻³⁸ 到 ±3.4×10³⁸ 单精度,性能好
double precision float8 8 字节 15位 ±2.23×10⁻³⁰⁸ 到 ±1.79×10³⁰⁸ 双精度,更精确

示例:

CREATE TABLE scientific_data (temperature REAL,                    -- 温度测量precise_calculation DOUBLE PRECISION -- 精确计算
);

三、字符串与文本类型

3.1 字符串类型对比

类型 描述 存储空间 特点 推荐场景
char(n) 定长字符串 n 字节 不足补空格,检索快 固定长度代码(如国家代码)
varchar(n) 变长字符串 实际长度+1 长度限制,节省空间 有限长度文本(用户名、邮箱)
text 变长文本 实际长度+1 无长度限制,功能强 任意长度文本(内容、描述)

3.2 字符串类型选择指南

场景 推荐类型 理由
固定长度编码 CHAR(n) 存储效率高,性能好
有限可变文本 VARCHAR(n) 空间优化,有约束
任意长度内容 TEXT 无限制,最灵活
不确定长度 TEXT 避免长度限制问题

示例:

CREATE TABLE user_data (country_code CHAR(2),        -- 国家代码,固定2位username VARCHAR(50),        -- 用户名,最多50字符email VARCHAR(100),          -- 邮箱地址biography TEXT,              -- 个人简介,无长度限制phone CHAR(11)               -- 电话号码
);

四、日期时间类型

4.1 日期时间类型详解

类型 完整名称 存储空间 范围 精度 示例
date DATE 4 字节 4713 BC - 5874897 AD 1 天 '2024-01-15'
time TIME 8 字节 00:00:00 - 24:00:00 1 微秒 '14:30:25.123456'
timestamp TIMESTAMP 8 字节 4713 BC - 5874897 AD 1 微秒 '2024-01-15 14:30:25'
timestamptz TIMESTAMP WITH TIME ZONE 8 字节 4713 BC - 5874897 AD 1 微秒 '2024-01-15 14:30:25+08'
interval INTERVAL 16 字节 ±178000000 年 1 微秒 '2 days 3 hours'

4.2 日期时间操作示例

-- 创建表
CREATE TABLE events (event_date DATE,                          -- 事件日期start_time TIME,                          -- 开始时间created_at TIMESTAMP,                     -- 创建时间(无时区)updated_at TIMESTAMPTZ DEFAULT NOW(),     -- 更新时间(带时区)duration INTERVAL                         -- 持续时间
);-- 常用函数
SELECT NOW() AS current_time,                    -- 当前时间戳CURRENT_DATE AS today,                    -- 当前日期CURRENT_TIME AS now_time,                 -- 当前时间EXTRACT(YEAR FROM created_at) AS year,    -- 提取年份AGE(created_at) AS time_ago;              -- 计算时间间隔

五、布尔与二进制类型

5.1 布尔类型

类型 存储空间 取值 有效输入
boolean 1 字节 TRUE, FALSE, NULL true/false, 't'/'f', 'yes'/'no', '1'/'0'

示例:

CREATE TABLE settings (is_active BOOLEAN DEFAULT TRUE,is_verified BOOLEAN DEFAULT FALSE,newsletter_optin BOOLEAN
);-- 多种写入方式
INSERT INTO settings VALUES (TRUE, FALSE, NULL),('t', 'f', 'yes'),('1', '0', 'no');

5.2 二进制类型

类型 描述 存储空间 格式 应用场景
bytea 二进制数据 1-4字节+实际大小 十六进制或转义格式 文件存储、图片、加密数据

示例:

CREATE TABLE documents (id SERIAL PRIMARY KEY,file_name TEXT,file_data BYTEA,              -- 存储二进制文件file_size INTEGER
);-- 插入二进制数据
INSERT INTO documents (file_name, file_data) 
VALUES ('image.png', E'\\x89504E470D0A1A0A0000000D49484452');

六、高级数据类型

6.1 JSON 类型对比

类型 存储格式 验证 索引支持 性能 推荐度
JSON 文本格式 写入时验证 有限 读取慢,写入快 ★★☆☆☆
JSONB 二进制格式 写入时验证 完整 读取快,写入稍慢 ★★★★★

JSONB 示例:

CREATE TABLE user_profiles (id SERIAL PRIMARY KEY,profile_data JSONB,           -- 推荐使用 JSONBcreated_at TIMESTAMPTZ DEFAULT NOW()
);-- 插入 JSON 数据
INSERT INTO user_profiles (profile_data) VALUES 
('{"name": "张三","age": 30,"address": {"city": "北京","street": "朝阳区"},"hobbies": ["读书", "游泳", "编程"]
}');-- JSON 查询操作
SELECT profile_data->>'name' AS name,                    -- 获取字符串值profile_data->'address'->>'city' AS city,         -- 嵌套查询profile_data->'hobbies'->>0 AS primary_hobby      -- 数组元素
FROM user_profiles;-- JSON 路径查询
SELECT profile_data#>>'{address,city}' AS city FROM user_profiles;

6.2 数组类型

声明方式 示例 描述
数据类型[] INTEGER[] 整数数组
数据类型[长度] VARCHAR(50)[] 字符串数组
多维数组 TEXT[][] 文本二维数组

数组操作示例:

CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,tags TEXT[],                    -- 标签数组prices DECIMAL[],               -- 价格历史dimensions INTEGER[3]           -- 固定长度数组
);-- 插入数组数据
INSERT INTO products (name, tags, prices, dimensions) VALUES 
('笔记本电脑', '{"电子产品","电脑","便携"}', '{4999.00, 4599.00, 4799.00}', '{30,20,5}');-- 数组查询
SELECT name,tags[1] AS primary_tag,         -- 访问数组元素array_length(tags, 1) AS tag_count,  -- 数组长度unnest(tags) AS individual_tag  -- 展开数组
FROM products
WHERE '电子产品' = ANY(tags);        -- 数组包含检查

6.3 范围类型

类型 描述 示例 适用场景
int4range 整数范围 [1,10) 年龄范围、数量区间
int8range 大整数范围 [100,1000] ID 范围、大数值区间
numrange 数值范围 (10.5,20.5] 价格区间、测量范围
tsrange 时间戳范围 [2024-01-01,2024-12-31] 时间段、有效期
daterange 日期范围 [2024-01-01,2024-06-30] 日期区间

范围类型示例:

CREATE TABLE reservations (id SERIAL PRIMARY KEY,room_id INTEGER,period TSRANGE,                 -- 时间段price_range NUMRANGE           -- 价格范围
);INSERT INTO reservations (room_id, period, price_range) VALUES 
(1, '[2024-01-15 14:00, 2024-01-20 10:00)', '[100.00, 200.00)');-- 范围查询
SELECT * FROM reservations 
WHERE period @> '2024-01-16 12:00'::TIMESTAMP;  -- 包含时间点

七、特殊数据类型

7.1 网络地址类型

类型 描述 存储空间 示例 适用场景
inet IP地址(IPv4/IPv6) 7或19字节 '192.168.1.1' 设备IP管理
cidr 网络地址块 7或19字节 '192.168.1.0/24' 子网划分
macaddr MAC地址 6字节 '08:00:2b:01:02:03' 网络设备

示例:

CREATE TABLE network_devices (id SERIAL PRIMARY KEY,device_name TEXT,ip_address INET,network CIDR,mac_address MACADDR
);-- 网络操作
SELECT ip_address,HOST(ip_address) AS host,           -- 提取主机部分MASKLEN(network) AS mask_length     -- 子网掩码长度
FROM network_devices
WHERE ip_address << network;            -- IP是否在网段内

7.2 其他特殊类型

类型 描述 存储空间 示例 用途
uuid 通用唯一标识符 16字节 '550e8400-e29b-41d4-a716-446655440000' 分布式ID
xml XML数据 可变 <book><title>SQL</title></book> 结构化文档
money 货币金额 8字节 '$123.45' 金融数据
tsvector 全文搜索向量 可变 'cat:1 dog:2' 全文索引
tsquery 全文搜索查询 可变 'cat & dog' 搜索查询

UUID 示例:

-- 启用 UUID 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE TABLE distributed_users (user_id UUID DEFAULT uuid_generate_v4(),  -- 自动生成 UUIDusername TEXT,created_at TIMESTAMPTZ DEFAULT NOW()
);INSERT INTO distributed_users (username) VALUES ('john_doe');

八、枚举与几何类型

8.1 枚举类型

-- 创建枚举类型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE product_size AS ENUM ('small', 'medium', 'large', 'x-large');-- 使用枚举
CREATE TABLE users (id SERIAL PRIMARY KEY,name TEXT,status user_status DEFAULT 'active'
);CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,size product_size
);

8.2 几何类型

类型 描述 存储空间 示例 用途
point 点坐标 16字节 (3,5) 地理位置
line 无限直线 32字节 {1,-1,0} 几何计算
lseg 线段 32字节 [(1,2),(3,4)] 线段表示
box 矩形 32字节 (1,1),(0,0) 边界框
path 路径 16+16n字节 [(0,0),(1,1),(2,0)] 运动轨迹
polygon 多边形 40+16n字节 ((0,0),(1,0),(1,1),(0,1)) 区域划分
circle 圆形 24字节 <(1,1),2> 圆形区域

九、类型选择最佳实践

9.1 数据类型选择指南

数据特征 推荐类型 理由 示例
主键标识 SERIAL, BIGSERIAL, UUID 自动生成,保证唯一性 用户ID、订单ID
金额价格 NUMERIC/DECIMAL 精确计算,避免误差 商品价格、账户余额
一般文本 TEXT 无长度限制,灵活 文章内容、描述
固定代码 CHAR(n) 存储效率高 国家代码、状态码
时间戳 TIMESTAMPTZ 时区支持,标准化 创建时间、更新时间
开关状态 BOOLEAN 语义清晰,存储高效 是否激活、是否验证
配置数据 JSONB 灵活结构,查询高效 用户配置、元数据
多个值 数组类型 避免关联表 标签、分类
唯一标识 UUID 分布式唯一性 分布式系统ID
二进制文件 BYTEA 原生二进制支持 图片、文档

9.2 性能优化建议

场景 优化建议 理由
频繁查询的数值 使用合适范围的整数类型 减少存储,提高计算速度
文本搜索 TEXT 列创建索引 加速文本查询
JSON 查询 使用 JSONB + GIN 索引 支持各种 JSON 查询
范围查询 使用范围类型 + GiST 索引 高效的范围操作
数组操作 对数组列创建 GIN 索引 加速数组包含查询

十、类型转换与函数

10.1 类型转换方法

-- 显式类型转换
SELECT '123'::INTEGER;
SELECT CAST('123' AS INTEGER);
SELECT '2024-01-15'::DATE;-- 隐式类型转换
SELECT 3 + '5';                    -- 自动转为数字
SELECT 'Hello' || 123;             -- 自动转为文本-- 类型检测
SELECT pg_typeof(123.4);           -- 返回数据类型

10.2 常用类型函数

类别 函数 描述 示例
数值函数 ROUND(), CEIL(), FLOOR() 四舍五入、向上/向下取整 ROUND(3.14159, 2) → 3.14
字符串函数 LENGTH(), UPPER(), LOWER() 长度、大小写转换 LENGTH('hello') → 5
日期函数 EXTRACT(), DATE_PART(), AGE() 提取部分、计算间隔 EXTRACT(YEAR FROM NOW())
JSON函数 ->, ->>, jsonb_extract_path() JSON 数据提取 data->>'name'
数组函数 array_length(), unnest() 数组操作 unnest(tags)
http://www.hskmm.com/?act=detail&tid=33886

相关文章:

  • CSP-J/S 2025 第一轮游记
  • 【汇编和指令集 . 第2025 .10期】万般皆为投影
  • 小作业 12
  • Python 潮流周刊#123:你可能不需要单例模式
  • Python 潮流周刊#122:Python 3.14 来了,速度如何?
  • 机器学习在视频质量检测中的技术应用
  • 基于博客园和xmlrpc的Typora图片上传脚本
  • 一位焦虑的普通二本软件工程的学生
  • C++类的运算符重载
  • 10.18 CSP-S模拟34/2025多校CSP模拟赛6 改题记录
  • 微软Office LTSC 2021(KpoJIuK直装版)x64 v16.0.14334.20344 10月版
  • 征程 6 | 征程 6 工具链如何支持 Matmul/Conv 双 int16 输入量化?
  • 结对项目:自动生成小学四则运算题目的命令行程序
  • 做题技巧与结论证明
  • 1. 两数之和
  • CSP-S模拟34/2025多校冲刺CSP模拟赛6
  • PostgreSQL 逻辑结构
  • 随机数技术
  • Java学习通互评5
  • 卡车厂实习第三天
  • 第六周作业---定时器
  • 『普及』浅谈图的基础
  • 被C语言链表折磨的一天 Σ( △ |||)︴
  • 运筹学在供应链优化中的实际应用
  • P6715 [CCO 2018] Fun Palace 题解
  • WebGL学习及项目实战(第03期:绘制多个点,线,面)
  • CF 359D. Pair of Numbers
  • 2025多校CSP模拟赛6
  • Java基础——类型转换,变量、常亮、作用域,基本运算符
  • 洛谷 LGR-246 S 模拟赛