Re: Query that produces index information for a Table

On 10 May 2010, at 6:02, Boyd, Craig wrote:

> I have been using PostgreSQL for a short while, but I have not had to use the pg_catalog tables before and the columns are a little cryptic to me. I think it ties to pg_class, but I am not sure how to relate them. Also, I have not had a chance to us the \dt function. Can you give me some pointers or point me to the docs?

Why didn't you just try it?

dalroi:solfertje > psql -E development
Welcome to psql 8.3.9, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

development=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index
' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

No relations found.

> SPAMfighter has removed 1388 of my spam emails to date.

You shouldn't be sending spam, you know ;) Or isn't that what's meant here?
That's a pretty useless number anyhow, the spam filter I use (dspam) catches about that much in a weeks time for my account alone. Without a total or a starting date its just a meaningless number.

Do you have any influence on what it prints under your messages?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4be7da5510411734319221!