A handy query for getting information about tables and columns in a postgresql database.

postgres-table-info.sql

  SELECT  col.attrelid::regclass as table,
          col.attname as name,
          format_type(col.atttypid, col.atttypmod) as type,
          col.attnotnull as not_null,
          def.adsrc as default,
          ( SELECT d.refobjid
            FROM pg_depend d
            WHERE col.attrelid = d.refobjid
            AND col.attnum = d.refobjsubid
            LIMIT 1
          ) IS NOT NULL as primary_key
  FROM pg_attribute as col
  LEFT JOIN pg_attrdef def
    ON col.attrelid = def.adrelid
    AND col.attnum = def.adnum
  JOIN pg_tables tbl
    ON col.attrelid::regclass = tbl.tablename::regclass
  WHERE tbl.schemaname = ANY (current_schemas(false))
    AND col.attnum > 0
  ORDER BY primary_key DESC