Postgres \d+ (show relation information) equivalent via sql

Question

I have seen several examples like here or here

and I am able to do most of what I can do but not all of them.

if I have :

\d+ myschema.mytable

I have all the columns listed and the indexes. Format for the index is for example :

Indexes:
    "id_pkey" PRIMARY KEY, btree (id,name,timestamp)
    "ix05_id" btree (name,company)

I was able to get the columns via sql and for the indexes and the keys i have found some sql from the above post but not exactly to bring in that format.

So for keys I was able to do :

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute, pg_namespace 
WHERE 
  pg_class.oid = 'mytable'::regclass AND 
  indrelid = pg_class.oid AND 
  nspname = 'myschema' AND 
  pg_class.relnamespace = pg_namespace.oid AND 
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
 AND indisprimary

So this one fetches the columns the key applies to, but not the name of the index/key? How can I add that in the above query? With what table do I need to join?

For the indexes I was able to do

SELECT * FROM pg_indexes WHERE tablename = 'mytable' AND schemaname = 'myschema' ;

Which fetches the indexes, but does not explicitly list the column they apply to - I only see the indexdef which is how they created? Can I get that info by joining with other tables or do I have to parse the indexdef and get the columns index applies to?

Answer 1

psql has a -E option:

-E
--echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

So if you run psql -E and do \d+ myschema.mytable, you should see exactly what SQL queries are executed internally.