Thread: Listing table definitions by only one command
<div dir="ltr">Hi<br /> 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?<br /><br />Thanks.<br /></div>
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;
wesSELECT
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;
<div class="moz-cite-prefix">Hi Carla,<br /><br /> Il 17/07/2013 17:29, Carla Goncalves ha scritto:<br
/></div><blockquotecite="mid:SNT139-W9B6944EFDF47E2E64EC54C2610@phx.gbl" type="cite"><style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style><div dir="ltr">Hi<br /> I would like to list the definition of all user tables by only one command. Is there
away to *not* show pg_catalog tables when using "\d ." in PostgreSQL 9.1.9?<br /></div></blockquote><br /> The simpler
waysimilar to a "\d ." I know is a query like this (supposing you are not interested also to 'information_schema'
schemeas well as 'pg_catalog', and interested only on tables list):<br /><br /> SELECT b.table_schema, a.table_name,
a.column_name,a.data_type, a.is_nullable FROM information_schema.columns a INNER JOIN <br /> (SELECT * FROM
information_schema.tablesWHERE table_type = 'BASE TABLE' AND table_schema <> 'pg_catalog' AND table_schema
<>'information_schema' ORDER BY table_name) b <br /> ON a.table_name = b.table_name;<br /><br /> This query
outputis a table with the same fields shown with "\dS ." command, ordered by tables name and organized as follows:<br
/><br/> table_schema | table_name | column_name | data_type | is_nullable <br />
--------------------+----------------+-------------------+-------------+--------------<br/> your_schema |
your_table | column_1 | integer | YES<br /> ... | ... |
... | ... | ... <br /><br /> It's quite less readable than "\d." (you'll obtain just one
tablein output than a single table for each table name), but it is ordered by table name and could be useful.<br /><br
/>Hope it helps.<br /><br /> Giuseppe.<br /> <br /><br /><br /><pre class="moz-signature" cols="72">--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
<a class="moz-txt-link-abbreviated" href="mailto:giuseppe.broccolo@2ndQuadrant.it">giuseppe.broccolo@2ndQuadrant.it</a>
|<a class="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.it">www.2ndQuadrant.it</a></pre>
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'
ANDn.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