PostgreSQL数据类型详解

PostgreSQL作为一款功能强大的开源关系型数据库管理系统，提供了丰富多样的数据类型来满足不同场景的数据存储需求。正确选择和使用数据类型不仅能够提高存储效率，还能确保数据的准确性和完整性。本文将详细介绍PostgreSQL的各种数据类型及其使用场景。

一、数值类型详解

数值类型是数据库中最基础也是最重要的数据类型之一，用于存储各种数字数据。

整数类型

PostgreSQL提供了多种整数类型，分别适用于不同的数值范围：

-- 创建包含各种整数类型的表示例
CREATE TABLE numeric_demo (small_int_col SMALLINT,     -- 2字节，范围-32768到32767int_col INTEGER,            -- 4字节，范围-2147483648到2147483647big_int_col BIGINT,         -- 8字节，范围更大的整数serial_col SERIAL           -- 自动递增整数
);

选择整数类型时，应根据实际数据范围选择最小但足够的类型，以节省存储空间。

精确数值类型

对于需要精确计算的场景（如财务数据），应使用DECIMAL或NUMERIC类型：

-- 创建财务数据表示例
CREATE TABLE financial_data (product_price DECIMAL(10,2),     -- 总共10位，小数点后2位tax_rate NUMERIC(5,4),           -- 总共5位，小数点后4位quantity DECIMAL                 -- 无精度限制的十进制数
);

浮点数类型

对于科学计算等不需要精确精度的场景，可以使用浮点数类型：

-- 创建科学计算表示例
CREATE TABLE scientific_data (measurement REAL,              -- 4字节单精度浮点数calculation DOUBLE PRECISION   -- 8字节双精度浮点数
);

二、字符类型深度解析

字符类型用于存储文本数据，PostgreSQL提供了多种字符类型以适应不同需求。

固定长度字符类型

CHAR(n)类型存储固定长度的字符串，不足长度的部分会用空格填充：

-- 创建固定长度字符类型表示例
CREATE TABLE fixed_char_demo (country_code CHAR(2),          -- 固定2字符长度state_code CHAR(3)             -- 固定3字符长度
);

可变长度字符类型

VARCHAR(n)类型存储可变长度字符串，只占用实际需要的存储空间：

-- 创建可变长度字符类型表示例
CREATE TABLE variable_char_demo (user_name VARCHAR(50),         -- 最大50字符长度email_address VARCHAR(100),    -- 最大100字符长度description VARCHAR            -- 无长度限制
);

大文本类型

对于存储大量文本内容（如文章、日志），应使用TEXT类型：

-- 创建大文本类型表示例
CREATE TABLE content_demo (article_title VARCHAR(200),article_content TEXT,          -- 无长度限制的文本metadata JSONB                 -- JSON格式的元数据
);

三、日期时间类型详解

日期时间类型在业务系统中应用广泛，正确使用这些类型对数据准确性至关重要。

基本日期时间类型

-- 创建日期时间类型表示例
CREATE TABLE datetime_demo (birth_date DATE,                    -- 日期类型（年月日）meeting_time TIME,                  -- 时间类型（时分秒）appointment TIMESTAMP,              -- 日期时间类型alarm TIMESTAMPTZ,                  -- 带时区的日期时间work_interval INTERVAL              -- 时间间隔类型
);

日期时间类型的特点

DATE类型只存储日期信息，不包含时间部分；TIME类型只存储时间信息，不包含日期部分；TIMESTAMP类型同时包含日期和时间信息；TIMESTAMPTZ类型在TIMESTAMP基础上增加了时区信息。

四、布尔类型和特殊类型

布尔类型

BOOLEAN类型用于存储逻辑值，只有TRUE、FALSE和NULL三种值：

-- 创建布尔类型表示例
CREATE TABLE boolean_demo (is_active BOOLEAN DEFAULT TRUE,is_deleted BOOLEAN DEFAULT FALSE,is_verified BOOLEAN
);

二进制数据类型

BYTEA类型用于存储二进制数据，如图片、文件等：

-- 创建二进制数据类型表示例
CREATE TABLE binary_demo (file_name VARCHAR(100),file_data BYTEA,               -- 二进制数据file_size INTEGER
);

五、JSON和数组类型

JSON类型

PostgreSQL对JSON提供了原生支持，包括JSON和JSONB两种类型：

-- 创建JSON类型表示例
CREATE TABLE json_demo (user_id SERIAL PRIMARY KEY,user_profile JSON,             -- JSON文本存储user_settings JSONB            -- 二进制JSON存储（更高效）
);

JSONB类型相比JSON类型具有更好的性能，因为它以二进制格式存储，支持索引。

数组类型

PostgreSQL支持数组类型，可以存储相同类型的多个值：

-- 创建数组类型表示例
CREATE TABLE array_demo (student_name VARCHAR(50),grades INTEGER[],              -- 整数数组tags TEXT[],                   -- 文本数组scores DECIMAL(5,2)[]          -- 小数数组
);

六、自定义和枚举类型

枚举类型

ENUM类型允许定义一组固定的值：

-- 创建枚举类型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');-- 使用枚举类型
CREATE TABLE users (user_id SERIAL PRIMARY KEY,username VARCHAR(50),status user_status DEFAULT 'active'
);

复合类型

复合类型允许创建包含多个字段的自定义类型：

-- 创建复合类型
CREATE TYPE address_type AS (street VARCHAR(100),city VARCHAR(50),postal_code VARCHAR(10),country VARCHAR(50)
);-- 使用复合类型
CREATE TABLE customers (customer_id SERIAL PRIMARY KEY,customer_name VARCHAR(100),address address_type
);