异常教程

PostgreSQL 数据类型（千字长文）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习 ，欢迎点击围观

在 PostgreSQL 数据库的开发中，理解数据类型的选择与使用是构建高效、可靠的系统的基石。对于编程初学者和中级开发者而言，掌握 PostgreSQL 的数据类型不仅能提升代码编写效率，还能避免因类型误用导致的性能问题或逻辑错误。本文将通过通俗易懂的比喻、实际案例和代码示例，系统性地解析 PostgreSQL 数据类型的核心概念与应用场景，帮助读者在开发中做出更明智的选择。

一、基础数据类型：数据库的“基础砖块”

PostgreSQL 提供了丰富的基础数据类型，类似于编程语言中的变量类型，用于定义表中列的存储形式。我们可以将这些类型想象为快递包裹的分类：每个包裹（数据）必须按照指定的尺寸（类型）包装，才能被系统正确处理。

1. 数值类型

PostgreSQL 的数值类型覆盖了整数、浮点数、精确小数等场景，适合存储不同精度的数值数据。

(1) 整数类型

  • integer (int)：存储 4 字节的整数，取值范围为 -2,147,483,648 到 2,147,483,647。
  • bigint：8 字节整数，适用于需要更大范围的场景（如社交媒体的点赞数）。
  • smallint：2 字节整数，节省空间但取值范围较小。

案例：电商订单表设计

CREATE TABLE orders (  
    order_id integer PRIMARY KEY,  
    product_id integer NOT NULL,  
    quantity integer CHECK (quantity > 0)  
);

(2) 浮点与精确小数类型

  • float：单精度浮点数，适合对精度要求不高的场景。
  • double precision：双精度浮点数，精度更高。
  • numeric(p,s)：精确小数类型，p 表示总位数，s 表示小数位数，适合金融、科学计算。

示例：金融交易表

CREATE TABLE transactions (  
    amount numeric(10,2) NOT NULL  -- 10位总长度，2位小数  
);

2. 字符串类型

字符串类型用于存储文本数据，常见的包括：

(1) text

无长度限制的文本字段，适合存储长篇内容（如用户评论）。

CREATE TABLE articles (  
    content text  
);

(2) varchar(n)

可变长度字符串，n 表示最大长度，适合固定长度的文本（如省份名称）。

CREATE TABLE users (  
    province varchar(20)  
);

(3) char(n)

固定长度字符串，不足时用空格填充，适用于需要严格格式的场景（如身份证号）。

CREATE TABLE customers (  
    id_number char(18)  
);

3. 布尔型与日期时间类型

(1) boolean

存储布尔值（true/false），适合标记状态（如用户是否激活）。

CREATE TABLE accounts (  
    is_active boolean DEFAULT false  
);

(2) 日期时间类型

  • date：仅存储日期（如出生日期）。
  • timestamp：存储日期与时间（精确到微秒）。
  • interval：存储时间间隔（如“3天5小时”）。

案例：订单创建时间记录

CREATE TABLE orders (  
    created_at timestamp DEFAULT CURRENT_TIMESTAMP  
);

二、复合数据类型：构建复杂数据的“积木”

PostgreSQL 的复合数据类型允许开发者将多个基础类型组合成更复杂的结构，适用于需要存储多维信息的场景。

1. 数组类型

数组类型用 data_type[] 表示，支持多维数组。例如，存储用户兴趣标签：

CREATE TABLE users (  
    hobbies text[]  
);  
INSERT INTO users (hobbies) VALUES ('{reading,traveling,music}');

2. JSON/JSONB 类型

  • json：存储未处理的 JSON 文本，适合需要保留原始结构的场景。
  • jsonb：二进制格式的 JSON，支持索引和查询优化，适合频繁查询的场景。

案例：用户配置信息存储

CREATE TABLE user_settings (  
    id integer PRIMARY KEY,  
    preferences jsonb  
);  
INSERT INTO user_settings (preferences)  
VALUES ('{"theme": "dark", "notifications": true}');

通过 jsonb 可以直接查询字段：

SELECT * FROM user_settings  
WHERE preferences ->> 'theme' = 'dark';

3. 枚举类型

通过 CREATE TYPE 自定义枚举类型，确保列的取值在预定义范围内。

CREATE TYPE status AS ENUM ('pending', 'completed', 'canceled');  
CREATE TABLE tasks (  
    status status NOT NULL  
);

三、特殊数据类型：应对特定场景的“工具箱”

PostgreSQL 还提供了一些针对特殊需求的数据类型，例如几何、网络地址、UUID 等。

1. UUID

用于生成全局唯一标识符，常用于分布式系统或需要避免自增主键冲突的场景。

CREATE TABLE users (  
    user_id uuid PRIMARY KEY DEFAULT gen_random_uuid()  
);

2. 网络地址类型

  • inet：存储 IPv4/IPv6 地址及子网掩码（如 192.168.1.0/24）。
  • macaddr：存储 MAC 地址。

案例：IP 地址记录

CREATE TABLE login_attempts (  
    ip inet NOT NULL  
);

3. 几何类型

支持存储几何形状（如点、线、多边形），适用于 GIS（地理信息系统）开发。

CREATE TABLE locations (  
    position point,  
    area polygon  
);

四、数据类型选择的实用建议：避免“尺寸不符”的误区

1. 根据业务需求选择精度

  • 对于金融交易，优先使用 numeric 而非浮点类型，避免精度丢失。
  • 对于 ID、计数器等整数，根据可能的最大值选择 integer 或 bigint。

2. 空间与性能的权衡

  • text 类型适合大文本，但索引效率较低；若需频繁查询短文本，varchar 更合适。
  • 使用 jsonb 时，合理设计索引（如 CREATE INDEX ON table USING GIN (jsonb_column)）以提升查询速度。

3. 预见性设计

  • 为未来扩展预留空间：例如，使用 bigint 替代 integer 避免增长溢出。
  • 枚举类型可减少业务逻辑中的字符串硬编码错误。

五、常见问题与解决方案

1. 数据类型转换错误

当执行 SELECT '2023-01-01'::date + 1 时，PostgreSQL 会自动将整数视为天数进行加法运算。若类型不兼容（如字符串与数值相加），需显式转换：

SELECT '10'::integer + 5;  -- 正确

2. 空间优化技巧

对于频繁查询的 jsonb 字段，可使用索引优化：

CREATE INDEX idx_preferences ON user_settings USING GIN (preferences);

3. 版本差异与兼容性

PostgreSQL 不同版本可能新增数据类型（如 citext 用于不区分大小写的文本）。开发前需确认目标版本的支持情况。

结论

PostgreSQL 数据类型的合理选择，直接影响数据库的性能、数据完整性和代码的可维护性。通过本文的解析，读者可以掌握基础类型的核心特性、复合类型的组合逻辑以及特殊场景的解决方案。在实际开发中，建议遵循以下原则：

  1. 明确业务需求：根据存储内容选择最合适的类型。
  2. 预留扩展空间：避免因类型限制导致后续重构。
  3. 善用工具特性：例如 JSONB 的查询优化或枚举类型的约束能力。

掌握这些知识后，开发者不仅能编写更健壮的 SQL 语句，还能在架构设计阶段提前规避潜在风险。PostgreSQL 数据类型的灵活与强大，正是它成为企业级数据库首选的重要原因之一。

最新发布

查看更多文章