Re: Listing table definitions by only one command

Hi Carla,

Il 17/07/2013 17:29, Carla Goncalves ha scritto:
> 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(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it