Re: Listing table definitions by only one command

Hi
I would like to list the definition of all user tables by only one command. Is there a way to *not* show pg_catalog tables when using "\d ." in PostgreSQL 9.1.9?

The simpler way similar to a "\d ." I know is a query like this (supposing you are not interested also to 'information_schema' scheme as well as 'pg_catalog', and interested only on tables list):

SELECT b.table_schema, a.table_name, a.column_name, a.data_type, a.is_nullable FROM information_schema.columns a INNER JOIN
(SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema <> 'pg_catalog' AND table_schema <> 'information_schema'  ORDER BY table_name) b
ON a.table_name = b.table_name;

This query output is a table with the same fields shown with "\dS ." command, ordered by tables name and organized as follows:

    table_schema | table_name | column_name | data_type | is_nullable
   --------------------+----------------+-------------------+-------------+--------------
     your_schema |  your_table  |    column_1     |   integer   |     YES
             ...           |         ...         |          ...            |       ...       |      ...

It's quite less readable than "\d." (you'll obtain just one table in output than a single table for each table name), but it is ordered by table name and could be useful.

Hope it helps.

Giuseppe.
   

-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it