xingliuhua
文章 标签 分类
xingliuhua

目录

pgsql-04 表的创建与管理

xingliuhua 收录于 笔记
   约 2498 字   预计阅读 5 分钟 

04 - 表的创建与管理

1. 📖 概述

本章介绍PostgreSQL中表的创建、修改和管理,并与MySQL进行详细对比。

2. 🏗️ 创建表

2.1 基本语法

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- MySQL对比
-- CREATE TABLE users (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     username VARCHAR(50) NOT NULL UNIQUE,
--     email VARCHAR(100) NOT NULL UNIQUE,
--     password_hash VARCHAR(255) NOT NULL,
--     is_active TINYINT(1) DEFAULT 1,
--     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 IF NOT EXISTS

-- PostgreSQL & MySQL
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2)
);

2.3 从查询结果创建表

-- PostgreSQL
CREATE TABLE users_backup AS
SELECT * FROM users;

-- 仅复制结构,不复制数据
CREATE TABLE users_template AS
SELECT * FROM users WHERE 1=0;

-- MySQL类似
-- CREATE TABLE users_backup AS SELECT * FROM users;

2.4 创建临时表

-- PostgreSQL
CREATE TEMP TABLE temp_calculations (
    id SERIAL,
    result NUMERIC
);
-- 临时表在会话结束时自动删除

-- MySQL
-- CREATE TEMPORARY TABLE temp_calculations (
--     id INT AUTO_INCREMENT,
--     result DECIMAL(10,2)
-- );

2.5 指定表空间

-- PostgreSQL
CREATE TABLESPACE fast_space LOCATION '/ssd/postgresql/data';

CREATE TABLE important_data (
    id SERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE fast_space;

-- MySQL使用不同的存储引擎和表空间概念

3. 🔧 修改表结构

3.1 添加列

-- PostgreSQL
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加带默认值的列
ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0;

-- 添加非空列(必须有默认值或先添加为可空)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active' NOT NULL;

-- MySQL相同
-- ALTER TABLE users ADD COLUMN phone VARCHAR(20);

3.2 删除列

-- PostgreSQL
ALTER TABLE users DROP COLUMN phone;

-- 如果不存在不报错
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- 级联删除(删除依赖该列的对象)
ALTER TABLE users DROP COLUMN email CASCADE;

-- MySQL
-- ALTER TABLE users DROP COLUMN phone;

3.3 修改列

-- PostgreSQL: 修改列名
ALTER TABLE users RENAME COLUMN username TO user_name;

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

-- 使用USING转换数据
ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(10) USING age::VARCHAR;

-- 设置/删除默认值
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT FALSE;
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT;

-- 设置/删除NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- MySQL: 修改列(需要完整定义)
-- ALTER TABLE users CHANGE username user_name VARCHAR(50) NOT NULL;
-- ALTER TABLE users MODIFY COLUMN age BIGINT;

3.4 重命名表

-- PostgreSQL
ALTER TABLE users RENAME TO app_users;

-- MySQL
-- RENAME TABLE users TO app_users;
-- 或
-- ALTER TABLE users RENAME TO app_users;

3.5 添加/删除约束

-- PostgreSQL
-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE;

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

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

-- 删除约束
ALTER TABLE users DROP CONSTRAINT uk_email;

4. 📋 查看表信息

4.1 列出所有表

-- PostgreSQL
\dt                          -- psql命令
\dt+                         -- 详细信息

-- SQL查询
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- MySQL
-- SHOW TABLES;

4.2 查看表结构

-- PostgreSQL
\d users                     -- psql命令
\d+ users                    -- 详细信息

-- SQL查询
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users';

-- MySQL
-- DESC users;
-- 或
-- DESCRIBE users;
-- 或
-- SHOW COLUMNS FROM users;

4.3 查看建表语句

-- PostgreSQL: 没有直接的SHOW CREATE TABLE
-- 需要使用pg_dump
-- pg_dump -t users --schema-only dbname

-- 或查询系统表
SELECT pg_get_tabledef('users');  -- 需要自定义函数

-- MySQL
-- SHOW CREATE TABLE users;

4.4 查看表大小

-- PostgreSQL
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_indexes_size('users')) AS indexes_size;

-- 查看所有表大小
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- MySQL
-- SELECT
--     table_name,
--     ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
-- FROM information_schema.tables
-- WHERE table_schema = 'mydb';

5. 🗑️ 删除表

-- PostgreSQL
DROP TABLE users;

-- 如果不存在不报错
DROP TABLE IF EXISTS users;

-- 级联删除(删除依赖对象)
DROP TABLE users CASCADE;

-- 一次删除多个表
DROP TABLE users, products, orders;

-- MySQL相同
-- DROP TABLE IF EXISTS users;

5.1 TRUNCATE vs DELETE

-- TRUNCATE: 快速清空表
TRUNCATE TABLE users;
TRUNCATE TABLE users RESTART IDENTITY;  -- 重置序列
TRUNCATE TABLE users CASCADE;            -- 级联清空

-- DELETE: 逐行删除
DELETE FROM users;

-- 对比
-- TRUNCATE: 快,不记录单行日志,重置自增,不触发触发器
-- DELETE: 慢,记录日志,可回滚,触发触发器

6. 🔄 表继承(PostgreSQL独有)

-- PostgreSQL: 表继承
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC(10, 2)
);

CREATE TABLE managers (
    department VARCHAR(100)
) INHERITS (employees);

CREATE TABLE developers (
    programming_language VARCHAR(50)
) INHERITS (employees);

-- 插入数据
INSERT INTO employees (name, salary) VALUES ('Alice', 50000);
INSERT INTO managers (name, salary, department) VALUES ('Bob', 80000, 'Sales');
INSERT INTO developers (name, salary, programming_language) VALUES ('Charlie', 70000, 'Python');

-- 查询所有员工(包括子表)
SELECT * FROM employees;

-- 仅查询employees表
SELECT * FROM ONLY employees;

-- MySQL没有表继承功能

7. 📊 分区表

7.1 PostgreSQL分区表(10+版本)

-- 范围分区
CREATE TABLE orders (
    id SERIAL,
    order_date DATE NOT NULL,
    amount NUMERIC(10, 2),
    customer_id INTEGER
) PARTITION BY RANGE (order_date);

-- 创建分区
CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- 默认分区
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 插入数据自动路由到对应分区
INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2024-06-15', 99.99, 1);

7.2 列表分区

-- PostgreSQL
CREATE TABLE sales (
    id SERIAL,
    region VARCHAR(50),
    amount NUMERIC(10, 2)
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales
    FOR VALUES IN ('US', 'Canada');

CREATE TABLE sales_europe PARTITION OF sales
    FOR VALUES IN ('UK', 'Germany', 'France');

CREATE TABLE sales_asia PARTITION OF sales
    FOR VALUES IN ('China', 'Japan', 'India');

7.3 哈希分区

-- PostgreSQL 11+
CREATE TABLE users_partitioned (
    id SERIAL,
    username VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users_partitioned
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_p1 PARTITION OF users_partitioned
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE users_p2 PARTITION OF users_partitioned
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE users_p3 PARTITION OF users_partitioned
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

7.4 MySQL分区表(8.0+)

-- MySQL 8.0+ 范围分区
-- CREATE TABLE orders (
--     id INT AUTO_INCREMENT,
--     order_date DATE NOT NULL,
--     amount DECIMAL(10, 2),
--     PRIMARY KEY (id, order_date)
-- )
-- PARTITION BY RANGE (YEAR(order_date)) (
--     PARTITION p2023 VALUES LESS THAN (2024),
--     PARTITION p2024 VALUES LESS THAN (2025),
--     PARTITION p2025 VALUES LESS THAN (2026)
-- );

8. 🔐 表级权限

-- PostgreSQL
-- 授予所有权限
GRANT ALL PRIVILEGES ON TABLE users TO alice;

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

-- 授予所有表权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

-- 撤销权限
REVOKE INSERT, UPDATE ON TABLE users FROM developer;

-- 查看权限
\dp users

-- MySQL
-- GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'alice'@'localhost';
-- REVOKE INSERT, UPDATE ON mydb.users FROM 'alice'@'localhost';

9. 📝 注释

-- PostgreSQL
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.email IS '用户邮箱地址';

-- 查看注释
\d+ users

-- MySQL
-- ALTER TABLE users COMMENT = '用户表';
-- ALTER TABLE users MODIFY COLUMN email VARCHAR(100) COMMENT '用户邮箱地址';

10. 🎯 最佳实践

10.1 1. 命名规范

-- 好的命名
CREATE TABLE user_profiles (        -- 表名:复数,小写,下划线
    id SERIAL PRIMARY KEY,           -- 主键:id
    user_id INTEGER,                 -- 外键:表名_id
    created_at TIMESTAMPTZ,          -- 时间戳:_at结尾
    is_active BOOLEAN                -- 布尔:is_/has_前缀
);

-- 避免
CREATE TABLE UserProfile (           -- 避免驼峰命名
    ID SERIAL PRIMARY KEY,           -- 避免大写
    userId INTEGER,                  -- 避免驼峰
    createdOn TIMESTAMPTZ            -- 不一致的命名
);

10.2 2. 主键策略

-- 推荐:自增整数主键
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL
);

-- 或UUID主键(分布式系统)
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER
);

-- 复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

10.3 3. 默认值和时间戳

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 自动更新updated_at(需要触发器)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

10.4 4. 软删除

-- 使用deleted_at实现软删除
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(300),
    content TEXT,
    deleted_at TIMESTAMPTZ DEFAULT NULL
);

-- 查询未删除的记录
SELECT * FROM articles WHERE deleted_at IS NULL;

-- 软删除
UPDATE articles SET deleted_at = NOW() WHERE id = 1;

11. 📋 实战示例

11.1 电商系统表设计

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 分类表
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id),
    level INTEGER DEFAULT 1
);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INTEGER REFERENCES users(id),
    total_amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 订单明细表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(10, 2) NOT NULL,
    subtotal NUMERIC(12, 2) NOT NULL
);

-- 创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_products_category_id ON products(category_id);

12. 📚 下一步

关键要点:

  • 使用SERIAL作为自增主键
  • 为时间戳字段使用TIMESTAMPTZ
  • 合理使用外键约束
  • PostgreSQL的表继承和分区是强大特性
  • 始终为表和列添加注释
更新于 2026-03-16
 pgsql
返回 | 主页