Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to get field's name, datatype of each field, length of each field and precision of each field of a PostgreSQL table. Please let me SQL query to get this information.

share|improve this question

closed as off-topic by StanleyJohns, Paul White, Mark Storey-Smith, Max Vernon, dezso Sep 2 '13 at 5:23

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – StanleyJohns, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

Which SQL client are you using. In psql you can use the \d command. – a_horse_with_no_name Aug 21 '13 at 6:33
If you use psql, first issue the \set ECHO_HIDDEN on command then \d your_table_name - it will output the queries with which it collects the information it displays. – dezso Aug 21 '13 at 7:24
I am using pg...and I want to write inside python script.. – Apurvagowda Aug 22 '13 at 6:23
up vote 2 down vote accepted

The simplest way in SQL is to query the information_schema.columns view with a WHERE clause on table_schema and table_name matching yours. All the properties you want (and more) are in the output columns of this single view.

This view is part of the Information Schema whose purpose is to provide standard ways to do database introspection.

share|improve this answer

I suggest you use pg_dump get tables and view 's definition, it's more convenient。 The following command is getting the definiton of the database francs.

pg_dump -h -E UTF8 -s -v francs > francs.ddl

"-s" means only dump matching schema, not data.

share|improve this answer
Not only convenient: also a tool that is maintained by the PG group so that it will support all new features of the database in the DDL that it exports. – Colin 't Hart Aug 22 '13 at 13:05

All the information used to define each table,column,function etc is stored in the System Catalogs. The contents of these system tables are explained in the manual

Using psql you get easy access to pre-formatted display of this information. Type \? to list available options (under Informational).

If you wish to extract this data to use in your own app then you will probably find looking through the source code of psql an easy way to find examples of selecting the data.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.