查询所有表名：
select relname as tabname from pg_class where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%'；
查询所有触发器：
SELECT * FROM pg_trigger
查询所有触发器以及相关联的表：
select a.relname, b.tgname from pg_class a inner join pg_trigger b on a.oid = b.tgrelid;
查询当前连接
SELECT * FROM pg_stat_activity
清除连接
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='testdb' AND pid<>pg_backend_pid();
通过PID定位语句
SELECT
procpid,
START,
now() - START AS lap,
current_query
FROM
(
SELECT
backendid,
pg_stat_get_backend_pid ( S.backendid ) AS procpid,
pg_stat_get_backend_activity_start ( S.backendid ) AS START,
pg_stat_get_backend_activity ( S.backendid ) AS current_query
FROM
( SELECT pg_stat_get_backend_idset () AS backendid ) AS S
) AS S
WHERE
current_query <> '<IDLE>'
AND procpid = 4692
ORDER BY
lap DESC;
查询正在执行的sql
SELECT * FROM pg_stat_activity where state!='idle' order by xact_start
停止正在执行的sql
SELECT pg_cancel_backend(pid);
查看当前PG库所安装的插件名称
select name from pg_available_extensions;
查看锁
SELECT
locker.pid,
pc.relname,
locker.MODE,
locker_act.application_name,
LEAST ( query_start, xact_start ) start_time,
locker_act.STATE,
CASE
WHEN GRANTED = 'f' THEN
'wait_lock'
WHEN GRANTED = 't' THEN
'get_lock'
END lock_satus,
CURRENT_TIMESTAMP - LEAST ( query_start, xact_start ) AS runtime,
locker_act.query
FROM
pg_locks locker,
pg_stat_activity locker_act,
pg_class pc
WHERE
locker.pid = locker_act.pid
AND NOT locker.pid = pg_backend_pid ()
AND application_name <> 'pg_statsinfod'
AND locker.relation = pc.oid
AND pc.reltype <> 0 --and pc.relname='t'
ORDER BY
runtime DESC;
远程添加扩展
psql --host=192.168.0.51 --port=5432 --dbname=adgoods_2021 --username=root -c "select control_extension('create','pgcrypto');"
批量更新
update t_ticket set sendtime=to_timestamp(tmp.curTime) from (values (?,?) ,(?,?)) as tmp (curTime,jobid) where taskid=tmp.jobid
sql1.sql = "update t_ticket set sendtime=to_timestamp(tmp.curTime) from (values (?,?) ";
sql1.paras = new ArrayList<Object>();
sql1.paras.add(mapper.readTree(msgs.get(0)).path("curTime").longValue());
sql1.paras.add(mapper.readTree(msgs.get(0)).path("jobid").textValue());
for (int i = 1; i < msgs.size(); i++) {
msg = msgs.get(i);
sendJson = mapper.readTree(msg);
sql1.sql+=",(?,?)";
sql1.paras.add(sendJson.path("curTime").longValue());
sql1.paras.add(sendJson.get("jobid").textValue());
}
sql1.sql+=") as tmp (curTime,jobid) where taskid=tmp.jobid";
DBUtil.Update(dataSource, sql1);
计算时间差秒数
select extract(epoch FROM (now() - (now()-interval '1 day') ));
时区相关操作
查看当前时区
show timezone;
show time zone;
查看所有时区
select * from pg_timezone_names;
查看+8时区
select * from pg_timezone_names where utc_offset = '08:00:00';
设置时区
set time zone 'PRC';
以指定时区展示时间
select now() at time zone 'Asia/Shanghai';
修改时区
ALTER DATABASE mtp SET timezone TO 'Asia/Shanghai'
查看表的体积
select pg_size_pretty(pg_relation_size('test'));
查看表的状态
select * from pg_stat_user_tables where relname = 'test';
select * from pg_stat_all_tables where relname = 'test';
vacuum
select * from pg_stat_progress_vacuum
查看pg的参数配置
select * from pg_settings
查询表的列名（带备注）
SELECT
case when a.attnotnull then 1 else 0 end required,
format_type ( A.atttypid, A.atttypmod ) AS column_type,
A.attname AS COLUMN_NAME,
d.description AS column_comment
FROM
pg_attribute
A LEFT JOIN pg_description d ON d.objoid = A.attrelid
AND d.objsubid = A.attnum
LEFT JOIN pg_class C ON A.attrelid = C.oid
LEFT JOIN pg_type T ON A.atttypid = T.oid
WHERE
A.attnum > 0
AND C.relname = #{tableName}
AND relname IN ( SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'public' )
AND A.attname NOT IN ( 'id', 'is_del', 'create_by', 'create_date', 'update_by', 'update_date' )
查询所有表名（带备注）
select relname as table_name,obj_description(c.oid) as table_comment from pg_class c
where relname in (select table_name from information_schema.tables where table_schema = 'public')
