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

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:
[color=blue]
> 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
>
> [/color]

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

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

On Fri, Feb 06, 2004 at 04:53:47PM +0000, C G wrote:[color=blue]
>
> 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?[/color]

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