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;

元数据相关

元数据是描述数据库、表、列、索引、用户、权限等系统信息的数据

PostgreSQL 所有元数据都以关系表（Relations）的形式存储在 pg_catalog 这个 schema 下（默认隐含可见，无需显式指定模式名），这些表是数据库实例启动时自动创建的核心系统表，按功能可分为以下类别：

核心系统表	存储内容
pg_database	数据库级元数据（数据库名、OID、所有者、编码、表空间等）
pg_namespace	模式（Schema）元数据（模式名、OID、所有者）
pg_class	表 / 索引 / 视图 / 序列等 “关系对象” 的核心元数据（OID、表名、所属模式、存储文件路径等）
pg_attribute	列级元数据（列名、所属表 OID、数据类型、是否非空、默认值、位置等）
pg_type	数据类型元数据（内置 / 自定义类型、类型名、存储长度、输入输出函数等）
pg_user/pg_roles	用户 / 角色元数据（用户名、密码哈希、权限标志、创建时间等）
pg_authid	角色权限核心表（比 pg_roles 更底层，存储权限标识、超级用户标志等）
pg_index	索引元数据（所属表 OID、索引列、是否唯一、主键标志等）
pg_constraint	约束元数据（主键、外键、唯一约束、检查约束等）
pg_tablespace	表空间元数据（表空间名、OID、所有者、物理存储路径）
pg_proc	函数 / 存储过程元数据（函数名、参数类型、返回类型、定义语句等）

information_schema

information_schema 是基于系统表的视图，屏蔽了 PostgreSQL 底层细节，推荐优先使用

information_schema 主要由视图（少量为系统函数）构成，核心分为 8 大类，覆盖数据库所有元数据维度：

分类	核心视图 / 对象	作用说明
1. 数据库 / 模式	`schemata`、`catalogs`（PostgreSQL 中 catalog 等价于数据库）	存储数据库、模式的元数据（名称、所有者、字符集等）
2. 表 / 视图	`tables`、`views`、`materialized_views`（PG 扩展）、`table_constraints`	存储表、视图、物化视图的元数据，及表级约束（主键、外键等）
3. 列	`columns`、`column_constraints`、`key_column_usage`	存储列的元数据（类型、是否非空、默认值），及列级约束（主键列、外键列等）
4. 权限	`table_privileges`、`column_privileges`、`usage_privileges`	存储用户对表 / 列 / 模式 / 类型等对象的权限（SELECT、INSERT、UPDATE 等）
5. 数据类型	`data_types`、`user_defined_types`、`domains`	存储内置 / 自定义数据类型、域（Domain）的元数据
6. 函数 / 存储过程	`routines`、`parameters`	存储函数 / 存储过程的元数据（名称、参数、返回类型、定义等）
7. 索引 / 统计信息	`statistics`、`indexes`（PG 扩展）	存储索引、统计信息的元数据（索引名、所属表、索引列等）
8. 其他对象	`sequences`、`triggers`、`foreign_data_wrappers`、`foreign_tables`	存储序列、触发器、外部数据包装器、外部表等特殊对象的元数据

常用查询

-- 查询所有用户定义的表（排除系统表）
SELECT n.nspname AS schema_name, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'  -- r=普通表，v=视图，i=索引
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- 查询指定表的列信息
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'your_table';

视图

information_schema.views 是 SQL 标准定义的视图元数据表，屏蔽了 PostgreSQL 底层细节，适合跨数据库（如 MySQL、Oracle）迁移的场景：

-- 标准信息模式查询视图
SELECT
    table_schema AS schema_name,      -- 视图所属模式
    table_name AS view_name,          -- 视图名称
    view_definition AS view_definition,  -- 视图定义SQL
    is_updatable AS is_updatable,     -- 是否可更新（YES/NO，仅可更新视图为YES）
    is_insertable_into AS is_insertable_into,  -- 是否可插入（YES/NO）
    is_trigger_updatable AS is_trigger_updatable,  -- 是否通过触发器更新
    is_trigger_deletable AS is_trigger_deletable   -- 是否通过触发器删除
FROM information_schema.views
-- 过滤系统视图，只查用户自定义视图
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_toast');

查询Postgresql自己的表：

-- 查询所有视图（含系统视图+用户自定义视图，可过滤）
SELECT
    n.nspname AS schema_name,          -- 视图所属模式名
    c.relname AS view_name,            -- 视图名称
    pg_get_userbyid(c.relowner) AS view_owner,  -- 视图所有者
    pg_get_viewdef(c.oid) AS view_definition,   -- 视图的完整定义SQL
    c.relcreated AS create_time,       -- 视图创建时间（PostgreSQL 12+ 支持）
    d.description AS view_comment,     -- 视图的注释（备注）
    c.relpersistence AS persistence,   -- 持久化类型（v=普通视图，t=临时视图）
    c.oid AS view_oid                  -- 视图的唯一OID（数据库内唯一）
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
WHERE c.relkind = 'v'  -- relkind='v' 表示视图（r=表，i=索引，m=物化视图）
  -- 可选：过滤系统模式，只查用户自定义视图
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast');

查询物化视图

-- 查询物化视图
SELECT schemaname, matviewname, definition FROM pg_matviews;

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

刷新页面返回顶部

vonlinee