4

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.

4
  • 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
  • 2
    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
  • 1
    I am using pg...and I want to write inside python script.. – Apurvagowda Aug 22 '13 at 6:23
  • Meta: I don't think this question is off-topic / too localized at all. – mike Jan 15 '19 at 21:38
4

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.

0
7

I suggest you use pg_dump to get tables' and views' definition, it's more convenient. The following command gets the definition of the database francs.

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

-s means only dump matching schema, not data.

1
  • 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
2

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.

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