表操作

表是PostgreSQL数据库中存储数据的基本单位。本章将详细介绍如何创建、修改和删除表，以及表的约束、默认值等重要概念。

创建表

基本语法

使用 CREATE TABLE 语句创建表：

sql

-- 创建表的基本语法
CREATE TABLE 表名 (
    列名1 数据类型 [约束条件],
    列名2 数据类型 [约束条件],
    ...
);

-- 创建一个简单的用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,              -- SERIAL是自增整数类型，PRIMARY KEY设置为主键
    username VARCHAR(50) NOT NULL,      -- VARCHAR(50)表示最多50字符，NOT NULL表示不能为空
    email VARCHAR(100) UNIQUE,          -- UNIQUE表示该列的值必须唯一
    age INTEGER CHECK (age >= 0),       -- CHECK约束确保年龄非负
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- DEFAULT设置默认值为当前时间
);

完整示例：创建员工表

sql

-- 创建一个完整的员工表，包含多种约束
CREATE TABLE employees (
    -- 主键列：使用SERIAL自增
    emp_id SERIAL PRIMARY KEY,
    
    -- 员工姓名：不能为空
    emp_name VARCHAR(100) NOT NULL,
    
    -- 部门ID：外键引用departments表
    dept_id INTEGER REFERENCES departments(dept_id),
    
    -- 职位
    position VARCHAR(50),
    
    -- 薪水：使用CHECK约束确保薪水为正数
    salary NUMERIC(10, 2) CHECK (salary > 0),
    
    -- 入职日期：默认为当前日期
    hire_date DATE DEFAULT CURRENT_DATE,
    
    -- 状态：使用枚举类型或CHECK约束限制取值
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'resigned')),
    
    -- 创建时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 更新时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加表注释
COMMENT ON TABLE employees IS '员工信息表';
COMMENT ON COLUMN employees.emp_id IS '员工ID，主键';
COMMENT ON COLUMN employees.emp_name IS '员工姓名';
COMMENT ON COLUMN employees.dept_id IS '所属部门ID';

使用IF NOT EXISTS

sql

-- 如果表不存在则创建，避免重复创建报错
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2)
);

从查询结果创建表

sql

-- 使用CREATE TABLE AS从查询结果创建新表
-- 这会复制数据结构和数据，但不复制约束（如主键、索引等）
CREATE TABLE employees_backup AS
SELECT * FROM employees;

-- 只复制表结构，不复制数据（使用WHERE条件为假）
CREATE TABLE employees_structure AS
SELECT * FROM employees
WHERE 1 = 0;

-- 使用LIKE复制表结构（包括默认值、约束等）
CREATE TABLE employees_copy (LIKE employees INCLUDING ALL);

表约束

约束用于保证数据的完整性和一致性。PostgreSQL支持多种约束类型。

主键约束（PRIMARY KEY）

sql

-- 单列主键
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,         -- 主键自动创建唯一索引
    dept_name VARCHAR(50) NOT NULL
);

-- 复合主键（多列组成的主键）
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    -- 使用多列作为复合主键
    PRIMARY KEY (order_id, product_id)
);

-- 添加主键约束名称
CREATE TABLE categories (
    cat_id SERIAL,
    cat_name VARCHAR(50),
    -- 命名主键约束
    CONSTRAINT pk_categories PRIMARY KEY (cat_id)
);

外键约束（FOREIGN KEY）

sql

-- 创建订单表，引用用户表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2),
    
    -- 外键约束：user_id引用users表的id列
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE      -- 删除用户时级联删除订单
        ON UPDATE CASCADE      -- 更新用户ID时级联更新
);

-- 外键的各种引用动作
CREATE TABLE order_details (
    detail_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    
    -- CASCADE: 父表删除/更新时，子表相应记录也被删除/更新
    CONSTRAINT fk_detail_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE,
    
    -- SET NULL: 父表删除/更新时，子表相应字段设为NULL
    CONSTRAINT fk_detail_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE SET NULL,
    
    -- RESTRICT: 如果子表有引用，则禁止删除父表记录（默认行为）
    -- NO ACTION: 与RESTRICT类似，但可以延迟检查
    quantity INTEGER NOT NULL
);

唯一约束（UNIQUE）

sql

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,        -- 单列唯一约束
    email VARCHAR(100),
    
    -- 命名的唯一约束
    CONSTRAINT uk_accounts_email UNIQUE (email)
);

-- 多列组合唯一约束
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- user_id和role_id的组合必须唯一
    CONSTRAINT uk_user_role UNIQUE (user_id, role_id)
);

检查约束（CHECK）

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2),
    discount NUMERIC(5, 2) DEFAULT 0,
    stock INTEGER DEFAULT 0,
    
    -- 价格必须大于0
    CONSTRAINT chk_price CHECK (price > 0),
    
    -- 折扣必须在0到100之间
    CONSTRAINT chk_discount CHECK (discount >= 0 AND discount <= 100),
    
    -- 库存不能为负
    CONSTRAINT chk_stock CHECK (stock >= 0),
    
    -- 折扣后的价格必须大于0（多列检查约束）
    CONSTRAINT chk_final_price CHECK (price * (1 - discount/100) > 0)
);

非空约束（NOT NULL）

sql

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,         -- 必填字段
    email VARCHAR(100),                 -- 可选字段
    phone VARCHAR(20) NOT NULL,
    address TEXT                        -- 可选字段
);

排除约束（EXCLUDE）

PostgreSQL特有的高级约束，用于确保某些列的组合不满足特定条件：

sql

-- 需要先安装btree_gist扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 创建会议室预订表，确保同一会议室的时间不冲突
CREATE TABLE room_bookings (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    
    -- 排除约束：同一房间的时间段不能重叠
    -- && 是时间范围重叠运算符
    CONSTRAINT chk_no_overlap EXCLUDE USING GIST (
        room_id WITH =,                  -- room_id相等
        tsrange(start_time, end_time) WITH &&  -- 时间范围重叠
    )
);

修改表

使用 ALTER TABLE 语句修改已存在的表。

添加列

sql

-- 添加单个列
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

-- 添加多个列
ALTER TABLE users
ADD COLUMN address TEXT,
ADD COLUMN city VARCHAR(50),
ADD COLUMN postal_code VARCHAR(10);

-- 添加带约束的列
ALTER TABLE users
ADD COLUMN age INTEGER CHECK (age >= 0 AND age <= 150);

删除列

sql

-- 删除单个列
ALTER TABLE users
DROP COLUMN phone;

-- 删除多个列
ALTER TABLE users
DROP COLUMN address,
DROP COLUMN city;

-- 如果列存在则删除（避免列不存在时报错）
ALTER TABLE users
DROP COLUMN IF EXISTS fax;

修改列

sql

-- 修改列的数据类型
ALTER TABLE users
ALTER COLUMN age TYPE BIGINT;

-- 修改列类型并使用USING子句转换数据
ALTER TABLE users
ALTER COLUMN salary TYPE NUMERIC(10, 2)
USING salary::NUMERIC(10, 2);

-- 修改列的默认值
ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'active';

-- 删除列的默认值
ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;

-- 设置列为NOT NULL
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

-- 取消NOT NULL约束
ALTER TABLE users
ALTER COLUMN email DROP NOT NULL;

-- 重命名列
ALTER TABLE users
RENAME COLUMN username TO user_name;

添加约束

sql

-- 添加主键约束
ALTER TABLE products
ADD CONSTRAINT pk_products PRIMARY KEY (id);

-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加唯一约束
ALTER TABLE users
ADD CONSTRAINT uk_users_phone UNIQUE (phone);

-- 添加检查约束
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

删除约束

sql

-- 删除约束（需要知道约束名称）
ALTER TABLE users
DROP CONSTRAINT uk_users_phone;

-- 如果约束存在则删除
ALTER TABLE users
DROP CONSTRAINT IF EXISTS uk_users_phone;

-- 查看表的所有约束
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

重命名表

sql

-- 重命名表
ALTER TABLE users
RENAME TO app_users;

-- 重命名表（另一种语法）
RENAME TABLE old_name TO new_name;

设置Schema

sql

-- 将表移动到另一个Schema
ALTER TABLE users
SET SCHEMA admin;

删除表

基本删除

sql

-- 删除表
DROP TABLE users;

-- 如果表存在则删除（避免表不存在时报错）
DROP TABLE IF EXISTS users;

级联删除

sql

-- CASCADE: 同时删除依赖于该表的对象（如视图、外键引用等）
DROP TABLE users CASCADE;

-- RESTRICT: 如果有依赖对象则拒绝删除（默认行为）
DROP TABLE users RESTRICT;

临时表

临时表只在当前会话中存在，会话结束后自动删除。

sql

-- 创建临时表
CREATE TEMP TABLE temp_orders (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INTEGER
);

-- 从查询创建临时表
CREATE TEMP TABLE temp_high_value_orders AS
SELECT * FROM orders
WHERE total_amount > 10000;

-- 临时表在会话结束时自动删除
-- 也可以手动删除
DROP TABLE temp_orders;

继承表

PostgreSQL支持表继承，这是其独特功能之一。

sql

-- 创建父表
CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50),
    model VARCHAR(50),
    year INTEGER
);

-- 创建子表继承父表
CREATE TABLE cars (
    num_doors INTEGER,
    fuel_type VARCHAR(20)
) INHERITS (vehicles);

-- 创建另一个子表
CREATE TABLE motorcycles (
    engine_cc INTEGER,
    has_sidecar BOOLEAN DEFAULT FALSE
) INHERITS (vehicles);

-- 插入数据
INSERT INTO cars (brand, model, year, num_doors, fuel_type)
VALUES ('Toyota', 'Camry', 2023, 4, 'gasoline');

INSERT INTO motorcycles (brand, model, year, engine_cc)
VALUES ('Honda', 'CBR600RR', 2023, 599);

-- 查询父表会包含所有子表的数据
SELECT * FROM vehicles;

-- 只查询父表数据（不包含子表）
SELECT * FROM ONLY vehicles;

分区表

分区表将大表分成多个物理小表，提高查询性能。

范围分区（RANGE）

sql

-- 创建分区父表
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2),
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023_q1 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

CREATE TABLE sales_2023_q3 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_2023_q4 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

-- 创建默认分区（存储不匹配其他分区的数据）
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

列表分区（LIST）

sql

-- 按地区分区
CREATE TABLE orders_by_region (
    id SERIAL,
    order_date DATE,
    region VARCHAR(50),
    amount NUMERIC(10, 2)
) PARTITION BY LIST (region);

-- 创建各地区的分区
CREATE TABLE orders_north PARTITION OF orders_by_region
    FOR VALUES IN ('北京', '天津', '河北');

CREATE TABLE orders_south PARTITION OF orders_by_region
    FOR VALUES IN ('广东', '福建', '浙江');

CREATE TABLE orders_west PARTITION OF orders_by_region
    FOR VALUES IN ('四川', '重庆', '云南');

哈希分区（HASH）

sql

-- 按哈希值分区
CREATE TABLE users_hash (
    id SERIAL,
    username VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY HASH (id);

-- 创建分区（数据均匀分布到各分区）
CREATE TABLE users_hash_0 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_hash_1 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE users_hash_2 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE users_hash_3 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

查看表信息

查看表结构

sql

-- 使用\d命令查看表结构
\d users

-- 查看表的详细信息（包括索引、约束等）
\d+ users

-- 查看所有表
\dt

-- 查看所有表（包括系统表）
\dt+

-- 查看特定schema的表
\dt public.*

使用SQL查询表信息

sql

-- 查询表的列信息
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

-- 查询表的主键
SELECT 
    kcu.column_name,
    tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'users'
    AND tc.constraint_type = 'PRIMARY KEY';

-- 查询表的外键
SELECT 
    tc.constraint_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.table_name = 'orders'
    AND tc.constraint_type = 'FOREIGN KEY';

-- 查询表的大小
SELECT 
    pg_size_pretty(pg_total_relation_size('users')) AS total_size,
    pg_size_pretty(pg_relation_size('users')) AS table_size,
    pg_size_pretty(pg_total_relation_size('users') - pg_relation_size('users')) AS index_size;

表级权限管理

sql

-- 授予表的所有权限
GRANT ALL PRIVILEGES ON TABLE users TO user1;

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON TABLE users TO user1;

-- 授予列级权限
GRANT SELECT (id, username), UPDATE (email) ON TABLE users TO user1;

-- 撤销权限
REVOKE ALL PRIVILEGES ON TABLE users FROM user1;

-- 查看表的权限
\z users
-- 或
\dp users

小结

本章介绍了PostgreSQL表操作的主要内容：

操作类型	主要命令	说明
创建表	`CREATE TABLE`	定义表结构和约束
修改表	`ALTER TABLE`	添加/删除/修改列和约束
删除表	`DROP TABLE`	删除表及其数据
约束类型	PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, EXCLUDE	保证数据完整性
特殊表	临时表、分区表、继承表	满足特殊需求

掌握表操作是数据库开发的基础，下一章我们将学习数据的增删改查操作。

表操作 ​

创建表 ​

基本语法 ​

完整示例：创建员工表 ​

使用IF NOT EXISTS ​

从查询结果创建表 ​

表约束 ​

主键约束（PRIMARY KEY） ​

外键约束（FOREIGN KEY） ​

唯一约束（UNIQUE） ​

检查约束（CHECK） ​

非空约束（NOT NULL） ​

排除约束（EXCLUDE） ​

修改表 ​

添加列 ​

删除列 ​

修改列 ​

添加约束 ​

删除约束 ​

重命名表 ​

设置Schema ​

删除表 ​

基本删除 ​

级联删除 ​

临时表 ​

继承表 ​

分区表 ​

范围分区（RANGE） ​

列表分区（LIST） ​

哈希分区（HASH） ​

查看表信息 ​

查看表结构 ​

使用SQL查询表信息 ​

表级权限管理 ​

小结 ​

表操作

创建表