Tip: View the SQL query behind psql commands

May 15, 2015
postgresql, tech, tips

If you want to view the SQL query used to construct the information returned from a psql command (which will help you learn the underlying information schema) then type \set ECHO_HIDDEN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$ psql test
psql (9.4.1)
Type "help" for help.

test=# \set ECHO_HIDDEN
test=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     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 <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

      List of relations
Schema | Name | Type  | Owner
--------+------+-------+--------
public | temp | table | andrew
(1 row)
comments powered by Disqus