Search code examples
postgresqlpsql

PostgreSQL \d Commands: Any way to "select" only one column?

Unlike most SQL derivatives, PostgreSQL doesn't use the traditional DESCRIBE TABLE foo syntax; it uses \d foo (from the psql interpreter).

That's great, except that (especially when I have psql open in a terminal with limited width) it can be difficult to read the output of \d, because there are a bunch of columns and a bunch of values, and they can be hard to line everything up.

So, my simple question is, when using \d and its variants (eg. \df), is there any way to only get one column of output back? For instance, if I do \d users, instead of getting:

                                       Table "public.users"
   Column   |           Type           | Collation | Nullable |              Default              
------------+--------------------------+-----------+----------+-----------------------------------
 id         | integer                  |           | not null | nextval('users_id_seq'::regclass)
 created_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 updated_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 username   | character varying(255)   |           | not null | 
 title      | character varying(500)   |           |          | 
 first_name | character varying(50)    |           |          | 
 last_name  | character varying(50)    |           |          |

is there some way (eg. \d users Column, although this doesn't work) to get just:

                                         Table "public.users"
   Column   |           
------------+
 id         |
 created_at |
 updated_at |
 username   |
 first_name |
 last_name  |
Solution

  • Actual subset of \d output

    Yes. Pop the hood by setting ECHO-HIDDEN to on and throw away anything you don't like, grabbing only what you want. Every individual piece of what that output is constructed from, will work fine on its own.

    -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.

    PostgreSQL doesn't understand \d. It's an internal meta-command of psql, which is a client like any other. The data you see is a result of the client issuing a series of queries and gluing their output together - the setting above lets you learn what those queries were. You can use them "by hand" or even rearrange them to better fit your purpose.

    Terminal width

    You might want to check out expanded mode. psql offers multiple output formats to select from: wrapped has adjustable columns width. You should also have control over your terminal screen buffer layout in its properties - there, you could crank up the width and whenever something's too wide, you'll scroll right if you want or just leave it sticking out, off-screen.

    Querying structures/schema

    There's SQL-standard compliant information_schema namespace, and in it, there's columns view. You can go there directly:

    select column_name from information_schema.columns
where table_schema='public' and table_name='users';

    The views in that schema point to the same place psql gets the list of columns for tables and views when you run \d. The actual data is in the system catalog which psql queries directly.