PostgreSQL数据类型详解

发布时间: 2025-04-08 09:42

↑

# PostgreSQL数据类型详解

本文将详细介绍PostgreSQL支持的各种数据类型，包括它们的使用场景、特点和最佳实践，帮助你在数据库设计时做出正确的类型选择。

## 数值类型

### 1. 整数类型

| 类型名称 | 存储空间 | 范围 | 用途 |
|---------|---------|------|------|
| smallint | 2字节 | -32768 到 +32767 | 小范围整数 |
| integer | 4字节 | -2147483648 到 +2147483647 | 常用整数 |
| bigint | 8字节 | -9223372036854775808 到 +9223372036854775807 | 大范围整数 |
| serial | 4字节 | 1 到 2147483647 | 自增整数 |
| bigserial | 8字节 | 1 到 9223372036854775807 | 大范围自增整数 |

```sql
CREATE TABLE products (
    product_id serial PRIMARY KEY,      -- 自增ID
    stock_count smallint,               -- 库存数量
    total_sales integer,                -- 总销售量
    global_identifier bigint            -- 全球唯一标识符
);
```

### 2. 小数类型

| 类型名称 | 描述 | 精度 | 用途 |
|---------|------|------|------|
| decimal(p,s) | 精确小数 | 用户指定 | 金融计算 |
| numeric(p,s) | 精确小数 | 用户指定 | 金融计算 |
| real | 单精度浮点数 | 6位数字 | 科学计算 |
| double precision | 双精度浮点数 | 15位数字 | 科学计算 |

```sql
CREATE TABLE financial_records (
    record_id serial PRIMARY KEY,
    amount decimal(10,2),              -- 金额，2位小数
    exchange_rate numeric(7,4),        -- 汇率，4位小数
    scientific_value double precision   -- 科学计算值
);
```

## 字符类型

### 1. 固定长度字符

```sql
CREATE TABLE documents (
    doc_id serial PRIMARY KEY,
    country_code char(2),              -- 国家代码，固定2字符
    postal_code char(6),               -- 邮政编码，固定6字符
    status char(1)                     -- 状态码，单字符
);
```

### 2. 可变长度字符

```sql
CREATE TABLE users (
    user_id serial PRIMARY KEY,
    username varchar(50),              -- 用户名，最大50字符
    email varchar(255),                -- 邮箱，最大255字符
    description text,                  -- 描述，无长度限制
    long_text text                     -- 长文本内容
);
```

## 日期和时间类型

### 1. 日期时间类型

| 类型名称 | 存储空间 | 描述 | 范围 |
|---------|---------|------|------|
| date | 4字节 | 仅日期 | 4713 BC 到 5874897 AD |
| time | 8字节 | 仅时间 | 00:00:00 到 24:00:00 |
| timestamp | 8字节 | 日期和时间 | 4713 BC 到 294276 AD |
| timestamptz | 8字节 | 带时区的日期和时间 | 4713 BC 到 294276 AD |
| interval | 16字节 | 时间间隔 | -178000000 年 到 178000000 年 |

```sql
CREATE TABLE events (
    event_id serial PRIMARY KEY,
    event_date date,                   -- 活动日期
    start_time time,                   -- 开始时间
    created_at timestamp,              -- 创建时间
    updated_at timestamptz,            -- 更新时间（带时区）
    duration interval                  -- 持续时间
);
```

## 布尔类型

```sql
CREATE TABLE features (
    feature_id serial PRIMARY KEY,
    is_active boolean,                 -- 是否激活
    is_deleted boolean DEFAULT false,   -- 是否删除
    is_premium boolean NOT NULL        -- 是否高级功能
);
```

## 枚举类型

```sql
-- 创建枚举类型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');

CREATE TABLE users (
    user_id serial PRIMARY KEY,
    status user_status DEFAULT 'inactive',  -- 用户状态
    membership_type user_type               -- 会员类型
);
```

## 几何类型

```sql
CREATE TABLE spatial_data (
    data_id serial PRIMARY KEY,
    point_data point,                  -- 点
    line_data line,                    -- 线
    polygon_data polygon,              -- 多边形
    circle_data circle                 -- 圆
);
```

## 网络地址类型

```sql
CREATE TABLE network_info (
    info_id serial PRIMARY KEY,
    ip_address inet,                   -- IPv4和IPv6地址
    mac_address macaddr,               -- MAC地址
    network_range cidr                 -- 网络范围
);
```

## JSON类型

```sql
CREATE TABLE documents (
    doc_id serial PRIMARY KEY,
    data json,                         -- JSON数据
    metadata jsonb                     -- 二进制JSON数据
);

-- 插入JSON数据
INSERT INTO documents (data) VALUES ('
{
    "title": "PostgreSQL Guide",
    "author": {
        "name": "John Doe",
        "email": "john@example.com"
    },
    "tags": ["database", "postgresql", "guide"]
}'
);
```

## 数组类型

```sql
CREATE TABLE items (
    item_id serial PRIMARY KEY,
    tags text[],                       -- 文本数组
    scores integer[],                  -- 整数数组
    coordinates point[]                -- 点数组
);

-- 插入数组数据
INSERT INTO items (tags, scores) VALUES (
    ARRAY['tag1', 'tag2', 'tag3'],
    ARRAY[1, 2, 3, 4, 5]
);
```

## 复合类型

```sql
-- 创建复合类型
CREATE TYPE address AS (
    street varchar(100),
    city varchar(50),
    country varchar(50),
    postal_code varchar(10)
);

CREATE TABLE contacts (
    contact_id serial PRIMARY KEY,
    name varchar(100),
    home_address address,              -- 家庭地址
    work_address address               -- 工作地址
);
```

## 使用建议

### 1. 类型选择原则

- 选择最小但足够的数据类型
- 考虑数据的增长性
- 注意精度要求
- 考虑查询性能

### 2. 性能考虑

- 使用合适的索引类型
- 避免过度使用TEXT类型
- JSON类型优先使用JSONB
- 合理使用数组和复合类型

### 3. 最佳实践

```sql
-- 金融数据使用decimal
CREATE TABLE transactions (
    transaction_id serial PRIMARY KEY,
    amount decimal(10,2),              -- 金额
    tax_rate decimal(5,4)              -- 税率
);

-- 时间戳默认使用timestamptz
CREATE TABLE logs (
    log_id serial PRIMARY KEY,
    created_at timestamptz DEFAULT CURRENT_TIMESTAMP
);

-- 使用适当的字符串类型
CREATE TABLE products (
    product_id serial PRIMARY KEY,
    sku char(8),                       -- 固定长度编码
    name varchar(100),                 -- 变长但有限制
    description text                   -- 无长度限制
);
```

## 总结

PostgreSQL提供了丰富的数据类型支持，正确选择和使用这些数据类型可以：

1. 确保数据完整性
2. 优化存储空间
3. 提高查询性能
4. 简化应用程序开发

在实际应用中，应该根据具体需求选择合适的数据类型，并遵循最佳实践来优化数据库设计。同时，要注意不同数据类型的限制和性能特征，以构建高效、可靠的数据库应用。

元素码农