Getting table attributes

[C G]

Dear All,

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?

Thanks

Colin

__________________________________________________ _______________
Find a cheaper internet access deal - choose one to suit you.
http://www.msn.co.uk/internetaccess
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Franco Bruno Borghesi]

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:

Dear All,

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?

Thanks

Colin

__________________________________________________ _______________
Find a cheaper internet access deal - choose one to suit you.
http://www.msn.co.uk/internetaccess


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (FreeBSD)

iD8DBQBAI86Q21dVnhLsBV0RAkx8AJ4634lTzmFhmV5/cJORUdwzJ6oLdwCgnKr0
O2a6dtNPupKq/VP9MX+YTOI=
=W0Sq
-----END PGP SIGNATURE-----

[Steve Atkins]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org