PostgreSQL常用操作（持续更新）_postgresql操作

PostgreSQL 13.1 手册

一、 管理操作

1.数据库对象Size查询

2.恢释放磁盘空间

VACUUM regclass; --释放具体的表（不会给操作系统，不会减少磁盘使用量）
VACUUM FULL regclass;–立即释放磁盘空间给操作系统（可能锁表）

3.数据库锁表

• 查找所有活动的被锁的表

select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (
select pid from pg_locks l
join pg_class t on l.relation = t.oid
and t.relkind = 'r');
1
2
3
4
5
6

• 查找锁表的pid

select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'lockedtable';
1

• 解锁

SELECT pg_cancel_backend(pid);
1

4.查询所有表字段信息

SELECT
    c.relname 表名称,
    a.attname AS 字段名称,
    col_description(a.attrelid,a.attnum) AS 注释,
    format_type ( a.atttypid, a.atttypmod ) AS 类型,
    CASE WHEN a.attnotnull='f' THEN '否' ELSE '是' END AS 是否必填,
    a.attnum 序号
FROM
    pg_class c
		left join pg_attribute a on a.attrelid = c.oid 
WHERE
    a.attnum > 0
    ORDER BY c.relname,a.attnum;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.根据主键名或约束名查询所在的表

SELECT conname AS constraint_name, pg_get_constraintdef(con.oid) AS constraint_definition, relname AS table_name
FROM pg_constraint con
JOIN pg_class cls ON (cls.oid = con.conrelid AND cls.relkind = 'r') -- 只选择关系类型为 "r"（表）的对象
WHERE conname = '<constraint_name>';
1
2
3
4

二、常用函数

1.字符串函数

• 多行转一行 string_agg(text,text)
  查询字段只能是字符型 结果用“，”隔开

SELECT a.id, a.db_name, string_agg(b.username, ',') FROM tbl_company a, tbl_user b WHERE a.id = b.company_id group by a.id;
1

• 一行拆分为多行 regexp_split_to_table(string, pattern)
• 查找子字符串的位置 position(substring in string)
  position(‘om’ in ‘Thomas’) ——> 3
• 连接字符串，忽略null concat_ws(pattern,string1,string2,......)

2.数据填充函数 generate_series(…)

例：

SELECT T::DATE 时间轴  FROM generate_series ( '2022-10-25' :: DATE, '2022-11-03' :: DATE, '1 days' ) AS T
1

3.加密|解密函数

• AES加密
  encrypt(‘’::bytea, key,‘aes’)::varchar
• AES解密
  convert_from(decrypt(‘’::bytea,key,‘aes’),‘SQL_ASCII’)

三、 常用查询

1.树形查询

WITH RECURSIVE a AS ( 
	select xx,1 as level from table1 where id = '123' 
	union all 
	select d.xx,a.level +1 from table1 d join a on d.pid = a.id
) select * from a
1
2
3
4
5

2.PARTITION 数据分组编号

select row_number() OVER(PARTITION by [分区字段] order by [排序字段]) 序号 from 表名
1

–示例，对成绩单中每个学生的成绩进行分组排序
select row_number() OVER(PARTITION by studentID order by core) 序号,name,core from schoolreport;

3.判断内容是否为数字

select 'Service' ~ '^[0-9\.]+$'
1

true-为数字

四、 表管理

1.创建带条件的唯一索引

注意：where 条件的字段是约束的一部分

create unique index UN_tablename on tablename(name, phone,enable) where enable = '0';
1

五、调试

• 存储过程/函数中打印

raise notice '**%**',param; --param为占位符所在的变量
1

六 、调优

使用 EXPLAIN ANALYZE ....待分析的SQL…查看执行时间及循环次数
再找出耗时较多的SQL进行修改