pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedListing table definitions by only one command
4+ messages / 4 participants
[nested] [flat]
* Listing table definitions by only one command
@ 2013-07-17 15:29 Carla Goncalves <cgourofino@hotmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Carla Goncalves @ 2013-07-17 15:29 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks.
=
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Listing table definitions by only one command
@ 2013-07-17 16:07 Wes James <comptekki@gmail.com>
parent: Carla Goncalves <cgourofino@hotmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Wes James @ 2013-07-17 16:07 UTC (permalink / raw)
To: Carla Goncalves <cgourofino@hotmail.com>; +Cc: pgsql-sql
On Wed, Jul 17, 2013 at 9:29 AM, Carla Goncalves <cgourofino@hotmail.com>wrote:
> 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?
>
> Thanks.
>
I didn't see a way to do that with \ commands, but found this with a google
search:
SELECT
N.nspname,
C.relname,
A.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS typeName
FROM
pg_class C,
pg_namespace N,
pg_attribute A,
pg_type T
WHERE
(C.relkind='r') AND
(N.oid=C.relnamespace) AND
(A.attrelid=C.oid) AND
(A.atttypid=T.oid) AND
(A.attnum>0) AND
(NOT A.attisdropped) AND
(N.nspname ILIKE 'public')
ORDER BY
C.oid, A.attnum;
wes
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Listing table definitions by only one command
@ 2013-07-18 16:39 Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
parent: Carla Goncalves <cgourofino@hotmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Giuseppe Broccolo @ 2013-07-18 16:39 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Listing table definitions by only one command
@ 2013-07-24 10:00 Luca Ferrari <fluca1978@infinito.it>
parent: Carla Goncalves <cgourofino@hotmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Luca Ferrari @ 2013-07-24 10:00 UTC (permalink / raw)
To: Carla Goncalves <cgourofino@hotmail.com>; +Cc: pgsql-sql
On Wed, Jul 17, 2013 at 5:29 PM, Carla Goncalves <cgourofino@hotmail.com> wrote:
> 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?
>
What do you mean by "user tables"? The execution of \d without any
argument provides the definition of all reachable tables (by mean of
search_path) that are not belonging to the information schema or toast
space, that is:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f'
THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
This kind of queries are hard-coded into the psql program, and
therefore cannot be altered on the fly as far as I know.
One trick could be to define a custom query as a psql variable, let's say:
\set my_d '* from pg_class left join ....';
and then do something like
select :my_d;
It's shorter, but it is not the same as a builtin command.
Luca
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-07-24 10:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-17 15:29 Listing table definitions by only one command Carla Goncalves <cgourofino@hotmail.com>
2013-07-17 16:07 ` Wes James <comptekki@gmail.com>
2013-07-18 16:39 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-07-24 10:00 ` Luca Ferrari <fluca1978@infinito.it>
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