PostgreSQL 表创建、修改与删除

核心概念

PostgreSQL 表创建、修改与删除是数据库管理的核心操作，涉及以下核心概念：

表结构：表的列定义、数据类型、约束条件和索引
约束条件：确保数据完整性的规则，如主键、外键、唯一约束、检查约束等
数据类型：PostgreSQL支持的各种数据类型，如数值型、字符串型、日期时间型、JSON型等
表空间：表存储的位置，可以为不同的表指定不同的表空间
权限控制：表级别的权限管理，控制用户对表的访问权限

表创建

1. 基本创建语法

sql

-- 创建基本表
CREATE TABLE table_name (
    column1 data_type constraint,
    column2 data_type constraint,
    ...
    columnN data_type constraint
);

-- 创建带约束的表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,                  -- 主键约束，自增序列
    name VARCHAR(100) NOT NULL,            -- 非空约束
    email VARCHAR(100) UNIQUE NOT NULL,    -- 唯一约束+非空约束
    department_id INTEGER REFERENCES departments(id), -- 外键约束
    hire_date DATE DEFAULT CURRENT_DATE,   -- 默认值约束
    salary NUMERIC(10, 2) CHECK (salary > 0) -- 检查约束
);

2. 常用创建示例

sql

-- 创建带注释的表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    category_id INTEGER,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 为表和列添加注释
COMMENT ON TABLE products IS '产品信息表';
COMMENT ON COLUMN products.product_id IS '产品ID';
COMMENT ON COLUMN products.product_name IS '产品名称';

-- 创建临时表
CREATE TEMPORARY TABLE temp_orders (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER
);

-- 创建分区表
CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE,
    amount NUMERIC(10, 2)
) PARTITION BY RANGE (sale_date);

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

3. 表创建的最佳实践

sql

-- 使用适当的数据类型
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 使用UUID作为主键
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP WITH TIME ZONE
);

-- 为频繁查询的列创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

表修改

1. 修改表结构

sql

-- 添加新列
ALTER TABLE table_name ADD COLUMN new_column data_type constraint;

-- 修改列的数据类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

-- 修改列的默认值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

-- 删除列的默认值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

-- 将列设置为NOT NULL
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

-- 允许列为空
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 重命名列
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

-- 重命名表
ALTER TABLE table_name RENAME TO new_table_name;

2. 修改约束条件

sql

-- 添加主键约束
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 添加唯一约束
ALTER TABLE table_name ADD UNIQUE (column_name);

-- 添加外键约束
ALTER TABLE child_table ADD FOREIGN KEY (column_name) REFERENCES parent_table(parent_column);

-- 添加检查约束
ALTER TABLE table_name ADD CHECK (condition);

-- 删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

-- 重命名约束
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_constraint_name;

3. 修改表空间

sql

-- 修改表的表空间
ALTER TABLE table_name SET TABLESPACE new_tablespace;

-- 修改索引的表空间
ALTER INDEX index_name SET TABLESPACE new_tablespace;

4. 常用修改示例

sql

-- 示例：修改employees表
-- 1. 添加新列
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- 2. 修改列数据类型
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);

-- 3. 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

-- 4. 删除约束
ALTER TABLE employees DROP CONSTRAINT unique_email;

-- 5. 重命名列
ALTER TABLE employees RENAME COLUMN phone TO contact_phone;

-- 6. 添加检查约束
ALTER TABLE employees ADD CHECK (salary >= 0);

表删除

1. 基本删除语法

sql

-- 删除表
DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

-- 删除多个表
DROP TABLE [IF EXISTS] table1, table2, ...;

2. 删除选项说明

IF EXISTS：如果表不存在，不会报错
CASCADE：级联删除依赖该表的对象（如视图、外键约束等）
RESTRICT：如果有对象依赖该表，则拒绝删除（默认行为）

3. 常用删除示例

sql

-- 删除表（如果存在）
DROP TABLE IF EXISTS temp_table;

-- 级联删除表及其依赖对象
DROP TABLE IF EXISTS old_table CASCADE;

-- 删除多个表
DROP TABLE IF EXISTS table1, table2, table3;

4. 删除表的注意事项

sql

-- 删除表前备份数据
CREATE TABLE backup_table AS SELECT * FROM original_table;

-- 或者使用pg_dump备份
-- pg_dump -t original_table dbname > backup.sql

-- 删除表
DROP TABLE original_table;

最佳实践

1. 表设计最佳实践

合理设计表结构：
- 根据业务需求设计合理的表结构
- 避免过度设计，遵循第三范式
- 为表和列添加清晰的注释
选择合适的数据类型：
- 选择最适合数据的类型，避免使用过大的数据类型
- 对于固定长度的字符串使用CHAR，可变长度使用VARCHAR
- 对于日期时间使用DATE、TIME或TIMESTAMP类型
- 对于JSON数据使用JSONB类型，支持索引
添加适当的约束：
- 为每个表添加主键
- 为唯一值添加唯一约束
- 为外键关系添加外键约束
- 为数据完整性添加检查约束
合理使用索引：
- 为频繁查询的列创建索引
- 为外键列创建索引
- 避免为频繁更新的列创建过多索引
- 考虑使用部分索引和表达式索引

2. 表修改最佳实践

谨慎修改生产表：
- 在修改生产表前，确保已备份数据
- 尽量在低峰期进行表结构修改
- 对于大表，考虑使用在线DDL工具或分阶段修改
注意修改的影响：
- 某些修改（如修改列数据类型）可能会锁定表
- 修改列名会影响依赖该列的应用程序
- 删除列会永久丢失数据
使用事务：
- 对于复杂的表修改，使用事务确保原子性
- 可以在事务中回滚修改

3. 表删除最佳实践

确认删除的表：
- 仔细确认要删除的表名，避免误删
- 使用IF EXISTS子句避免因表不存在导致的错误
备份重要数据：
- 在删除表前，确保已备份重要数据
- 考虑使用DROP TABLE ... CASCADE时，要了解级联删除的对象
记录删除操作：
- 记录所有表删除操作，包括时间、原因和执行人
- 对于重要表，考虑先重命名为临时表，观察一段时间后再删除

常见问题（FAQ）

Q1：如何创建带有自增主键的表？

解决方案：

在PostgreSQL中，可以使用SERIAL或IDENTITY类型创建自增主键：

sql

-- 方法1：使用SERIAL类型（兼容旧版本）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 方法2：使用IDENTITY类型（PostgreSQL 10+推荐）
CREATE TABLE products (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 方法3：使用UUID作为主键
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL
);

Q2：如何修改表的主键？

解决方案：

修改表的主键需要先删除旧主键，然后添加新主键：

sql

-- 1. 删除旧主键
ALTER TABLE table_name DROP CONSTRAINT table_name_pkey;

-- 2. 添加新主键
ALTER TABLE table_name ADD PRIMARY KEY (new_column);

-- 示例：将users表的主键从id改为email
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ADD PRIMARY KEY (email);

Q3：如何安全地删除一个被其他表引用的表？

解决方案：

可以使用CASCADE选项级联删除依赖该表的对象，或者先删除所有依赖关系：

sql

-- 方法1：使用CASCADE级联删除
DROP TABLE IF EXISTS parent_table CASCADE;

-- 方法2：先删除外键约束，再删除表
-- 查看依赖关系
SELECT conname, conrelid::regclass FROM pg_constraint WHERE confrelid = 'parent_table'::regclass;

-- 删除所有外键约束
ALTER TABLE child_table DROP CONSTRAINT fk_child_parent;

-- 然后删除表
DROP TABLE parent_table;

Q4：如何向现有表添加外键约束？

解决方案：

使用ALTER TABLE语句添加外键约束：

sql

-- 示例：为orders表添加外键约束，关联到users表的id列
ALTER TABLE orders ADD CONSTRAINT fk_orders_users 
FOREIGN KEY (user_id) REFERENCES users(id) 
ON DELETE CASCADE 
ON UPDATE CASCADE;

-- 外键约束选项说明：
-- ON DELETE CASCADE：当父表记录被删除时，子表相关记录也被删除
-- ON DELETE SET NULL：当父表记录被删除时，子表外键列设为NULL
-- ON UPDATE CASCADE：当父表主键更新时，子表外键也更新
-- ON UPDATE SET NULL：当父表主键更新时，子表外键列设为NULL

Q5：如何查看表的结构？

解决方案：

使用以下命令查看表的结构：

sql

-- 方法1：使用psql命令
\d table_name;

-- 方法2：使用SQL查询
SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default,
    character_maximum_length
FROM information_schema.columns 
WHERE table_name = 'table_name';

-- 方法3：查看表的约束
SELECT 
    conname, 
    contype, 
    conkey, 
    confrelid::regclass
FROM pg_constraint 
WHERE conrelid = 'table_name'::regclass;

PostgreSQL 表创建、修改与删除 ​

核心概念 ​

表创建 ​

1. 基本创建语法 ​

2. 常用创建示例 ​

3. 表创建的最佳实践 ​

表修改 ​

1. 修改表结构 ​

2. 修改约束条件 ​

3. 修改表空间 ​

4. 常用修改示例 ​

表删除 ​

1. 基本删除语法 ​

2. 删除选项说明 ​

3. 常用删除示例 ​

4. 删除表的注意事项 ​

最佳实践 ​

1. 表设计最佳实践 ​

2. 表修改最佳实践 ​

3. 表删除最佳实践 ​

常见问题（FAQ） ​

Q1：如何创建带有自增主键的表？ ​

Q2：如何修改表的主键？ ​

Q3：如何安全地删除一个被其他表引用的表？ ​

Q4：如何向现有表添加外键约束？ ​

Q5：如何查看表的结构？ ​

PostgreSQL 表创建、修改与删除

核心概念

表创建

1. 基本创建语法

2. 常用创建示例

3. 表创建的最佳实践

表修改

1. 修改表结构

2. 修改约束条件

3. 修改表空间

4. 常用修改示例

表删除

1. 基本删除语法

2. 删除选项说明

3. 常用删除示例

4. 删除表的注意事项

最佳实践

1. 表设计最佳实践

2. 表修改最佳实践

3. 表删除最佳实践

常见问题（FAQ）

Q1：如何创建带有自增主键的表？

Q2：如何修改表的主键？

Q3：如何安全地删除一个被其他表引用的表？

Q4：如何向现有表添加外键约束？

Q5：如何查看表的结构？