PostgreSQL allows us to copy an existing table with or without data. In Postgres, either you can copy only the structure of an existing table, or you can copy a table completely along with its data. To copy only the table’s structure, you must specify a WITH NO DATA clause.

This write-up will demonstrate how to copy a table with or without data in PostgreSQL. So, let's start.

PostgreSQL: How to Copy a Table?

Let’s learn how to copy the table’s data in PostgreSQL. To do this, firstly, you need to understand the following syntax:

CREATE TABLE new_tab_name AS 
TABLE existing_tab_name;

By following the above syntax, the data of the existing table will be copied to the new table.

Example: How to Copy Entire Table’s Data in Postgres?

We have created a student_info table whose details are shown in the following snippet:

SELECT * FROM student_info;

Let’s run the following query to copy the data of the student_info table to a new table named student_record:

CREATE TABLE student_record AS 
TABLE student_info;

Let’s run the SELECT command to fetch all the records of the newly created student_record table:

SELECT * FROM student_record;

From the output, it is clear that all the records of student_info have been copied to the student_record table.

How to Copy Only Specific Table’s Record in PostgreSQL?

To partially copy the data of one table to another, use the WHERE clause as follows:

CREATE TABLE new_tab_name AS 
SELECT * FROM existing_tab_name
WHERE condition;

By following the above syntax, only those records will be copied to the new table, which satisfies the given condition.

Example: How to Copy Partial Data From a Table?

Suppose we want to copy the details of only those students who are above 18 years. To do so, we will copy the specific records from the student_info table to the selected_student table as follows:

CREATE TABLE selected_student AS 
SELECT * FROM student_info
WHERE std_age > 18;

Let’s execute the SELECT command to fetch the filtered/copied data:

SELECT * FROM selected_student;

The output shows that partial data has been copied to the seleted_student table.

How to Copy Only Table’s Structure in PostgreSQL?

If you need to copy the table’s structure without copying the table’s data, then you have to use the WITH NO DATA clause as follows:

CREATE TABLE new_tab_name AS 
TABLE existing_tab_name
WITH NO DATA;

Let’s implement it practically to get more clarity.

Example: How to Copy Only Structure of a Table in Postgres?

Suppose we have to copy only the table’s structure; to do that, we will use the WITH NO DATA clause as follows:

CREATE TABLE student AS 
TABLE student_info
WITH NO DATA;

Let’s utilize the SELECT statement to see the structure of the student table:

SELECT * FROM student;

The output authenticates that the structure of the student_info table has been successfully copied to the student table.

Conclusion

PostgreSQL allows us to copy an existing table with or without data. In Postgres, either you can copy only the structure of an existing table, or you can copy a table completely along with its data. You can also copy partial data of a table using the WHERE clause. To copy only the table’s structure, you must specify a WITH NO DATA clause. This post considered various examples to explain how to copy a table in PostgreSQL.