Querying metadata

You can query PostgreSQL system views and catalogs to retrieve metadata about database objects. These objects can be queried like regular tables to return information about tables, columns, constraints, and privileges.

Sample queries

List column-level privileges for a specific table

The following query returns all column-level privileges defined for the master table in your database.

SELECT
    table_catalog AS "TABLE_CAT",
    table_schema  AS "TABLE_SCHEM",
    table_name    AS "TABLE_NAME",
    column_name   AS "COLUMN_NAME",
    grantor       AS "GRANTOR",
    grantee       AS "GRANTEE",
    privilege_type AS "PRIVILEGE",
    is_grantable   AS "IS_GRANTABLE"
FROM information_schema.column_privileges
WHERE table_name = 'master'
ORDER BY
    "TABLE_CAT",
    "TABLE_SCHEM",
    "TABLE_NAME",
    "COLUMN_NAME",
    "PRIVILEGE";

List all objects in the public schema

The following query returns all objects (tables, views, indexes, sequences, and more) in the public schema, along with their object type:

SELECT
    n.nspname AS "TABLE_SCHEM",
    c.relname AS "TABLE_NAME",
    c.relkind AS "TABLE_TYPE"
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n
    ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
ORDER BY
    "TABLE_TYPE",
    "TABLE_SCHEM",
    "TABLE_NAME";