-- Find unique index not null (that is also definition of primary key, so you
-- will automacially get primary keys also) for table.
-- For better solution see
-- http://archives.postgresql.org/pgsql-patches/2003-07/msg00025.php
-- but it's still not in PostgreSQL 7.3.

SELECT c.relname as table,a.attname as index_col,c2.relname as index_name,
	pg_catalog.pg_get_indexdef(i.indexrelid) as definition
	,c2.oid
FROM pg_catalog.pg_class c,
	pg_catalog.pg_class c2,
	pg_catalog.pg_index i,
	pg_catalog.pg_attribute a
WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid
	and c.oid = a.attrelid AND a.attnum > 0 AND NOT a.attisdropped
	and pg_catalog.pg_get_indexdef(i.indexrelid) like '% ('||attname||')'
	and c.relname not like 'pg_%'
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;

