Re: postgresql rookie needs help with system catalog

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