How can I do a dump of only the table structure in PostgreSQL?

Better Stack Team
Updated on November 23, 2023

To perform a dump of only the table structure (schema) in PostgreSQL, you can use the pg_dump command with the --schema-only option. Here's how to do it:

pg_dump --schema-only -t your_table_name your_database_name > table_structure_dump.sql

Let's break down this command:

  • -schema-only - This option specifies that you want to dump only the schema (table structure) of the database objects. It excludes the data.
  • t your_table_name - This option allows you to specify the name of the table for which you want to dump the schema. Replace your_table_name with the name of the specific table you want to dump. If you want to dump the schema for multiple tables, you can list them separated by commas, e.g., t table1,table2.
  • your_database_name - Replace this with the name of the PostgreSQL database you want to dump the table structure from.
  • > table_structure_dump.sql - This part of the command directs the output to a file named table_structure_dump.sql. You can choose a different filename if you prefer.

After running this command, you will have a SQL script (in this case, table_structure_dump.sql) that contains the SQL commands to recreate the table structure, including all columns, indexes, constraints, and other schema-related information for the specified table. The script does not contain any data insertion statements, making it suitable for creating an empty table with the same structure in another PostgreSQL database or for version control of your schema.

Got an article suggestion? Let us know
Explore more
Licensed under CC-BY-NC-SA

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Make your mark

Join the writer's program

Are you a developer and love writing and sharing your knowledge with the world? Join our guest writing program and get paid for writing amazing technical guides. We'll get them to the right readers that will appreciate them.

Write for us
Writer of the month
Marin Bezhanov
Marin is a software engineer and architect with a broad range of experience working...
Build on top of Better Stack

Write a script, app or project on top of Better Stack and share it with the world. Make a public repository and share it with us at our email.

or submit a pull request and help us build better products for everyone.

See the full list of amazing projects on github