PostgreSQL 常用SQL操作命令

PostgreSQL 常用SQL操作命令

一、数据库操作

-- 创建数据库（指定编码）
CREATE DATABASE mydb 
  ENCODING 'UTF8' 
  LC_COLLATE 'en_US.utf8' 
  LC_CTYPE 'en_US.utf8';

-- 切换数据库
\c mydb

-- 删除数据库（需断开连接）
DROP DATABASE IF EXISTS mydb WITH (FORCE);

二、表操作

2.1 基础表管理

-- 创建表（含约束）
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  salary NUMERIC(10,2) CHECK (salary > 0),
  department_id INT REFERENCES departments(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 修改表结构
ALTER TABLE employees
  ADD COLUMN email VARCHAR(100) UNIQUE,
  ALTER COLUMN name TYPE VARCHAR(80);

2.2 索引管理

-- 创建复合索引
CREATE INDEX idx_emp_dept_salary 
  ON employees(department_id, salary DESC);

-- 创建表达式索引
CREATE INDEX idx_emp_lower_name 
  ON employees(LOWER(name));

-- 查看索引信息
\di+

三、数据操作

3.1 CRUD操作

-- 批量插入（RETURNING返回结果）
INSERT INTO employees (name, salary) 
VALUES 
  ('Alice', 65000),
  ('Bob', 72000)
RETURNING id, created_at;

-- 更新带条件
UPDATE employees 
SET salary = salary * 1.05 
WHERE department_id = 2 
  AND salary < 80000;

-- 删除软删除数据
DELETE FROM logs 
WHERE status = 'archived' 
  AND created_at < NOW() - INTERVAL '2 years';

3.2 高级查询

-- CTE递归查询
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 100
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN org_tree o ON o.id = e.manager_id
)
SELECT * FROM org_tree;

-- 窗口函数使用
SELECT 
  name,
  salary,
  department_id,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) 
FROM employees;

四、事务控制

BEGIN;
  SAVEPOINT before_update;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  -- 模拟错误检测
  -- ROLLBACK TO before_update;
COMMIT;

五、JSON操作

-- 创建JSON字段表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  details JSONB NOT NULL
);

-- JSON路径查询
SELECT 
  details->>'name' AS product_name,
  details->'specs'->'weight' AS weight 
FROM products
WHERE details @> '{"category": "electronics"}';

六、维护命令

-- 分析表统计信息
ANALYZE VERBOSE employees;

-- 重建索引
REINDEX INDEX CONCURRENTLY idx_emp_name;

-- 查看锁信息
SELECT * FROM pg_locks 
WHERE relation = 'employees'::regclass;

-- 备份指定表
pg_dump -t employees mydb > employees_backup.sql

七、权限管理

-- 创建角色
CREATE ROLE analyst WITH
  LOGIN
  PASSWORD 'secure123'
  VALID UNTIL '2025-12-31';

-- 授权表访问
GRANT SELECT, INSERT ON employees TO analyst;

-- 查看权限
\dp employees

八、性能优化

-- 查看查询计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees WHERE salary > 50000;

-- 设置工作内存
SET work_mem = '64MB';

-- 强制使用索引
SET enable_seqscan = off;