PG常用SQL，pgsql

PG常用SQL，pgsql

一、查看當前資料庫連接

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_queryFROM    (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 SWHERE   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