pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
To: pgsql-sql@postgresql.org
Subject: Re: Listing table definitions by only one command
Date: Thu, 18 Jul 2013 18:39:47 +0200
Message-ID: <51E81A53.7040200@2ndquadrant.it> (raw)
In-Reply-To: <SNT139-W9B6944EFDF47E2E64EC54C2610@phx.gbl>
References: <SNT139-W9B6944EFDF47E2E64EC54C2610@phx.gbl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
view thread (4+ messages) latest in thread
Message-ID: <51E81A53.7040200@2ndquadrant.it>
Permalink: ../51E81A53.7040200@2ndquadrant.it/
Also on: postgresql.org/message-id/51E81A53.7040200@2ndquadrant.it
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: giuseppe.broccolo@2ndquadrant.it
Subject: Re: Listing table definitions by only one command
In-Reply-To: <51E81A53.7040200@2ndquadrant.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox