PostgreSQL 数据库的常用操作，包括数据库的创建、连接与删除，表的增删改查及索引优化，数据的插入更新删除查询，事务管理与视图创建，以及备份恢复策略。此外还涵盖了用户权限管理和 psql 命令行元命令的使用，提供了具体的 SQL 语句和 shell 命令示例，适用于数据库开发与管理场景。
-- 基础创建
CREATE DATABASE db_name;
-- 指定字符集、所有者、编码（推荐生产环境使用）
CREATE DATABASE db_name WITH OWNER = username
ENCODING = 'UTF8'
LC_COLLATE =
LC_CTYPE
TABLESPACE pg_default;
# 基础连接
psql -U username -d db_name -h host_ip -p port
# 示例：连接本地默认端口的 test_db 数据库
psql -U postgres -d test_db -h 127.0.0.1 -p 5432
# 本地默认配置（用户名 postgres，端口 5432）可简化
psql -d db_name
\c db_name; -- 等同于 MySQL 的 use db_name;
-- 基础删除（数据库不存在会报错）
DROP DATABASE db_name;
-- 安全删除（数据库不存在不报错）
DROP DATABASE IF EXISTS db_name;
-- 强制删除（断开所有连接后删除，需超级权限）
DROP DATABASE db_name WITH (FORCE);
CREATE TABLE user_info (
id SERIAL PRIMARY KEY, -- 自增主键（SERIAL 等价于 int4+ 自增序列）
username VARCHAR(50) NOT NULL UNIQUE, -- 非空唯一
age INT DEFAULT 0, -- 默认值 0
email VARCHAR(100),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 默认当前时间
update_time TIMESTAMP
);
-- 说明：PostgreSQL 10+ 推荐使用 IDENTITY 列替代 SERIAL（更标准）
CREATE TABLE user_info (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 其他字段同上
username VARCHAR(50) NOT NULL UNIQUE,
age INT DEFAULT 0,
email VARCHAR(100),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP
);
-- 1. 添加字段
ALTER TABLE user_info ADD COLUMN phone VARCHAR(20);
-- 2. 修改字段类型
ALTER TABLE user_info ALTER COLUMN email TYPE VARCHAR(150);
-- 3. 修改字段默认值
ALTER TABLE user_info ALTER COLUMN age SET DEFAULT 18;
-- 4. 删除字段
ALTER TABLE user_info DROP COLUMN IF EXISTS phone;
-- 5. 重命名字段
ALTER TABLE user_info RENAME COLUMN update_time TO modify_time;
-- 6. 重命名表
ALTER TABLE user_info RENAME TO user_info_new;
-- 基础删除
DROP TABLE user_info;
-- 安全删除
DROP TABLE IF EXISTS user_info;
-- 级联删除（删除表及依赖它的对象，如外键关联）
DROP TABLE IF EXISTS user_info CASCADE;
-- 方法 1：查看当前数据库所有表
\dt
-- 方法 2：查看指定表结构（详细字段信息）
\d user_info
-- 方法 3：SQL 查询表结构
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'user_info';
-- 普通索引
CREATE INDEX idx_user_username ON user_info(username);
-- 唯一索引（确保字段值唯一）
CREATE UNIQUE INDEX idx_user_email ON user_info(email);
-- 复合索引（多字段组合）
CREATE INDEX idx_user_age_create_time ON user_info(age, create_time);
-- 删除索引
DROP INDEX IF EXISTS idx_user_username;
-- 基础插入
INSERT INTO user_info (username, age, email)
VALUES ('zhangsan', 25, '[email protected]');
-- 批量插入
INSERT INTO user_info (username, age, email)
VALUES ('lisi', 28, '[email protected]'), ('wangwu', 30, '[email protected]');
-- 插入并返回结果（获取自增 ID 等）
INSERT INTO user_info (username, age)
VALUES ('zhaoliu', 22)
RETURNING id, username, create_time;
-- 基础更新
UPDATE user_info SET age = 26, email = '[email protected]'
WHERE username = 'zhangsan';
-- 安全更新（避免无 WHERE 条件更新全表）
UPDATE user_info SET modify_time = CURRENT_TIMESTAMP
WHERE id = 1;
-- 基础删除
DELETE FROM user_info WHERE username = 'zhaoliu';
-- 清空全表（不可回滚，谨慎使用）
TRUNCATE TABLE user_info;
-- 清空全表并重置自增序列（SERIAL/IDENTITY 列）
TRUNCATE TABLE user_info RESTART IDENTITY;
-- 基础查询
SELECT id, username, age FROM user_info;
-- 条件查询
SELECT * FROM user_info WHERE age > 25 AND email IS NOT NULL;
-- 排序查询
SELECT * FROM user_info ORDER BY age DESC, create_time ASC;
-- 分页查询（PostgreSQL 特有，比 LIMIT+OFFSET 更高效）
-- 第 1 页（每页 10 条）
SELECT * FROM user_info ORDER BY id LIMIT 10 OFFSET 0;
-- 第 2 页
SELECT * FROM user_info ORDER BY id LIMIT 10 OFFSET 10;
-- 聚合查询
SELECT COUNT(*) AS total_count, -- 总条数
AVG(age) AS avg_age, -- 平均年龄
MAX(age) AS max_age -- 最大年龄
FROM user_info;
-- 分组查询
SELECT age, COUNT(*) AS user_count
FROM user_info
GROUP BY age
HAVING COUNT(*) > 1; -- 分组后过滤
-- 开启事务
BEGIN;
-- 执行操作（多个 SQL 语句）
INSERT INTO user_info (username, age) VALUES ('test1', 18);
UPDATE user_info SET age = 19 WHERE username = 'test1';
-- 提交事务（确认修改）
COMMIT;
-- 回滚事务（放弃修改，回到 BEGIN 前状态）
-- ROLLBACK;
-- 创建视图
CREATE VIEW v_user_adult AS
SELECT id, username, email, create_time
FROM user_info
WHERE age >= 18;
-- 查询视图
SELECT * FROM v_user_adult;
-- 删除视图
DROP VIEW IF EXISTS v_user_adult;
# 基础备份（导出 SQL 文件）
pg_dump -U username -d db_name -h host -p port > backup.sql
# 压缩备份（推荐，节省空间）
pg_dump -U username -d db_name | gzip > backup.sql.gz
# 仅备份表结构（不包含数据）
pg_dump -U username -d db_name -s > backup_schema.sql
# 恢复 SQL 格式备份
psql -U username -d db_name -h host -p port < backup.sql
# 恢复压缩备份
gzip -d -c backup.sql.gz | psql -U username -d db_name
# 恢复自定义格式备份（pg_dump -F c 导出的文件）
pg_restore -U username -d db_name -h host -p port backup.dump
-- 基础创建
CREATE USER user1 WITH PASSWORD '123456';
-- 创建带权限的用户（允许创建数据库、角色）
CREATE USER user2 WITH PASSWORD '123456' CREATEDB CREATEROLE;
-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE db_name TO user1;
-- 授予表的查询/插入权限
GRANT SELECT, INSERT ON TABLE user_info TO user1;
-- 授予序列权限（自增列需要）
GRANT USAGE, SELECT ON SEQUENCE user_info_id_seq TO user1;
-- 收回权限
REVOKE INSERT ON TABLE user_info FROM user1;
-- 基础删除
DROP USER IF EXISTS user1;
-- 级联删除（用户拥有的对象一并删除）
DROP USER IF EXISTS user2 CASCADE;
| 元命令 | 功能说明 |
|---|---|
| \c db_name | 切换数据库 |
| \l | 查看所有数据库 |
| \dt | 查看当前数据库所有表 |
| \d table_name | 查看指定表结构 |
| \di | 查看所有索引 |
| \du | 查看所有用户 |
| \q | 退出 psql 终端 |
| \x | 切换横向 / 纵向显示查询结果 |
| \timing | 开启 / 关闭查询耗时统计 |
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL 转 CSV/JSON/XML在线工具，online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online
将 Markdown（GFM）转为 HTML 片段，浏览器内 marked 解析；与 HTML 转 Markdown 互为补充。 在线工具，Markdown 转 HTML在线工具，online