How to Duplicate a Table in PostgreSQL?

Many users ask for the duplication of a table without creating it again and adding data within PostgreSQL. Duplication commands can be used here. Let’s see it by opening the pgAdmin GUI from the Start menu of the Windows 10 desktop. Add your server password twice upon asked. After that, you will get a pgAdmin Graphical User Application Interface of PostgreSQL. Within the Databases, you can explore many things. You will find a database Postgres that has been already defined and built by PostgreSQL within the installation and configuration process. So, you don’t have to create a new database.

Example 01:

Let’s have our first example to duplicate a table already defined in Postgres. Upon exploring the database Postgres you will find the option of Tables. Create a new table “test” with some columns record in it. You can find this table under the options of a table upon exploring it, as shown in the image below.

Please tap on the icon of Query Tool to open it. When it gets open, write the SELECT query into it to fetch the newly created table “test” records as per the below command. Tap on the icon of “Run” to execute this command. The output shows three different tables “test” columns with their records, e.g. ID, Fname, and Lname.

# SELECT * FROM test;

Now, it’s time to create a duplicate table “Dup_test” for the table “test”. So, open the new tab of a query tool first and write out the command stated below. This query has its subpart to fetch all the records of table “test” using the SELECT statement. The CREATE TABLE command has been used to create a new table “Dup_test” the same as the table “test”. The SELECT statement has been fetching all the data and copying it into table “Dup_test”. Execute the query using the icon “Run” from the top taskbar. Upon executing this query, PostgreSQL shows the success message within the output area under the messages section.

# CREATE TABLE duplicate_table_name AS (SELECT * FROM table_name);

When you explore the Tables list, it shows you the two tables, e.g. dup_test and test.

When we check the newly created table “dup_test” by using the SELECT query in the query tool area, we have found that it contains the same data and structure as the table “test” has. So, the record and structure of table “test” have been fully duplicated in the table “dup_test”.

# SELECT * FROM table_name;

Example 02:

A user can also create a duplicate table in PostgreSQL using another command. This duplication will be done without the duplication of table data. Hence, we will be using the “no data” keyword after the select statement as per the below query. The query has been creating a new table named “duplicate” with CREATE TABLE statement and copying the structure of a table “test” via the SELECT statement. The statement “with no data” will be used to prevent this query to copy data from a table “test” to a table “duplicate”. Upon execution, the query was successful as per the below output, and the table was successfully duplicated.

# CREATE TABLE table_name AS (SELECT * FROM table_name) with no data;

You can find this table within the “Tables” section of PostgreSQL as below.

Upon checking the records of a newly duplicated table named “duplicate” with the SELECT query as below, we have found that the structure of a table is the same as table “test”. There are no records in this table due to the “with no data” statement usage within the query. Hence, the query was successful.

# SELECT * FROM table_name;

Example 03:

Another quick and simplest way to duplicate a table is using the “AS TABLE” statement within the CREATE TABLE command of PostgreSQL. In this instance, we will see how this query works magically. So, we have opened the query tool by its icon. Then we have to write the below query in it. We have created a table named “new” as a copy of a table “test” by a clause “AS TABLE” within our query. Try out the command in the query area of the command-line shell of PostgreSQL to see the results. Click the Run icon from the pgAdmin graphical user interface taskbar or press the “Enter” key from the keypad if you are working within the command shell of SQL to run this query. You will see that the query works properly as per the output shown in the snapshot output area, e.g. Messages. This means that a table “test” has been duplicated successfully, and a new table “new” has been created in the database Postgres.

# CREATE TABLE table_to_be_duplicated AS TABLE table_to_be_copied_from;

You can see the newly created table “new” within the list of tables under the database Postgres.

Upon fetching the contents of a table “new” by the query tool using a SELECT command, it shows the same data as the table “test” has along with the structure, e.g. column names.

Example 04:

Let’s have another simple example to illustrate the concept of duplication. This time, we have created a table “new” within the database Postgres of the pgAdmin graphical user interface. This table has 10 records in it within its four columns, e.g. ID, Name, City, and Age. Let’s see the records of table “new” by using the query tool. We have tried the below command within the query area to fetch the table “new” order by ID column. The output for this command shows the 10 records for some users.

# SELECT * FROM table_name ORDER BY “column_name” ASC;

To create a duplicate table, open up a new tab for the query tool. We have used the below command to create a new table “duplicate” as the table “new” shown above. We have been using the “WITH NO DATA” clause within this query to not copy the contents of a table. Instead, this query will only make a copy of a structure of a table “new”. So after writing this query within the query area, tap on the Run button shown in the taskbar of pgAdmin. The query will get executed, and the success message for the duplicate table will be shown in the output area of the query tool as per the snapshot below.

# CREATE TABLE duplicate_table_name AS TABLE table_name WITH NO DATA;

After duplicated and created a table, let’s see the newly created duplicated table, e.g. “duplicate”. So we have fetched the contents of a table “duplicate” while using the SELECT query within the query area ordered by the column “ID”. We have seen that the table “duplicate” structure is the same as the table “new”. This table didn’t copy the records of table “new” as using the “WITH NO DATA” clause.

# SELECT * FROM table_name ORDER BY “ID” ASC;

Conclusion:

We have discussed different PostgreSQL commands to duplicate a table. We have seen how to duplicate a table structure with and without its data. All these commands are equally efficient to be used on the command-line shell of PostgreSQL.