From cgourofino@hotmail.com Wed Jul 17 15:29:57 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzTfw-0003Lh-VC for pgsql-sql@arkaria.postgresql.org; Wed, 17 Jul 2013 15:29:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1UzTfw-0000qk-Aw for pgsql-sql@arkaria.postgresql.org; Wed, 17 Jul 2013 15:29:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzTfv-0000qe-MH for pgsql-sql@postgresql.org; Wed, 17 Jul 2013 15:29:55 +0000 Received: from snt0-omc2-s11.snt0.hotmail.com ([65.55.90.86]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzTfr-0006LA-RG for pgsql-sql@postgresql.org; Wed, 17 Jul 2013 15:29:55 +0000 Received: from SNT139-W9 ([65.55.90.71]) by snt0-omc2-s11.snt0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 17 Jul 2013 08:29:47 -0700 X-TMN: [LLP2Phwf31UtsVw4PaJDiJpvRnfoeV8F] X-Originating-Email: [cgourofino@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_93b1bb6b-66b7-400f-8752-0a6f9b4b7722_" From: Carla Goncalves To: "pgsql-sql@postgresql.org" Subject: Listing table definitions by only one command Date: Wed, 17 Jul 2013 18:29:47 +0300 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 17 Jul 2013 15:29:47.0889 (UTC) FILETIME=[78B90E10:01CE8302] X-Pg-Spam-Score: -0.4 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_93b1bb6b-66b7-400f-8752-0a6f9b4b7722_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 Postg= reSQL 9.1.9? Thanks. = --_93b1bb6b-66b7-400f-8752-0a6f9b4b7722_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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= .

= --_93b1bb6b-66b7-400f-8752-0a6f9b4b7722_-- From comptekki@gmail.com Wed Jul 17 16:07:37 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzUGO-0005Hk-RR for pgsql-sql@arkaria.postgresql.org; Wed, 17 Jul 2013 16:07:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1UzUGO-0000Kc-B4 for pgsql-sql@arkaria.postgresql.org; Wed, 17 Jul 2013 16:07:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzUGN-0000KV-7s for pgsql-sql@postgresql.org; Wed, 17 Jul 2013 16:07:35 +0000 Received: from mail-ie0-x233.google.com ([2607:f8b0:4001:c03::233]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzUGJ-00073k-2f for pgsql-sql@postgresql.org; Wed, 17 Jul 2013 16:07:34 +0000 Received: by mail-ie0-f179.google.com with SMTP id c10so4471310ieb.38 for ; Wed, 17 Jul 2013 09:07:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=6sxFpyGoNkRj3xP2qp45iowwlVDEtKFOiup21BWlZNE=; b=aWfjBGFtH9QAdACBsWChFtf3wCgi2tl05HSWL6uc2UYjNMBnii7KeTizpUuuMc7KSE Ar1dUYMQ5FOU4100pvGbp9dk02G+5nEL8lpKvzZlosiUQYfZFEQneCuNhdjAGtSxviaO htsJTah//ggnXyZKAimvxfwRcZn+CchCZxGSRlC5TP4SEuXUmy+S6SISuS5Y+N0Qhau1 L6PFgX5sdARDGw0R4CsbwAdT3aY4GC9gSbjNQUj0U4OLZFBG53BLHOhp8koklqLEhuua jtlWziRRS42O19N5CmZ6+rtQQGAzLq7qU+9CWzwzniE6WprqGnZFQNfv2CiiShJUuoBl oBXQ== MIME-Version: 1.0 X-Received: by 10.43.67.3 with SMTP id xs3mr5176965icb.45.1374077249270; Wed, 17 Jul 2013 09:07:29 -0700 (PDT) Received: by 10.50.83.70 with HTTP; Wed, 17 Jul 2013 09:07:29 -0700 (PDT) In-Reply-To: References: Date: Wed, 17 Jul 2013 10:07:29 -0600 Message-ID: Subject: Re: Listing table definitions by only one command From: Wes James To: Carla Goncalves Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=001a11c2162e56378004e1b74a0f X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a11c2162e56378004e1b74a0f Content-Type: text/plain; charset=ISO-8859-1 On Wed, Jul 17, 2013 at 9:29 AM, Carla Goncalves 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 --001a11c2162e56378004e1b74a0f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

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 use= r tables by only one command. Is there a way to *not* show pg_catalog table= s when using "\d ." in PostgreSQL 9.1.9?

Thanks.
=

I didn't see a way to do that with \ comman= ds, but found this with a google search:

SELECT
N.nspname,
C.relname,
A.attname,
pg_catalog.format_type(a.atttypid, a.atttyp= mod) AS typeName
FROM
pg_class C,
pg_namespace N,
pg_attribute A,
pg_type T
WHERE
(C.relkind=3D'r') AND
(N.oid=3DC.relnamespace) AND
(A.attrelid=3DC.oid) AND
(A.atttypid=3DT.oid) AND
(A.attnum>0) AND
(NOT A.attisdropped) AND
(N.nspname ILIKE 'public') ORDER BY
C.oid, A.attnum;

wes

--001a11c2162e56378004e1b74a0f-- From giuseppe.broccolo@2ndquadrant.it Thu Jul 18 16:38:09 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzrDV-0004Mw-MJ for pgsql-sql@arkaria.postgresql.org; Thu, 18 Jul 2013 16:38:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1UzrDV-00083U-0T for pgsql-sql@arkaria.postgresql.org; Thu, 18 Jul 2013 16:38:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzrDT-00083N-Uf for pgsql-sql@postgresql.org; Thu, 18 Jul 2013 16:38:08 +0000 Received: from mail03.pnet.xcon.it ([62.48.53.116]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzrDL-0008U0-Ka for pgsql-sql@postgresql.org; Thu, 18 Jul 2013 16:38:07 +0000 Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by mail03.pnet.xcon.it (Postfix) with ESMTP id 0ED28B711 for ; Thu, 18 Jul 2013 18:37:58 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from mail03.pnet.xcon.it ([10.68.1.24]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id wU2CKAOY9S6z for ; Thu, 18 Jul 2013 18:37:39 +0200 (CEST) Received: from bnc1.pnet.xcon.it (bnc1.pnet.xcon.it [10.68.1.23]) by mail03.pnet.xcon.it (Postfix) with ESMTP id E1220B6E0 for ; Thu, 18 Jul 2013 18:37:57 +0200 (CEST) Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by bnc1.pnet.xcon.it (Postfix) with ESMTP id D90CA1022E35 for ; Thu, 18 Jul 2013 18:37:57 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from bnc1.pnet.xcon.it ([10.68.1.23]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id hCZgaY4onLJX for ; Thu, 18 Jul 2013 18:37:38 +0200 (CEST) Received: from [192.168.0.131] (unknown [83.149.163.202]) (Authenticated sender: giuseppe.broccolo@2ndquadrant.it) by bnc1.pnet.xcon.it (Postfix) with ESMTPSA id 4FA581022E23 for ; Thu, 18 Jul 2013 18:37:57 +0200 (CEST) Message-ID: <51E81A53.7040200@2ndquadrant.it> Date: Thu, 18 Jul 2013 18:39:47 +0200 From: Giuseppe Broccolo 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: In-Reply-To: Content-Type: multipart/alternative; boundary="------------030302010609070506080004" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql 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

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-- From fluca1978@infinito.it Wed Jul 24 10:00:53 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1vsK-0007wN-Qr for pgsql-sql@arkaria.postgresql.org; Wed, 24 Jul 2013 10:00:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V1vsK-0004J5-7B for pgsql-sql@arkaria.postgresql.org; Wed, 24 Jul 2013 10:00:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1vsJ-0004Iy-5a for pgsql-sql@postgresql.org; Wed, 24 Jul 2013 10:00:51 +0000 Received: from mail-wg0-x234.google.com ([2a00:1450:400c:c00::234]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1vsG-0007ZU-4W for pgsql-sql@postgresql.org; Wed, 24 Jul 2013 10:00:50 +0000 Received: by mail-wg0-f52.google.com with SMTP id b13so198259wgh.19 for ; Wed, 24 Jul 2013 03:00:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; bh=ZLDYN0+3CX48L1pHXkE6xXHjZ48u9UEDQtj7+fiDj8E=; b=0vWikom8vUw6Rpb2C1stzo3H7FzZc2jMGI1lAWdAZzKlWm2RrK0bW6q0k6Xcikur5k AYG9F+YGDY0TbczwSz17om0UBOb1QJjYXBmDf0Yj1mfbeU2L7h/BteaRjjlqfP46VlGF XFSk8dZPVAMI5lFNq7aQgyf0r6E0D/qIrr+eY5B4aq/7I6OJqf5GkDjwELErbvpJXlmG Zs0kgLQV/i1gbmPG81cyPeCLSsTcsjjrn0GbpMOJLbyf6D6UNrJcMokZ98sOrF75haAz 9Me5839fbyGg4gRVH02xxXvZYhFGng8hKQg858a+C1IGrcTkFCOO5vokfQS2rBln5Ooi fl+Q== MIME-Version: 1.0 X-Received: by 10.194.174.4 with SMTP id bo4mr26382860wjc.40.1374660047371; Wed, 24 Jul 2013 03:00:47 -0700 (PDT) Received: by 10.194.8.39 with HTTP; Wed, 24 Jul 2013 03:00:47 -0700 (PDT) In-Reply-To: References: Date: Wed, 24 Jul 2013 12:00:47 +0200 X-Google-Sender-Auth: y4wowc5hMBxXBwerY3FFwTUIKk4 Message-ID: Subject: Re: Listing table definitions by only one command From: Luca Ferrari To: Carla Goncalves Cc: "pgsql-sql@postgresql.org" Content-Type: text/plain; charset=ISO-8859-1 X-Pg-Spam-Score: -1.6 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On Wed, Jul 17, 2013 at 5:29 PM, Carla Goncalves 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