查询所有表名：

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