Re: postgresql rookie needs help with system catalog

On Wed, Jan 22, 2020 at 9:56 AM barry kimelman <perlmaster56(at)gmail(dot)com>
wrote:

> I am trying to come up with a query of the system catalog tables to give
> me the same info as the psql command "\d tablename"
>
> so far I have come up with the following query
> 1 SELECT
> 2 a.attname as "Column",
> 3 pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
> 4 FROM
> 5 pg_catalog.pg_attribute a
> 6 WHERE
> 7 a.attnum > 0
> 8 AND NOT a.attisdropped
> 9 AND a.attrelid = (
> 10 SELECT c.oid
> 11 FROM pg_catalog.pg_class c
> 12 LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
> c.relnamespace
> 13 WHERE c.relname = 'laptop'
> 14 AND pg_catalog.pg_table_is_visible(c.oid)
> 15 );
>
> which yields the following result
>
> Column | Datatype
> ---------------+------------------------
> id | integer
> name | character varying(250)
> price | double precision
> purchase_date | date
> (4 rows)
>
> But how do I get the Collation and Nullable and Default values displayed
> by the "psql \d" command ?
>
> Thanks.
> --
>
> Barrry Kimelman
> Winnipeg, Manitoba, Canada
>

You can turn the ECHO_HIDDEN flag on in psql. This will show the underlying
queries that run whenever you do any of the \d psql commands. They're often
more complex than you expect, but definitely helps you with learning the
system catalogs as you're trying to do here

Just type the following in psql, and your next \d commands will also show a
bunch of additional details. Can either log off or set this back to "off"
to disable it.

\set ECHO_HIDDEN on

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com