Postgres allows us to copy, clone, or duplicate a table with or without data. For this, different built-in commands, such as CREATE TABLE AS SELECT, CREATE TABLE AS TABLE, INHERITS, etc. are used in Postgres. Postgres supports all sorts of scenarios like copying an entire table, a partial table, or only the table’s structure.

This post demonstrates how to create a copy of a table in Postgres using four different methods.

• Method 1: Using CREATE TABLE AS SELECT Command
• Method 2: Using CREATE TABLE AS TABLE Command
• Method 3: Using CREATE TABLE LIKE Command
• Method 4: Using INHERITS Option

Users can use any of the stated methods depending on their needs.

Sample Table

A sample table named “author_info” is created with the following data:

Method 1: Using CREATE TABLE AS SELECT Command

The “CREATE TABLE AS SELECT” statement allows a user to copy an entire table, some specific records, or the table’s structure only. The stated command is not able to copy the indexes or constraints, such as NOT NULL, PRIMARY KEY, FOREIGN KEY, etc. Users need to follow the below-provided syntax to acquire the functionality of the stated command:

CREATE TABLE new_table_name AS 
SELECT * FROM existing_table_name
WITH NO DATA
WHERE condition;

Here, “new_table_name” represents a new table to be created while “existing_table_name” represents a table to be copied. However, if the "WITH NO DATA" option is specified, only the table structure will be copied. The “WHERE” clause will be utilized to copy a partial table.

Example 1: Duplicating a Complete Table

To create the copy of the selected table, i.e., “author_info”, we will execute the “CREATE TABLE AS SELECT” command as follows:

CREATE TABLE author_table_copy AS 
SELECT * FROM author_info;

Execute the below-provided command to verify the working of the “CREATE TABLE AS SELECT” command:

SELECT * FROM author_table_copy
ORDER BY author_id ASC;

The clone of the “author_info” table has been created successfully.

Example 2: Duplicating a Partial Table

To duplicate a partial table, use the CREATE TABLE AS SELECT statement with WHERE clause, as shown below:

CREATE TABLE author_table_copy AS 
SELECT author_id, author_name, author_exp
FROM author_info
WHERE author_id <= 5;

Execute the below-provided command to see the data from the duplicated table:

SELECT * FROM author_table_copy
ORDER BY author_id ASC;

A partial table has been copied successfully.

Example 3: Duplicating Table’s Structure

Execute the “CREATE TABLE AS SELECT” command to duplicate only the table’s structure:

CREATE TABLE author_table_copy AS 
SELECT * FROM author_info
WITH NO DATA;

Execute the below-mentioned command to see the data from the duplicated table:

SELECT * FROM author_table_copy;

The table’s structure has been copied successfully.

Method 2: Using CREATE TABLE AS TABLE Command

In PostgreSQL, the “CREATE TABLE AS TABLE” Command is used to duplicate the entire table or table’s structure only. However, you can’t copy indexes, NOT NULL, PRIMARY KEY, FOREIGN KEY constraints, etc. using the “CREATE TABLE AS TABLE” command.

CREATE TABLE new_table_name AS 
TABLE original_table
WITH DATA | WITH NO DATA;

Specify the “WITH DATA” clause to duplicate a table with complete data. However, when the "WITH NO DATA" option is specified, only the table structure will be copied.

Example 1: Copying a Complete Table

In the following code snippet, we will duplicate the entire table’s data via the “CREATE TABLE AS TABLE” command:

CREATE TABLE new_table_name AS 
TABLE original_table
WITH DATA;

Execute the “SELECT” query to verify the table’s duplication:

Example 2: Copying Table’s Structure

If you want only the table’s structure(without data), you must execute the “CREATE TABLE AS TABLE” statement with the “WITH NO DATA” clause:

CREATE TABLE author_info_copy1 AS 
TABLE author_info
WITH NO DATA;

Execute the “SELECT” query to describe the table’s structure:

From the output snippet, you can observe that the table’s structure has been copied successfully.

Method 3: Using CREATE TABLE LIKE Command

In Postgres, the “CREATE TABLE LIKE” statement is used to copy the table’s structure along with constraints, such as NOT NULL. The stated command has a pretty straightforward syntax, as shown in the following snippet:

CREATE TABLE table_name (LIKE original_table_name);

Let’s put the above-stated syntax into practice.

Example: Copying Table Via the LIKE Option

In the following example, the “CREATE TABLE” statement is executed with the “LIKE” option to create a copy of the “author_info” table:

CREATE TABLE author_info_copy
LIKE author_info;

Run the “SELECT” query to fetch the table’s structure:

SELECT * FROM author_info_copy;

The output signifies that a copy of the “author_info” table has been created successfully.

Method 4: Using INHERITS Option

Postgres offers an “INHERITS” option that is used to propagate the modifications made in the parent table to the child table. The “INHERITS” option not only inherits the data from the parent table but also allows us to add some new columns to the child table:

CREATE TABLE child_table(
col_name data_type constraint
) 
INHERITS (parent_table);

Use the below query to inherit a table without including new columns in the child column:

CREATE TABLE child_table()
INHERITS (parent_table);

Let’s comprehend the usage of the “INHERITS” option via the below-provided example.

Example: Inherit Parent Table

The below-given code inherits the “author_info” table via the INHERITS option:

CREATE TABLE author_info_copy(
author_age SMALLINT
) 
INHERITS (author_info);

Execute the SELECT query to see the structure of the child(inherited) table:

SELECT * FROM author_info_copy;

The output authenticates the usage of the “INHERITS” option.

Conclusion

Postgres offers different built-in commands, such as CREATE TABLE AS SELECT, CREATE TABLE AS TABLE, INHERITS, etc. to copy, clone, or duplicate a table with or without data. For instance, the “CREATE TABLE AS SELECT” statement allows a user to copy an entire table, some specific records, or the table’s structure only, the “CREATE TABLE LIKE” statement is used to copy the table’s structure along with constraints, and so on. This post explained various methods to copy a table in Postgres.