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

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

Backup

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