Getting table attributes

On Fri, Feb 06, 2004 at 04:53:47PM +0000, C G wrote:

I'm trying to get a table containing all the user created tables, which
contains the column name and their types. Basically, I want to be able to
do "\d all_user_tables" - but I can't use the \d notation.

I tried to combine the pg_ tables, but I couldn't get what I wanted (due to
my lack of skill).

Any ideas?

If you start psql with the -E flag then it'll show you the SQL query
it generates for the various \commands as you run them.

If you're using a recent version of postgres you can probably find the
data in a more version-independent way within information_schema, but I've
not played with that yet.

Cheers,
Steve

this query will list every table (with its attributes) in the "public"
schema. You could alter the where clause to add more schemas (maybe
using IN):

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;

On Fri, 2004-02-06 at 13:53, C G wrote: