Question 283641 in PostgreSQL Question posted in
The official documentation can be found here.

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  |
Tags:

4

Answers


  1. 0

    You should check the command gdesc
    it describes the result columns that a query will have.

    So let’s assume you have a simple table with three columns. It would help if you used it like below

    SELECT * FROM sample_table gdesc

    and the result that you will receive would be

       Column   |            Type             
------------+-----------------------------
 id         | uuid
 created_at | timestamp without time zone
 deleted_at | timestamp without time zone
(4 rows)

    I also encourage you to check this thread

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    Use the system catalog pg_attribute directly:

    SELECT
    attname AS column
FROM
    pg_attribute
WHERE
    attrelid = 'users'::regclass
AND 
    attnum > 0;

    pg_attribute stores the column names for table like objects e.g. tables, indexes, views, etc. The 'users'::regclass translates the table name into oid needed for attrelid. attnum > 0 eliminates the system columns, cmax, cmin, ctid, etc.

    Login or Signup to reply.
  4. 0

    You can use less -S or leafpad or any other text editor program, that would not wrap lines, as a pager:

    tometzky=> setenv PAGER 'less -S'
tometzky=> setenv PAGER leafpad

    Or you can query information schema to get similar data with extended output:

    tometzky=> x on
Expanded display is on.

tometzky=> select
  column_name,
  is_nullable,
  data_type,
  character_maximum_length
from information_schema.columns
where table_name='users' and table_schema=current_schema();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search