Message-ID: <51E81A53.7040200@2ndquadrant.it>
Date: Thu, 18 Jul 2013 18:39:47 +0200
From: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130623 Thunderbird/17.0.7
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Listing table definitions by only one command
References: <SNT139-W9B6944EFDF47E2E64EC54C2610@phx.gbl>
In-Reply-To: <SNT139-W9B6944EFDF47E2E64EC54C2610@phx.gbl>
Content-Type: multipart/alternative;
 boundary="------------030302010609070506080004"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------030302010609070506080004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------030302010609070506080004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">Hi Carla,<br>
      <br>
      Il 17/07/2013 17:29, Carla Goncalves ha scritto:<br>
    </div>
    <blockquote cite="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 a way to *not* show pg_catalog tables when
        using "\d ." in PostgreSQL 9.1.9?<br>
      </div>
    </blockquote>
    <br>
    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):<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.tables WHERE table_type = 'BASE
    TABLE' AND table_schema &lt;&gt; 'pg_catalog' AND table_schema
    &lt;&gt; 'information_schema'&nbsp; ORDER BY table_name) b <br>
    ON a.table_name = b.table_name;<br>
    <br>
    This query output is a table with the same fields shown with "\dS ."
    command, ordered by tables name and organized as follows:<br>
    <br>
    &nbsp;&nbsp;&nbsp; table_schema | table_name | column_name | data_type |
    is_nullable <br>
    &nbsp;&nbsp;
--------------------+----------------+-------------------+-------------+--------------<br>
    &nbsp;&nbsp; &nbsp; your_schema |&nbsp; your_table&nbsp; | &nbsp;&nbsp; column_1&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; integer&nbsp;&nbsp;
    |&nbsp;&nbsp;&nbsp;&nbsp; YES<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    ...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | &nbsp; &nbsp;&nbsp; ... <br>
    <br>
    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.<br>
    <br>
    Hope it helps.<br>
    <br>
    Giuseppe.<br>
    &nbsp;&nbsp;&nbsp; <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>
  </body>
</html>

--------------030302010609070506080004--