How to print the table structure from postgresql?

Question

I am using phpPgAdmin in the browser and PgAdmin III for Windows. Is there anyway to take the printout of the table structure for the entire database?

filiprem · Accepted Answer · 2016-06-15 16:27:30Z

8

The standard way of exporting database schema is pg_dump:

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

Sligtly better way combines pg_dump with pg_restore list filtering:

#!/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, pg_dump command can be generated in PgAdmin III:

right click the database in object browser, select "Backup"
select destination filename (common extension is .sql or .txt)
Choose "Plain" format. (that is, text format)
on "Dump Options #1" tab, tick "Only Schema"
click "Backup"

Note: the resulting file will have not only tables, but also all other objects (views, functions, etc.). If you need only the minimal printout, you can edit this file in text editor and remove unneeded stuff. Leave only "Type: TABLE;" items.

edited Jun 15, 2016 at 16:27

answered Dec 28, 2011 at 8:26

filiprem

7,2131 gold badge35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

That should be pg_dump not psql

omikron · Accepted Answer · 2014-04-03 14:07:44Z

5

If you are on Windows and pgAdmin, you should 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 details about one table.

answered Apr 3, 2014 at 14:07

omikron

2,8461 gold badge28 silver badges35 bronze badges

Comments

sniperwolf897 · Accepted Answer · 2014-02-12 15:13:07Z

1

You can do them one at a time as you need them. Right click on a table in pgAdminIII, go to Reports and select "Data Dictionary Report".

For the output format, select "XHTML 1.0 Transitional", choose "Embed the default stylesheet" option, give it a file name and click OK.

Open the XML file in your browser and print.

answered Feb 12, 2014 at 15:13

sniperwolf897

111 silver badge2 bronze badges

2 Comments

Ajay Takur Over a year ago

How about for with the data?

pdog Over a year ago

Is there a programmatic way to run the equivalent of pgAdminIII "Data Dictionary Reports" on all the tables within a schema?

Collectives™ on Stack Overflow

How to print the table structure from postgresql?

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related