Copying PostgreSQL Tables: Structure, Indexes, and Data - Explained Simply

This method leverages the CREATE TABLE statement with the LIKE clause to define a new table based on the existing one. You can also specify the INCLUDING INDEXES option to copy the indexes along with the table structure.

Example:

CREATE TABLE new_table LIKE original_table INCLUDING INDEXES;

Explanation:

• CREATE TABLE new_table: This defines a new table named new_table.
• LIKE original_table: This instructs PostgreSQL to create the new table with the same structure as the existing original_table.
• INCLUDING INDEXES: This option ensures that all indexes defined on original_table are also created on the new table.

Note:

This method only copies the table structure and indexes, not the actual data. You need to separately insert data into the new table using an INSERT statement.

Using pg_dump and pg_restore:

Another approach involves utilizing the built-in command-line utilities pg_dump and pg_restore.

Steps:

1. Dump the original table:

   pg_dump -t original_table database_name > original_table.sql
   

   • Replace database_name with the actual database name.
   • This command creates a backup file named original_table.sql containing the definition of the table and its indexes.

2. Restore the dump to create a new table:

   psql -d database_name < original_table.sql
   

   • This command restores the information from the dump file, creating a new table with the same structure and indexes as the original.

Related Issues and Solutions:

• Index Names: When using the CREATE TABLE ... LIKE ... method, PostgreSQL might assign different names to the copied indexes to avoid conflicts. To maintain the original index names, you can create the new table in a different schema and then manually rename the indexes.
• Data Copying: Both methods described above don't automatically copy the data. You need to use an INSERT statement to populate the new table with data from the original table.