Advanced PostgreSQL Queries

All Postgres users, like it or not, will need to retrieve information about the tables, triggers, sequences etc that they have in their databases. The following are some of the things you might want to query:

1. What constraints does my table have?

select pgc.relname as "TableName", pc.conname as "ConstraintName",
contype as "ConstraintType", conkey as "KeyCols",
confkey as "ForeignCols", consrc as "Src"
from pg_class pgc, pg_constraint pc
where pgc.oid = pc.conrelid
and relname = '< tablename >';


2. How to get the column name of the primary key of a table?

select column_name from information_schema.key_column_usage where constraint_name IN
(
select c.conname as "ConstraintName"
from pg_class r, pg_constraint c
where r.oid = c.conrelid
and contype = 'p'
and relname = '< tablename >' );


3. What triggers are on my table?

select pc.relname as "Table", pt.tgname as "TriggerName",
pt.tgconstrname as "ConstraintName", pt.tgenabled as "Enabled",
pt.tgisconstraint as "IsConstraint", pcc.relname as "ReferencedTable",
pp.proname as "FunctionName"
from pg_trigger pt, pg_class pc, pg_class pcc, pg_proc pp
where pt.tgfoid = pp.oid and pt.tgrelid = pc.oid
and pt.tgconstrrelid = pcc.oid
and pc.relname = '< tablename >';


4. What indexes are on my table?

select * from pg_indexes where tablename = '< tablename >';


More Advanced PostgreSQL Queries can be found here:
http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/