SQL

Aggregate queries

Use aggregate functions like count, min, max, sum, avg.

Kick out all sessions from a database

SELECT pg_terminate_backend(pid) FROM pg_stat_get_activity(NULL::integer) WHERE datid = (SELECT oid FROM pg_database WHERE datname = 'tier_4');

Clone a live database

Note: all the users must be kicked off for this to work.

CREATE DATABASE tier_4_copy WITH TEMPLATE tier_4;

Get the number of rows in all tables in a database

SELECT sum(a.reltuples), a.schemaname
  from (
    SELECT
      nspname AS schemaname,relname,reltuples
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE
      nspname NOT IN ('pg_catalog', 'information_schema') AND
      relkind='r'
    ORDER BY reltuples DESC
  ) a
  group by schemaname

Export the schema for a table

This query basically lets you do a pg_dump without shell access, i.e. pg_dump -t 'schema-name.table-name' --schema-only database-name

Source: https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr

SELECT
  'CREATE TABLE ' || relname || E'\n(\n' ||
  array_to_string(
    array_agg(
      '    ' || column_name || ' ' ||  type || ' '|| not_null
    )
    , E',\n'
  ) || E'\n);\n'
from
(
  SELECT
    c.relname, a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
    case
      when a.attnotnull
    then 'NOT NULL'
    else 'NULL'
    END as not_null
  FROM pg_class c,
   pg_attribute a,
   pg_type t
   WHERE c.relname = 'pbm_member_mapping'
   AND a.attnum > 0
   AND a.attrelid = c.oid
   AND a.atttypid = t.oid
 ORDER BY a.attnum
) as tabledefinition
group by relname;