syntax to view the structure of a table using sql

[aardvark]

I need to access the structure of a table in a python script, so \d is not available -- I need to access the structure of a table using sql commands. (Also, \d is not working from psql anyway, because my workstation has the postgresql 8.2 client, while the server is running postgresql 7.4.)

In sqlite, for all tables, this is easy:
SELECT * FROM sqlite_master WHERE type='table'

I have looked up and down the internet for a postgresql answer, but so far with no luck.

I would appreciate the answer or some inspiration.

Thanks,

[rski]

I need to access the structure of a table in a python script, so \d is not available -- I need to access the structure of a table using sql commands. (Also, \d is not working from psql anyway, because my workstation has the postgresql 8.2 client, while the server is running postgresql 7.4.)

In sqlite, for all tables, this is easy:
SELECT * FROM sqlite_master WHERE type='table'

I have looked up and down the internet for a postgresql answer, but so far with no luck.

I would appreciate the answer or some inspiration.

Thanks,

Do not look the whole internet, just look at postgresql.org
here

http://www.postgresql.org/docs/8.2/interactive/catalogs.html

I think there will be need to join some system tables.

[aardvark]

Hey rski,

Actually, your response was helpful, but maybe not helpful in the way you intended. I looked at the link you sent, and picked out 3 tables that seemed to hold relevant information, namely pg_attribute, pg_constraint & pg_type. Then I googled "pg_attribu te pg_constraint pg_type table structure" without the quotes. I found what I was looking for, namely this:

SELECT
  a.attnum,
  a.attname AS field,
  t.typname AS type,
  a.attlen AS length,
  a.atttypmod AS lengthvar,
  a.attnotnull AS notnull
FROM
  pg_class c,
  pg_attribute a,
  pg_type t
WHERE
  c.relname = 'your_table_name'
  AND a.attnum > 0
  AND a.attrelid = c.oid
  AND a.atttypid = t.oid
  ORDER BY a.attnum

That was the kind of answer I was hoping for -- my brain might overheat if I had to figure that out for myself!

I found it here:

http://www.parsed.org/tag/sql/

Thanks,

[xportage]

thanks to aardvark
finally I can read the table structure