Examples for using the system catalogs

The system catalogs are regular tables where PostgreSQL stores schema metadata, such as information about tables and columns, and internal bookkeeping information. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. Normally, one should not change the system catalogs by hand: there are SQL commands to make all supported changes. For example, CREATE DATABASE inserts a row into the pg_database catalog — and actually creates the database on disk.

It this section we want to show examples for how to parse some of the system catalogs, making queries with the classic PyGreSQL interface.

We assume that you have already created a connection to the PostgreSQL database, as explained in the Basic examples:

>>> from pg import DB
>>> db = DB()
>>> query = db.query

Lists indices

This query lists all simple indices in the database:

print(query("""SELECT bc.relname AS class_name,
        ic.relname AS index_name, a.attname
    FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
    WHERE i.indrelid = bc.oid AND i.indexrelid = ic.oid
        AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid
        AND NOT a.attisdropped AND a.attnum>0
    ORDER BY class_name, index_name, attname"""))

List user defined attributes

This query lists all user-defined attributes and their types in user-defined tables:

print(query("""SELECT c.relname, a.attname,
        format_type(a.atttypid, a.atttypmod)
    FROM pg_class c, pg_attribute a
    WHERE c.relkind = 'r' AND c.relnamespace!=ALL(ARRAY[
        'pg_catalog','pg_toast', 'information_schema']::regnamespace[])
        AND a.attnum > 0
        AND a.attrelid = c.oid
        AND NOT a.attisdropped
    ORDER BY relname, attname"""))

List user defined base types

This query lists all user defined base types:

print(query("""SELECT r.rolname, t.typname
    FROM pg_type t, pg_authid r
    WHERE r.oid = t.typowner
        AND t.typrelid = '0'::oid and t.typelem = '0'::oid
        AND r.rolname != 'postgres'
    ORDER BY rolname, typname"""))

List operators

This query lists all right-unary operators:

print(query("""SELECT o.oprname AS right_unary,
        lt.typname AS operand, result.typname AS return_type
    FROM pg_operator o, pg_type lt, pg_type result
    WHERE o.oprkind='r' and o.oprleft = lt.oid
        AND o.oprresult = result.oid
    ORDER BY operand"""))

This query lists all left-unary operators:

print(query("""SELECT o.oprname AS left_unary,
        rt.typname AS operand, result.typname AS return_type
    FROM pg_operator o, pg_type rt, pg_type result
    WHERE o.oprkind='l' AND o.oprright = rt.oid
        AND o.oprresult = result.oid
    ORDER BY operand"""))

And this one lists all of the binary operators:

print(query("""SELECT o.oprname AS binary_op,
        rt.typname AS right_opr, lt.typname AS left_opr,
        result.typname AS return_type
    FROM pg_operator o, pg_type rt, pg_type lt, pg_type result
    WHERE o.oprkind = 'b' AND o.oprright = rt.oid
        AND o.oprleft = lt.oid AND o.oprresult = result.oid"""))

List functions of a language

Given a programming language, this query returns the name, args and return type from all functions of a language:

language = 'sql'
print(query("""SELECT p.proname, p.pronargs, t.typname
    FROM pg_proc p, pg_language l, pg_type t
    WHERE p.prolang = l.oid AND p.prorettype = t.oid
        AND l.lanname = $1
    ORDER BY proname""", (language,)))

List aggregate functions

This query lists all of the aggregate functions and the type to which they can be applied:

print(query("""SELECT p.proname, t.typname
    FROM pg_aggregate a, pg_proc p, pg_type t
    WHERE a.aggfnoid = p.oid
        and p.proargtypes[0] = t.oid
    ORDER BY proname, typname"""))

List operator families

The following query lists all defined operator families and all the operators included in each family:

print(query("""SELECT am.amname, opf.opfname, amop.amopopr::regoperator
    FROM pg_am am, pg_opfamily opf, pg_amop amop
    WHERE opf.opfmethod = am.oid
        AND amop.amopfamily = opf.oid
    ORDER BY amname, opfname, amopopr"""))