PostgreSQL – Copy Table

In PostgreSQL, the copy table functionality is a powerful feature that allows us to efficiently duplicate existing tables, including their structure and data. This capability is essential for various database management tasks such as backing up data, migrating tables, or testing modifications in a separate environment

In this article, we will explain different methods to copy a table in PostgreSQL, providing detailed syntax, practical examples, and outputs to ensure that we can effectively utilize this feature in our projects.

Copying a Table Completely (Structure and Data)

To copy an entire table, including both its structure and data, use the ‘CREATE TABLE AS' statement. The given below command creates a new table (‘new_table') with the same structure and data as the existing table (‘existing_table'). This method is particularly useful for creating backups or duplicates of tables for testing or data migration purposes.

Using this approach, we can easily replicate a table, ensuring that all the original data is preserved in the new table, allowing for uninterrupted workflow and data integrity.

Syntax:

CREATE TABLE new_table AS 
TABLE existing_table;

Example

In this example, the CREATE TABLE statement creates a new table called employees_backup with the same structure and data as the employees table. After creating the table, data is inserted into the employees table, which will also be present in employees_backup.

Query:

CREATE TABLE employees_backup AS 
TABLE employees;

INSERT INTO employees (id, first_name, last_name, email) 
VALUES 
    (1, 'John', 'Doe', '[email protected]'),
    (2, 'Jane', 'Smith', '[email protected]');

Output

Explanation:

This output shows that both the structure and data of the employees table have been successfully copied into employees_backup.

Copying a Table Structure Without Data

If we need to copy only the table structure without any data, we can add the ‘WITH NO DATA' clause to the ‘CREATE TABLE' statement. This allows us to create a new table that maintains the original table’s column definitions, data types, and constraints while excluding the actual rows of data.

Query:

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

Example

In this example, the CREATE TABLE statement creates a new table called employees_structure with the same column structure as the employees table but does not copy any data, as the WITH NO DATA clause is used.

Query:

CREATE TABLE employees_structure AS 
TABLE employees 
WITH NO DATA;

Output

This command creates a new table named employees_structure with the same structure as the employees table but without any data.

Copying a Table with Partial Data

To copy a table with partial data from an existing table, we can use a SELECT statement with a WHERE clause to specify the rows to be copied. The condition in the WHERE clause defines which rows of the existing table will be copied to the new table.

Syntax:

CREATE TABLE new_table AS 
SELECT *
FROM existing_table
WHERE condition;

Example

This example creates a new table named recent_employees is created by copying only the rows from the employees table where the hire date is after January 1, 2023. The SELECT statement fetches all columns from the employees table, but only for employees hired after the specified date.

Query:

CREATE TABLE recent_employees AS 
SELECT *
FROM employees
WHERE hire_date > '2023-01-01';

Output

Explanation:

The output shows two employees, Mark Taylor and Lucy Brown, who were hired on May 1, 2023, and July 15, 2023, respectively.

Conclusion

PostgreSQL provides powerful and flexible methods for copying tables. We can choose to copy an existing table along with its structure and data, copy only the structure, or copy partial data based on specific conditions. Mastering these techniques will enable us to perform efficient data management, create backups, and streamline our database operations.