PostgreSQL common SQL, pgsql

PostgreSQL common SQL, pgsql

1. view the current database connection

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;

2. view the currently running 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;

3. view the field name of a specified table

    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;

4. Multiple like Modes

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

5. delete a connection

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