-- Example #1: table/attribute names

    SELECT t.tablename, a.attnum, a.attname
    FROM pg_tables AS t
      JOIN pg_class AS c ON t.tablename = c.relname
      JOIN pg_attribute AS a ON c.oid = a.attrelid
    WHERE schemaname = 'public'
    ORDER BY t.tablename, a.attnum;


-- Example #2: optimizer statistics 

    SELECT * FROM pg_stats
    WHERE schemaname = 'public'
    ORDER BY tablename;


-- Example #3: what can be NOT NULL?

    -- generate queries that count null values
    SELECT
      'SELECT count(*) FROM ' || t.tablename
      || ' WHERE ' || A.attname || ' IS NULL;' AS sql
    FROM pg_tables AS t
      JOIN pg_class AS c ON t.tablename = c.relname
      JOIN pg_attribute AS a ON c.oid = a.attrelid
    WHERE schemaname = 'public'
      AND attnum > 0
    ORDER BY tablename, attnum;
