PG常用SQL语句

整理记录一些常用的sql语句

客户端连接相关

查看当前数据库中客户端连接

SELECT 
    pid,
    usename,
    datname,
    client_addr,
    client_port,
    state,
    query,
    backend_start
FROM 
    pg_stat_activity
ORDER BY 
    backend_start;

字段说明：

pid: 进程 ID。
usename: 连接的用户名。
datname: 数据库名称。
client_addr: 客户端的 IP 地址。
client_port: 客户端的端口。
state: 当前连接的状态（如 active, idle 等）。
query: 当前执行的 SQL 查询。
backend_start: 后端进程的启动时间。

序列

创建序列

CREATE SEQUENCE [IF NOT EXISTS] 序列名
    [INCREMENT [BY] 增量值]  -- 步长（正数递增，负数递减，默认1）
    [MINVALUE 最小值 | NO MINVALUE]  -- 最小值（默认1）
    [MAXVALUE 最大值 | NO MAXVALUE]  -- 最大值（默认bigint最大值）
    [START [WITH] 起始值]  -- 起始值（默认1）
    [CACHE 缓存大小]  -- 预生成的序列值数量（默认1，减少IO）
    [CYCLE | NO CYCLE]  -- 达到最大值后是否循环（默认不循环）
    [OWNED BY (表名.字段名 | NONE)];  -- 关联表字段（删除表时可自动删除序列）

例如

-- 创建一个从100开始、步长2、最大1000的序列，关联到users表的id字段
CREATE SEQUENCE IF NOT EXISTS users_id_seq
    INCREMENT BY 2
    START WITH 100
    MAXVALUE 1000
    CACHE 5
    OWNED BY users.id;

获取 / 修改序列当前值

SELECT currval('序列名');  -- 需先调用nextval，否则报错
SELECT last_value FROM 序列名;  -- 直接查询序列的最后一个值

# 修改当前值（重置序列）：
-- 方式1：设置序列的下一个值（下一次nextval会返回该值）
SELECT setval('序列名', 新值);  -- 例如：setval('users_id_seq', 200)

-- 方式2：同时设置是否为"已使用"（true表示下一次返回新值+1）
SELECT setval('序列名', 新值, 是否已使用);  -- 例如：setval('users_id_seq', 200, false)

-- 每次调用 nextval 会使序列值按步长递增，即使事务回滚，已生成的值也不会重复使用。
SELECT nextval('序列名');  -- 生成下一个值（自动递增）

修改序列属性

ALTER SEQUENCE [IF EXISTS] 序列名
    [INCREMENT [BY] 新增量值]
    [MINVALUE 新最小值 | NO MINVALUE]
    [MAXVALUE 新最大值 | NO MAXVALUE]
    [RESTART [WITH] 新起始值]  -- 重置序列，下一次nextval返回该值
    [CACHE 新缓存大小]
    [CYCLE | NO CYCLE]
    [OWNED BY (新表.新字段 | NONE)];  -- 重新关联或解除关联
    
-- 将序列步长改为3，最大10000
ALTER SEQUENCE users_id_seq
    INCREMENT BY 3
    MAXVALUE 10000;

删除序列

CASCADE：如果有依赖该序列的对象（如默认值），会一并删除。
RESTRICT：如果有依赖对象，拒绝删除（默认）。

DROP SEQUENCE [IF EXISTS] 序列名 [CASCADE | RESTRICT];

序列与自增字段关联

PostgreSQL 中 SERIAL 或 IDENTITY 类型会自动创建关联序列：

-- 创建表时自动生成序列（SERIAL 是自增整数的语法糖）
CREATE TABLE test (
    id SERIAL PRIMARY KEY,  -- 等价于 int4 + 关联序列 + 默认值nextval(序列)
    num BIGSERIAL  -- 等价于 int8 + 关联序列
);

-- PostgreSQL 10+ 推荐使用 IDENTITY（更标准）
CREATE TABLE test2 (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY  -- 强制使用序列，不允许手动插入
);

序列是数据库级别的对象，多个会话可同时调用 nextval，不会重复。
若序列关联到表字段（OWNED BY），删除表时会自动删除序列（需使用 DROP TABLE ... CASCADE）。
currval 依赖当前会话的 nextval 调用，不同会话的 currval 互不影响。

序列元数据

pg_sequences 是一个系统视图，专门用于展示序列的信息，包含序列名、所属模式、当前值、增量值等详细信息。

# 序列名和所属模式
SELECT schemaname, sequencename FROM pg_sequences;
-- 查看属性
SELECT * FROM pg_sequences WHERE sequencename = '序列名';

pg_class 是 PostgreSQL 中存储数据库对象（表、索引、序列等）元数据的核心表。序列在 pg_class 中的 relkind 字段值为 'S'，因此可以通过过滤该字段查询序列：

SELECT 
  n.nspname AS schemaname,  -- 序列所属模式
  c.relname AS sequencename  -- 序列名
FROM 
  pg_class c
JOIN 
  pg_namespace n ON c.relnamespace = n.oid
WHERE 
  c.relkind = 'S'  -- 只筛选序列类型
ORDER BY 
  n.nspname, c.relname;

说明：

序列通常与表的自增字段（如 SERIAL 或 IDENTITY 类型）关联，但也可以是独立创建的序列。
pg_sequences 视图更直观，包含序列的当前值（last_value）、最小值（min_value）、最大值（max_value）、增量（increment_by）等关键信息，适合快速查看序列详情。
pg_class 方法更底层，适合需要结合其他元数据过滤的场景。

查看创建序列的SQL语句

-- 查看创建语句（PostgreSQL 11+）
SELECT pg_get_create_script(oid) FROM pg_class WHERE relname = '序列名' AND relkind = 'S';

如果 pg_get_create_script 函数不存在，说明使用的 PostgreSQL 版本低于 11.0（该函数是在 PostgreSQL 11 中新增的）。对于低版本（<11），可以通过查询系统表手动手动拼接序列的创建语句，以下是具体方法：

SELECT 
  'CREATE SEQUENCE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || E'\n' ||
  '    INCREMENT BY ' || s.increment_by || E'\n' ||
  '    MINVALUE ' || CASE WHEN s.min_value = -9223372036854775808 THEN 'NO MINVALUE' ELSE s.min_value::text END || E'\n' ||
  '    MAXVALUE ' || CASE WHEN s.max_value = 9223372036854775807 THEN 'NO MAXVALUE' ELSE s.max_value::text END || E'\n' ||
  '    START WITH ' || s.start_value || E'\n' ||
  '    CACHE ' || s.cache_value || E'\n' ||
  CASE WHEN s.is_cycled THEN '    CYCLE' ELSE '    NO CYCLE' END || E'\n' ||
  '    OWNED BY ' || CASE WHEN s.owned_by != 0 THEN (SELECT quote_ident(n2.nspname) || '.' || quote_ident(c2.relname) || '.' || quote_ident(a.attname) 
                                                    FROM pg_class c2
                                                    JOIN pg_namespace n2 ON c2.relnamespace = n2.oid
                                                    JOIN pg_attribute a ON a.attrelid = c2.oid AND a.attnum = (s.owned_by >> 32)::int
                                                    WHERE c2.oid = (s.owned_by & 0xFFFFFFFF)::oid)
                         ELSE 'NONE' END || ';' AS create_sequence_sql
FROM 
  pg_class c
JOIN 
  pg_namespace n ON c.relnamespace = n.oid
JOIN 
  pg_sequences s ON s.schemaname = n.nspname AND s.sequencename = c.relname
WHERE 
  c.relkind = 'S'  -- 筛选序列类型
  AND c.relname = '你的序列名';  -- 替换为你的序列名

锁相关

查看当前数据库中所有活动的锁

SELECT 
    pg_locks.locktype,
    pg_locks.database,
    pg_locks.relation,
    pg_locks.page,
    pg_locks.tuple,
    pg_locks.virtualxid,
    pg_locks.transactionid,
    pg_locks.virtualtransaction,
    pg_locks.pid,
    pg_stat_activity.usename,
    pg_stat_activity.application_name,
    pg_stat_activity.state,
    pg_stat_activity.query
FROM 
    pg_locks
JOIN 
    pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
ORDER BY 
    pg_locks.pid;

查看哪些表被锁

SELECT 
    relation::regclass AS table_name,
    mode,
    count(*) AS count
FROM 
    pg_locks
WHERE 
    granted
GROUP BY 
    relation, mode
ORDER BY 
    table_name;

解锁

在 PostgreSQL 中，通常不能直接解锁，锁会在事务结束时自动释放。如果需要强制解锁，可以考虑终止持有锁的会话：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = <pid_of_locking_process>;

事务相关

查看数据库事务隔离级别

SELECT name, setting FROM pg_settings WHERE name ='default_transaction_isolation';
-- 或者
SELECT current_setting('default_transaction_isolation');

设置全局事务隔离级别，修改postgresql.conf文件中的default_transaction_isolation，或者通过下面的SQL设置：

-- 设置全局事务隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 设置当前会话事务隔离级别
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 或者
BEGIN ISOLATION LEVEL READ UNCOMMITTED READ WRITE;

权限相关

在 PostgreSQL 中，权限管理是通过 GRANT 和 REVOKE 命令来实现的。以下是一些常用的与权限相关的 SQL 语句示例：

1. 授予权限

授予用户对表的 SELECT 权限

GRANT SELECT ON table_name TO username;

授予用户对数据库的所有权限

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

授予用户对特定模式的使用权限

GRANT USAGE ON SCHEMA schema_name TO username;

2. 撤销权限

撤销用户对表的 SELECT 权限

REVOKE SELECT ON table_name FROM username;

撤销用户对数据库的所有权限

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

撤销用户对特定模式的使用权限

REVOKE USAGE ON SCHEMA schema_name FROM username;

3. 查看权限

查看某个表的权限

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'table_name';

查看用户的所有权限

SELECT *
FROM information_schema.role_table_grants
WHERE grantee = 'username';

4.创建角色并授予权限

创建新角色

CREATE ROLE role_name WITH LOGIN PASSWORD 'password';

授予角色对数据库的权限

GRANT ALL PRIVILEGES ON DATABASE database_name TO role_name;

5. 设置默认权限

设置模式内新对象的默认权限

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name
GRANT SELECT ON TABLES TO role_name;

posted @ 2025-08-04 19:58 vonlinee 阅读(17) 评论(0) 收藏举报

刷新页面返回顶部

vonlinee