PG常用SQL

一、查看当前数据库连接

SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
       pg_stat_get_backend_activity(s.backendid) AS current_query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

二、查看当前正在运行sql

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>'
ORDER BY
   lap DESC;

三、查看指定表字段名

    SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull
    FROM pg_class as c,pg_attribute as a
    where c.relname = 'tablename' and a.attrelid = c.oid and a.attnum>0;

四、like多个模式

WHERE somecolumn ~~* any(array['%some%', '%someelse']));

五、删除连接

 select pg_terminate_backend(pid)
               from pg_stat_activity
              where pid == pid_number