13

In pgAdmin III you can:

  • right-click a table;
  • scripts;
  • CREATE script;
  • save the script from the SQL Editor.

If one has to do this for more than one table, is there a way to combine the scripts in one file (apart from manually copy-pasting them)? If this can be done via psql prompt or phppgadmin, that will be ok too.

Alexander Popov
  • 23,073
  • 19
  • 91
  • 130

2 Answers2

28

Here's a way using pgAdmin.

  • Right-click on your database (or schema).
  • Choose "backup"
  • Under "Format" choose "plain"
  • Under "Dump Options #1" choose "Only schema"
  • Under "Objects" choose the tables you want.

Then click "backup". The output should be a plain text file with the create table statements.

Here's the PgAdmin documentation on backup.

bookman B.
  • 117
  • 8
mlinth
  • 2,968
  • 6
  • 30
  • 30
  • Do you know the updated procedure for pgAdmin 4? – sw1337 Nov 26 '22 at 15:46
  • The other answer here using pgdump will get you what you need. I don't know how to do this in pgadmin 4. – mlinth Dec 22 '22 at 20:17
  • For those who stumble in this thread but want to have a CREATE statement but also INSERTs to fill it with data, do a "Backup" -> "Format: Plain" like in answer, but don't choose "Only schema", in Table Options choose "Use Column INSERTS" and then press Backup. – Dmitry Polovinkin Aug 28 '23 at 12:40
  • And regarding @sw1337 question - as I see in pgAdmin 4 there is the choice "Only schemas" in Data Options tab in Backup window, so maybe that's it. – Dmitry Polovinkin Aug 28 '23 at 12:42
6

"pg_dump" is also an option here. The -s flag for schema-only and -t to select specific tables. It's not psql or pgAdmin though. It's a utility available to the PostgreSQL user. Example:

sudo su - postgres
pg_dump -s database
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
OverworkedTechydude
  • 253
  • 3
  • 9