印刻数据库

外观

PostgreSQL数据类型支持 ​

引言 ​

PostgreSQL支持丰富的数据类型，为开发者提供了极大的灵活性和表现力。选择合适的数据类型对于数据库设计至关重要，它直接影响到数据存储效率、查询性能和数据完整性。本文将详细介绍PostgreSQL支持的主要数据类型，包括基本类型、高级类型以及实际应用场景。

基本数据类型 ​

数值类型 ​

PostgreSQL支持多种数值类型，从精确的整数到浮点数和高精度小数。

类型存储大小范围描述
smallint2字节-32768 到 32767小范围整数
integer4字节-2147483648 到 2147483647常用整数类型
bigint8字节-9223372036854775808 到 9223372036854775807大范围整数
decimal可变最多131072位数字，小数点后最多16383位精确小数
numeric可变最多131072位数字，小数点后最多16383位精确小数（与decimal同义）
real4字节6位十进制精度单精度浮点数
double precision8字节15位十进制精度双精度浮点数
smallserial2字节1 到 32767小范围自增整数
serial4字节1 到 2147483647常用自增整数
bigserial8字节1 到 9223372036854775807大范围自增整数

使用示例：

sql
-- 创建包含不同数值类型的表
CREATE TABLE products (
    id serial PRIMARY KEY,
    name varchar(100),
    price numeric(10,2),  -- 精确到分的价格
    stock integer,        -- 库存数量
    weight real,          -- 重量（千克）
    rating double precision  -- 评分
);

-- 插入数据
INSERT INTO products (name, price, stock, weight, rating) VALUES
('Laptop', 999.99, 50, 2.5, 4.7),
('Smartphone', 699.99, 100, 0.15, 4.5);

字符串类型 ​

PostgreSQL提供了多种字符串类型，用于存储不同长度和格式的文本数据。

类型存储大小描述
char(n)固定n字节固定长度字符串，不足则填充空格
character(n)固定n字节与char(n)相同
varchar(n)可变，最大n字节可变长度字符串，最大长度为n
character varying(n)可变，最大n字节与varchar(n)相同
text可变，无最大限制无限长度文本
bytea可变，无最大限制二进制数据

使用示例：

sql
CREATE TABLE users (
    id serial PRIMARY KEY,
    username varchar(50) UNIQUE NOT NULL,
    email text NOT NULL,
    password_hash bytea NOT NULL,
    bio text,
    country char(2)  -- ISO国家代码，固定2个字符
);

INSERT INTO users (username, email, password_hash, bio, country) VALUES
('john_doe', 'john@example.com', digest('secure_password', 'sha256'), 'Software developer', 'US'),
('jane_smith', 'jane@example.com', digest('another_password', 'sha256'), NULL, 'GB');

日期/时间类型 ​

PostgreSQL支持多种日期和时间类型，用于存储不同精度的时间信息。

类型存储大小范围描述
date4字节4713 BC 到 5874897 AD仅日期
time8字节00:00:00 到 23:59:59.999999仅时间
time with time zone12字节00:00:00+1459 到 23:59:59.999999-1459带时区的时间
timestamp8字节4713 BC 到 5874897 AD日期和时间
timestamp with time zone8字节4713 BC 到 5874897 AD带时区的日期和时间
interval16字节-178000000 年 到 178000000 年时间间隔

使用示例：

sql
CREATE TABLE orders (
    id serial PRIMARY KEY,
    user_id integer REFERENCES users(id),
    order_date timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    shipping_date date,
    estimated_delivery interval,
    order_status varchar(20)
);

INSERT INTO orders (user_id, shipping_date, estimated_delivery, order_status) VALUES
(1, '2023-12-28', INTERVAL '3 days', 'processing'),
(2, '2023-12-29', INTERVAL '2 days', 'shipped');

-- 查询30天内的订单
SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL '30 days';

布尔类型 ​

用于存储真/假值。

类型存储大小取值
boolean1字节TRUE, FALSE, NULL

使用示例：

sql
CREATE TABLE products (
    id serial PRIMARY KEY,
    name varchar(100),
    is_active boolean DEFAULT TRUE,
    is_featured boolean DEFAULT FALSE
);

INSERT INTO products (name, is_active, is_featured) VALUES
('Laptop', TRUE, TRUE),
('Old Model', FALSE, FALSE);

-- 查询活跃且推荐的产品
SELECT * FROM products WHERE is_active = TRUE AND is_featured = TRUE;

高级数据类型 ​

JSON/JSONB ​

PostgreSQL支持两种JSON数据类型：

  • JSON：以文本格式存储，保留原始格式，但查询性能较低
  • JSONB：以二进制格式存储，支持高效查询和索引

使用示例：

sql
CREATE TABLE products (
    id serial PRIMARY KEY,
    name varchar(100),
    attributes jsonb  -- 使用JSONB类型存储产品属性
);

-- 插入JSON数据
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "cpu": "Intel i7", "ram": 16, "storage": 512, "color": ["black", "silver"]}'),
('Smartphone', '{"brand": "Apple", "model": "iPhone 15", "storage": 256, "color": ["pink", "black", "white"]}');

-- JSONB查询示例

-- 1. 查询特定品牌的产品
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- 2. 查询RAM大于等于16GB的产品
SELECT * FROM products WHERE (attributes->>'ram')::integer >= 16;

-- 3. 查询包含特定颜色的产品
SELECT * FROM products WHERE attributes->'color' ? 'black';

-- 4. 创建JSONB索引以提高查询性能
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

数组类型 ​

PostgreSQL支持任意数据类型的数组，包括多维数组。

使用示例：

sql
CREATE TABLE teams (
    id serial PRIMARY KEY,
    name varchar(100),
    member_ids integer[],  -- 成员ID数组
    tags text[],          -- 标签数组
    scores integer[][]    -- 二维数组，存储团队成员的分数
);

-- 插入数组数据
INSERT INTO teams (name, member_ids, tags, scores) VALUES
('Team A', '{1, 2, 3}', '{"development", "backend"}', '{{90, 85}, {88, 92}, {95, 87}}'),
('Team B', '{4, 5}', '{"development", "frontend", "design"}', '{{82, 78}, {89, 91}}');

-- 数组查询示例

-- 1. 查询包含特定成员的团队
SELECT * FROM teams WHERE 2 = ANY(member_ids);

-- 2. 查询包含特定标签的团队
SELECT * FROM teams WHERE '{"design"}' <@ tags;

-- 3. 查询标签数量大于2的团队
SELECT * FROM teams WHERE array_length(tags, 1) > 2;

-- 4. 访问数组元素（索引从1开始）
SELECT name, member_ids[1] AS first_member FROM teams;

-- 5. 创建数组索引
CREATE INDEX idx_teams_tags ON teams USING GIN(tags);

范围类型 ​

范围类型表示连续的值范围，PostgreSQL内置了多种范围类型，并允许创建自定义范围类型。

内置范围类型描述
int4range整数范围
int8range大整数范围
numrange数值范围
tsrange不带时区的时间范围
tstzrange带时区的时间范围
daterange日期范围

使用示例：

sql
CREATE TABLE bookings (
    id serial PRIMARY KEY,
    room_id integer,
    booking_period daterange,  -- 预订日期范围
    guest_name varchar(100),
    EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)  -- 防止同一房间的重叠预订
);

-- 插入范围数据
INSERT INTO bookings (room_id, booking_period, guest_name) VALUES
(1, '[2023-12-25, 2023-12-31)', 'John Doe'),
(2, '[2023-12-28, 2024-01-02)', 'Jane Smith');

-- 范围查询示例

-- 1. 查询特定日期的所有预订
SELECT * FROM bookings WHERE booking_period @> '2023-12-28'::date;

-- 2. 查询与指定范围重叠的预订
SELECT * FROM bookings WHERE booking_period && '[2023-12-30, 2024-01-05)'::daterange;

-- 3. 查询包含指定范围的预订
SELECT * FROM bookings WHERE booking_period @> '[2023-12-26, 2023-12-29)'::daterange;

-- 4. 查询特定房间的当前预订
SELECT * FROM bookings WHERE room_id = 1 AND booking_period @> CURRENT_DATE;

UUID类型 ​

UUID（通用唯一标识符）是一种128位的标识符，用于在分布式系统中唯一标识对象。

使用示例：

sql
-- 首先需要启用uuid-ossp扩展
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),  -- 使用UUID作为主键
    username varchar(50) UNIQUE NOT NULL,
    email text NOT NULL
);

-- 插入数据（UUID会自动生成）
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com');

-- 查询数据
SELECT * FROM users WHERE id = '550e8400-e29b-41d4-a716-446655440000';

几何类型 ​

PostgreSQL支持多种几何类型，用于存储空间数据。

几何类型描述示例
point点(1, 2)
line无限长直线
lseg线段[(1, 2), (3, 4)]
box矩形框((1, 2), (3, 4))
path路径（闭合或开放）[(1, 2), (3, 4), (5, 6)]
polygon多边形((1, 2), (3, 4), (5, 6), (1, 2))
circle圆形<(1, 2), 3>

使用示例：

sql
CREATE TABLE locations (
    id serial PRIMARY KEY,
    name varchar(100),
    coordinates point,  -- 坐标点
    area polygon,       -- 区域多边形
    radius circle       -- 覆盖范围圆
);

-- 插入几何数据
INSERT INTO locations (name, coordinates, area, radius) VALUES
('Office', '(10, 20)', '((5, 15), (15, 15), (15, 25), (5, 25), (5, 15))', '<(10, 20), 10>'),
('Warehouse', '(30, 40)', '((25, 35), (35, 35), (35, 45), (25, 45), (25, 35))', '<(30, 40), 15>');

-- 几何查询示例

-- 1. 查询距离给定点一定范围内的位置
SELECT * FROM locations WHERE coordinates <@ circle '<(15, 25), 15>';

-- 2. 查询两个位置之间的距离
SELECT name, coordinates <-> point '(15, 25)' AS distance FROM locations ORDER BY distance;

-- 3. 查询包含给定点的区域
SELECT * FROM locations WHERE area @> point '(12, 22)';

网络地址类型 ​

PostgreSQL支持存储IP地址和MAC地址。

类型描述示例
cidrIPv4或IPv6网络192.168.1.0/24
inetIPv4或IPv6地址192.168.1.100
macaddrMAC地址08:00:2b:01:02:03
macaddr88字节MAC地址08:00:2b:01:02:03:04:05

使用示例：

sql
CREATE TABLE network_devices (
    id serial PRIMARY KEY,
    name varchar(100),
    ip_address inet,     -- IP地址
    network cidr,        -- 所属网络
    mac_address macaddr  -- MAC地址
);

-- 插入网络数据
INSERT INTO network_devices (name, ip_address, network, mac_address) VALUES
('Server A', '192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03'),
('Server B', '10.0.0.50', '10.0.0.0/8', '08:00:2b:04:05:06'),
('Workstation', '2001:db8::1', '2001:db8::/32', '08:00:2b:07:08:09');

-- 网络查询示例

-- 1. 查询特定网络中的设备
SELECT * FROM network_devices WHERE ip_address <<= network;

-- 2. 查询特定子网中的设备
SELECT * FROM network_devices WHERE ip_address << '192.168.0.0/16';

-- 3. 查询IP地址在特定范围内的设备
SELECT * FROM network_devices WHERE ip_address BETWEEN '192.168.1.1' AND '192.168.1.254';

自定义数据类型 ​

PostgreSQL允许用户创建自定义数据类型，包括枚举类型、复合类型和域。

枚举类型 ​

枚举类型用于存储预定义的一组值。

使用示例：

sql
-- 创建枚举类型
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- 使用枚举类型
CREATE TABLE orders (
    id serial PRIMARY KEY,
    customer_id integer,
    order_date timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    status order_status DEFAULT 'pending'  -- 使用枚举类型
);

-- 插入数据
INSERT INTO orders (customer_id, status) VALUES
(1, 'processing'),
(2, 'shipped');

-- 查询数据
SELECT * FROM orders WHERE status = 'shipped';

-- 枚举类型的排序是基于创建时的顺序
SELECT DISTINCT status FROM orders ORDER BY status;

复合类型 ​

复合类型允许将多个字段组合成一个新的类型。

使用示例：

sql
-- 创建复合类型
CREATE TYPE address AS (
    street varchar(100),
    city varchar(50),
    state varchar(50),
    zip_code varchar(20),
    country varchar(50)
);

-- 使用复合类型
CREATE TABLE customers (
    id serial PRIMARY KEY,
    name varchar(100),
    shipping_address address,  -- 使用复合类型
    billing_address address    -- 使用复合类型
);

-- 插入数据
INSERT INTO customers (name, shipping_address, billing_address) VALUES
('John Doe', 
 ('123 Main St', 'New York', 'NY', '10001', 'USA'),
 ('123 Main St', 'New York', 'NY', '10001', 'USA')
),
('Jane Smith', 
 ('456 Oak Ave', 'London', 'England', 'SW1A 1AA', 'UK'),
 ('456 Oak Ave', 'London', 'England', 'SW1A 1AA', 'UK')
);

-- 查询复合类型字段
SELECT name, (shipping_address).city, (shipping_address).country FROM customers;

-- 更新复合类型字段
UPDATE customers SET shipping_address = ROW('789 Pine St', 'New York', 'NY', '10002', 'USA') WHERE id = 1;

域 ​

域是基于现有类型的约束类型，用于限制字段的取值范围。

使用示例：

sql
-- 创建域类型
CREATE DOMAIN positive_integer AS integer CHECK (VALUE > 0);
CREATE DOMAIN email_address AS text CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

-- 使用域类型
CREATE TABLE users (
    id serial PRIMARY KEY,
    username varchar(50) UNIQUE NOT NULL,
    email email_address NOT NULL,  -- 使用邮箱域类型
    age positive_integer CHECK (age >= 18)  -- 使用正整数域类型，并添加额外约束
);

-- 插入有效数据
INSERT INTO users (username, email, age) VALUES
('john_doe', 'john@example.com', 30);

-- 尝试插入无效数据（会失败）
-- INSERT INTO users (username, email, age) VALUES ('invalid_user', 'not_an_email', 17);

版本差异 ​

不同PostgreSQL版本引入了新的数据类型和增强：

PostgreSQL 12 ​

  • 增强了JSONB支持，包括SQL/JSON路径表达式
  • 改进了B-tree索引对范围类型的支持

PostgreSQL 13 ​

  • 增强了数组操作的性能
  • 支持jsonb_path_ops索引的更高效查询

PostgreSQL 14 ​

  • 增强了JSONB处理，包括新的聚合函数
  • 改进了范围类型的边界处理

PostgreSQL 15 ​

  • 支持MERGE语句，增强了数据操作能力
  • 改进了数组的排序和去重功能

PostgreSQL 16 ​

  • 新增向量数据类型（vector），用于机器学习和相似度搜索
  • 增强了JSONB的查询性能

数据类型选择最佳实践 ​

  1. 选择最精确的类型：使用能够准确表示数据的最小类型，例如使用integer而不是bigint（如果值在integer范围内）

  2. 考虑性能影响：

    • 较小的类型通常更高效
    • JSONB比JSON具有更好的查询性能
    • 数组类型可以减少表连接，但可能使查询更复杂

  3. 考虑数据完整性：

    • 使用域类型添加额外约束
    • 对于预定义的值集，使用枚举类型
    • 对于结构化数据，考虑使用复合类型而不是JSONB

  4. 考虑扩展性：

    • 对于可能增长的数据，选择足够大的类型
    • 对于不确定结构的数据，JSONB提供了灵活性

  5. 考虑兼容性：

    • 如果需要与其他系统集成，选择通用类型
    • 考虑ORM框架对特定类型的支持

常见问题（FAQ） ​

什么时候应该使用JSONB而不是JSON？ ​

JSONB类型在大多数情况下是更好的选择，因为它：

  • 支持高效的索引
  • 提供更快的查询性能
  • 自动删除不必要的空格
  • 支持更丰富的查询操作

JSON类型仅在需要保留原始JSON格式（包括空格和顺序）时使用。

数组类型和单独的关联表哪个更好？ ​

这取决于具体场景：

  • 数组类型：适合小型、固定大小的集合，查询简单但不适合频繁更新
  • 关联表：适合大型、动态的集合，支持复杂查询和频繁更新，但需要额外的表连接

如何选择合适的数值类型？ ​

  • 对于整数，根据值的范围选择smallint、integer或bigint
  • 对于精确小数（如货币），使用numeric/decimal类型
  • 对于科学计算或不需要精确结果的场景，使用real或double precision

PostgreSQL支持哪些时间精度？ ​

PostgreSQL的timestamp和time类型支持微秒（6位小数）精度。可以通过类型修饰符指定更低的精度，例如timestamp(3)表示毫秒精度。

如何创建自定义数据类型？ ​

PostgreSQL支持三种自定义数据类型：

  • 枚举类型：使用CREATE TYPE ... AS ENUM
  • 复合类型：使用CREATE TYPE ... AS (
  • 域类型：使用CREATE DOMAIN ... AS

向量数据类型有什么用途？ ​

PostgreSQL 16引入的向量数据类型主要用于：

  • 机器学习模型的特征存储
  • 相似度搜索（如图像、文本、音频）
  • 推荐系统
  • 异常检测

总结 ​

PostgreSQL丰富的数据类型支持是其强大功能的重要组成部分，为开发者提供了极大的灵活性和表现力。从基本的数值和字符串类型到高级的JSONB、数组和范围类型，再到自定义类型，PostgreSQL能够满足各种复杂应用场景的需求。

选择合适的数据类型对于数据库设计至关重要，它直接影响到数据存储效率、查询性能和数据完整性。开发者应该根据实际需求，结合各种数据类型的特点和最佳实践，做出合理的选择。

随着PostgreSQL的不断发展，新的数据类型和增强功能将持续推出，进一步扩展其应用领域和性能表现。