PostgreSQL常用操作（持续更新）

PostgreSQL 13.1 手册

一、 管理操作

1.数据库对象Size查询

名称	返回类型	描述
pg_total_relation_size(regclass)	bigint	指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据。
pg_table_size(regclass)	bigint	指定表OID或表名使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射）
pg_indexes_size(regclass)	bigint	关联指定表OID或表名的表索引的使用总磁盘空间
pg_database_size(name)	bigint	指定名称的数据库使用的磁盘空间
pg_size_pretty(bigint)	text	把字节计算的大小转换成一个人类易读的大小(使用KB 、MB 、GB 、TB)

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');

• 查找锁表的pid

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

• 解锁

SELECT pg_cancel_backend(pid);

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;

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.字符串函数

• 多行转一行 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;

• 一行拆分为多行 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

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

2.PARTITION 数据分组编号

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

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

3.判断内容是否为数字

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

true-为数字

四、 表管理

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

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

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

五、调试

• 存储过程/函数中打印

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

六 、调优

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