How to print table structure from postgresql?

Question

I am using phpPgAdmin in a browser and PgAdmin III for Windows. Is there anyway to print out the table structure for the entire database?

Answer 1

The standard way to export a database schema is pg_dump :

#!/bin/sh pg_dump --schema-only MYDBNAME > output-file.sql 

Too better a way to combine pg_dump with pg_restore :

 #!/bin/sh dump=`mktemp` list=`mktemp` pg_dump --schema-only MYDBNAME -Fc -f $dump pg_restore -l $dump | grep ' TABLE ' > $list pg_restore -L $list $dump > output-file.sql rm $list $dump 

If you prefer GUI wizards, the pg_dump command can be generated in PgAdmin III :

• right-click the database in the object browser, select "Backup"
• select destination file name (common extension -.sql or .txt)
• Select the "Normal" format. (i.e. text format)
• on the "Parameters of dump No. 1" tab, check "Schema only"
• Click "Backup"

Note. In the resulting file, not only tables will be displayed, but also all other objects (views, functions, etc.). If you need only a minimal printout, you can edit this file in a text editor and delete unnecessary things. Leave only "Type: TABLE;" elements.

Answer 2

If you are on Windows and pgAdmin, you must have psql somewhere in C:\Program files\postgresql\<version>\bin\psql .

Run psql, and then you have \d , which prints all tables and indexes, and \d <table_name> , which gives you information about a single table.

Answer 3

You can do them one at a time as you need. Right-click on the table in pgAdminIII, open "Reports" and select "Report on Data Dictionaries".

For the output format, select "XHTML 1.0 Transitional", select "Insert Standard Style Sheet", specify a file name and click "OK."

Open the XML file in your browser and print.