Did you ever wonder what PostgreSQL is executing in the background when you use the psql shortcuts?

When you work with PostgreSQL you are probably using psql and when you use psql you are probably using one or more of the shortcuts psql is providing. These shortcuts provide a quick and convenient way to get meta data out of the PostgreSQL catalog which safes you from a lot of typing and typos. The only issue with that is that it hides the statements which are executed to get the meta data so you don’t know were the information is actually coming from. Of course you can check either the information_schema or the PostgreSQL system catalog and then write your own queries for what you are looking for. But, hey, there is a much easier way.

Lets start by creating a dummy table and an index:

postgres=# create table dummy ( a int primary key, b varchar(50), c timestamp with time zone );
CREATE TABLE
postgres=# create index i_dummy on dummy ( c );
CREATE INDEX
postgres=# 

The fastest way to get the definition of the table is:

postgres=# d dummy
             Table "public.dummy"
 Column |           Type           | Modifiers 
--------+--------------------------+-----------
 a      | integer                  | not null
 b      | character varying(50)    | 
 c      | timestamp with time zone | 
Indexes:
    "dummy_pkey" PRIMARY KEY, btree (a)
    "i_dummy" btree (c)

As you can see you do not only get the definition of the table itself but also information about the primary key and the index. But where does this information come from? As information about the index and the primary key is displayed as well the information must be coming from more than one catalog table, but which? Quite easy when you check the options of psql:

postgres@pgbox:/home/postgres/ [PG962] psql --help | grep "hidden"
  -E, --echo-hidden        display queries that internal commands generate

When you fire up psql with this option all the internal statements will be displayed when you use a short cut:

postgres@pgbox:/home/postgres/ [PG962] psql -E postgres
postgres=# d dummy
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(dummy)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '16679';
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation  t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16679' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************

********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
  pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = '16679' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
**************************

********* QUERY **********
SELECT pol.polname,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd 
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
WHEN '*' THEN 'ALL'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '16679' ORDER BY 1;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16679' ORDER BY inhseqno;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '16679' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************

             Table "public.dummy"
 Column |           Type           | Modifiers 
--------+--------------------------+-----------
 a      | integer                  | not null
 b      | character varying(50)    | 
 c      | timestamp with time zone | 
Indexes:
    "dummy_pkey" PRIMARY KEY, btree (a)
    "i_dummy" btree (c)

Here you go. Quite a lot of stuff is happening in the background and you can exactly see what it is. This is a great way to get known to the catalog. When you are already inside psql and want to switch the display of the hidden stuff to on you can do that as well:

postgres=# set ECHO_HIDDEN on
postgres=# d dummy
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(dummy)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

...

When you want to make this permanent add it to your .psqlrc (scroll down to the “Files” section). Have fun …