Announcing Crunchy Bridge for Analytics—interact with your data lake using Postgres commands via extension, with a vectorized, parallel query engine. Learn more in our announcement.

Tutorial Instructions

psql Echo Commands

Postgres has a couple features to echo back what you’re doing with psql.

Echo PSQL commands as SQL with -E

Since we’re using a special preloaded psql, you’ll use 

\set ECHO_HIDDEN on

If you’re starting a new local psql session or connecting to another machine, you can do -E to set this.

Now let’s have the echo show us something. Do a table lookup with:

\dt+

Now you’ll see that it echos back to you the query it used to get this data, plus at the bottom, the normal results of \dt+.

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 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
  am.amname as "Access method",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

**************************

                                    List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method |  Size  | Description 
--------+---------+-------+----------+-------------+---------------+--------+-------------
 public | weather | table | postgres | permanent   | heap          | 856 kB | 
(1 row)

Now isn’t that cool! We can use psql to find the names of Postgres’ internal tables, catalog, and other naming conventions.

Now let’s try with indexing. Create a simple index: 

CREATE INDEX idx_weather_type ON weather(event_type);

And now use psql to look at your index:

\di

Now you see the output:

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 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  c2.relname as "Table"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','I','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

**************************

                   List of relations
 Schema |       Name       | Type  |  Owner   |  Table  
--------+------------------+-------+----------+---------
 public | idx_weather_type | index | postgres | weather
(1 row)

nocomment

If you wanted to write other queries to the indexing tables or other internal systems, using -E is a great way to find how Postgres is storing those internal things and how to query them.

Echo Queries

You can also have psql echo back queries that it is running with:

\set ECHO queries

If you’re starting a new psql connections, you can add -e to your connection string to set this.

Echo queries will just read back to you the query with the results. Here’s a sample query. 

SELECT event_type, COUNT(*) AS event_count
FROM weather
GROUP BY event_type
ORDER BY event_count DESC
LIMIT 1;

And we get back:

SELECT event_type, COUNT(*) AS event_count
FROM weather
GROUP BY event_type
ORDER BY event_count DESC
LIMIT 1;


 event_type | event_count 
------------+-------------
 Hail       |         412
(1 row)

no comment

This can be useful if you’re running queries from a file or need the query output a 2nd time for record keeping.

Want to know everything you have preset with psql? Remember \set shows you the whole list of what you have going on. 

\set

Loading terminal...

Loading terminal...