September 21st, 2017 by depesz | Tags: , , , | No comments »
Did it help? If yes - maybe you can help me?

On 5th of September 2017, Tom Lane committed patch:

Add \gdesc psql command.
This command acts somewhat like \g, but instead of executing the query
buffer, it merely prints a description of the columns that the query
result would have.  (Of course, this still requires parsing the query;
if parse analysis fails, you get an error anyway.)  We accomplish this
using an unnamed prepared statement, which should be invisible to psql
Pavel Stehule, reviewed by Fabien Coelho

This is actually pretty interesting thing. Let's assume we have a very complex, and rather slow query, for example:

select *, pg_sleep(1) from pg_database;

In my case, with 8 databases, it would take 8 seconds to run. And if all I need is to check what columns are really returned, or what datatypes they use, I can:

=$ \timing
Timing is on.
=$ select *, pg_sleep(1) from pg_database \gdesc
    Column     |   Type    
 datname       | name
 datdba        | oid
 encoding      | integer
 datcollate    | name
 datctype      | name
 datistemplate | boolean
 datallowconn  | boolean
 datconnlimit  | integer
 datlastsysoid | oid
 datfrozenxid  | xid
 datminmxid    | xid
 dattablespace | oid
 datacl        | aclitem[]
 pg_sleep      | void
(14 rows)
Time: 2.540 ms

And get results immediately (well, in 2.5ms in this case).

I, for one, know that I will be using this a lot, once Pg11 will be released. Thanks guys.

Leave a comment