How to Duplicate a Table in PostgreSQL

In PostgreSQL, duplicating a table can be an essential operation when you want to create a backup of a table or work with a similar structure while keeping the original table intact. There are several ways to duplicate a table, including copying both its structure and data, or just the structure. Below, we will walk through the most common methods to duplicate a table in PostgreSQL.

Method 1: Duplicating a Table Including Data

If you want to duplicate a table along with its data, you can use the CREATE TABLE AS statement. This method will create a new table that contains both the structure and the data of the original table.

CREATE TABLE new_table AS TABLE original_table;

This query will create a new table new_table that contains all the columns and data from the original_table. If you want to include specific columns or apply filtering conditions, you can modify the query like this:

CREATE TABLE new_table AS SELECT column1, column2 FROM original_table WHERE condition;

Method 2: Duplicating a Table Structure Only

If you only want to duplicate the table structure without the data, you can use the CREATE TABLE statement with the LIKE clause. This will create a new table with the same columns, types, and constraints as the original table.

CREATE TABLE new_table (LIKE original_table INCLUDING CONSTRAINTS);

Note that this will not copy any data into the new table. If you want to exclude certain elements like constraints or defaults, you can adjust the INCLUDING option accordingly.

Method 3: Using pg_dump for Table Duplication

If you need to duplicate a table across different PostgreSQL instances or create a backup of a table, you can use pg_dump. This utility allows you to dump the table's schema and data into a file, which can be restored to a different PostgreSQL database.

pg_dump -t original_table dbname > table_backup.sql

Then, you can restore the table into a new database:

psql dbname < table_backup.sql

Conclusion

Duplicating tables in PostgreSQL can be done efficiently using different methods depending on your needs. Whether you want to copy the entire structure and data, or just the structure, PostgreSQL provides you with powerful tools to achieve this. Experiment with these methods to find the best fit for your use case!