Search code examples
postgresqlcommand-line-interfacepsql

Using results of \d command in psql

The command \d in psql lists all tables, views, and sequences. Is there a way to catch this output into a table and run a query on it?

mydb-> \d

      List of relations
Schema |  Name  | Type  |   Owner
-------+--------+-------+---------
public | mytab1 | table | username

mydb-> select * from <use relation from \d> where Type='view';

      List of relations
Schema |  Name  | Type  |   Owner
-------+--------+-------+---------

Alternatively, is there a way to use select-from-where query to get this table and other table meta data?

Thanks.

Solution

  • You can get the query behind \d if you start psql with the -E option. Then running \d will also show the metadata query.

    Once you have that query, it should be easy to adapt it to your needs.

    If you need it to builld a list of DDL statements, you can use psql's \gexec to execute the results of a query as SQL statements.