By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

syntax to view the structure of a table using sql

P: 2
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,
Nov 3 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
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/i.../catalogs.html
I think there will be need to join some system tables.
Nov 4 '07 #2

P: 2
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_attribute pg_constraint pg_type table structure" without the quotes. I found what I was looking for, namely this:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   a.attnum,
  3.   a.attname AS field,
  4.   t.typname AS type,
  5.   a.attlen AS length,
  6.   a.atttypmod AS lengthvar,
  7.   a.attnotnull AS notnull
  8. FROM
  9.   pg_class c,
  10.   pg_attribute a,
  11.   pg_type t
  12. WHERE
  13.   c.relname = 'your_table_name'
  14.   AND a.attnum > 0
  15.   AND a.attrelid = c.oid
  16.   AND a.atttypid = t.oid
  17.   ORDER BY a.attnum
  18.  
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,
Nov 5 '07 #3

P: n/a
thanks to aardvark
finally I can read the table structure
Sep 30 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.