Thread: Re: SQL statement : list table details

Hi,
Thanks for this query BTW, I was looking for a query like this :)
Let's see if anybody comes up with any solution to the data types.
-samik

Dino Hoboloney wrote:

>     I am looking for a SQL statement which would list table names, columns,
> and column types of a specific DB. So far I have managed to find a statement
> that lists all of the tables and columns in the DB with
>
> SELECT a.attnum, a.attname AS field, c.relname AS table_name FROM pg_class
> c, pg_attribute a WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND
> a.attnum > 0 AND a.attrelid = c.oid ORDER BY table_name, attnum;
>
> unfortunately I am unable to come up with a solution to listing the data
> types for the columns listed. Any ideas?
>
>
>

On Thu, 2002-02-28 at 19:59, Samik Raychauhduri wrote:
> Hi,
> Thanks for this query BTW, I was looking for a query like this :)
> Let's see if anybody comes up with any solution to the data types.

Do this:

$ psql -E
nconway=> \d my_table

This will display the queries that psql uses to determine the data types
of a table. The same holds true for all the other psql 'slash commands'.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

On Thu, 28 Feb 2002, Samik Raychauhduri wrote:

> Hi,
> Thanks for this query BTW, I was looking for a query like this :)
> Let's see if anybody comes up with any solution to the data types.
> -samik
>
> Dino Hoboloney wrote:
>
> >     I am looking for a SQL statement which would list table names, columns,
> > and column types of a specific DB. So far I have managed to find a statement
> > that lists all of the tables and columns in the DB with
> >
> > SELECT a.attnum, a.attname AS field, c.relname AS table_name FROM pg_class
> > c, pg_attribute a WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND
> > a.attnum > 0 AND a.attrelid = c.oid ORDER BY table_name, attnum;
> >
> > unfortunately I am unable to come up with a solution to listing the data
> > types for the columns listed. Any ideas?

If you're running 7.2, I think format_type(a.atttypid, a.atttypmod) in
the select list will give you the human readable type.