Efficient Methods for Copying Table Data in PostgreSQL: From COPY Command to CREATE TABLE AS

Introduction

In database management and data migration, it is often necessary to copy data from one table to another. Many developers first consider PostgreSQL's COPY command, which is primarily used for exporting data to external files or importing from files into tables. However, the COPY command itself does not directly support table-to-table data copying. This article systematically introduces several effective methods for achieving table data replication within PostgreSQL and analyzes their advantages and disadvantages.

Core Replication Methods

PostgreSQL provides multiple built-in mechanisms for copying table data without relying on external files or complex data pipelines. The following are three of the most commonly used methods:

Method 1: Using the CREATE TABLE AS Statement

This is the simplest and most direct approach, creating a new table and copying data in a single SQL statement. The basic syntax is as follows:

CREATE TABLE mycopy AS
SELECT * FROM mytable;

This method creates a new table named mycopy with a structure based on the result of the SELECT query and immediately populates it with all data from mytable. Note that the new table does not automatically inherit additional attributes such as indexes, constraints, or default values from the original table; only the table structure and data are copied.

Method 2: Combining the LIKE Clause with INSERT INTO SELECT

When it is necessary to fully replicate all attributes of the original table (including indexes, constraints, etc.), a two-step approach can be employed:

CREATE TABLE mycopy (LIKE mytable INCLUDING ALL);

INSERT INTO mycopy
SELECT * FROM mytable;

The first step uses the LIKE clause to create the new table, with the INCLUDING ALL option ensuring that all table attributes are copied. The second step uses the INSERT INTO ... SELECT statement to copy the data. Although this method requires two statements, it provides a more complete replication of the table structure.

Method 3: Selective Column Replication

In practical applications, it may be necessary to copy only specific columns or to reorder columns. This can be achieved by explicitly specifying column names:

INSERT INTO mycopy(colA, colB)
SELECT col1, col2 FROM mytable;

This method is particularly useful when table structures are not identical or when data transformation is required. Through carefully designed SELECT clauses, operations such as data cleaning and type conversion can be performed during the copying process.

Performance Analysis and Best Practices

From a performance perspective, CREATE TABLE AS is generally the fastest method, as it combines table creation and data insertion into a single operation. The two-step approach (first CREATE TABLE, then INSERT) is more advantageous when replicating complete table attributes but may be slightly slower.

For large tables, it is recommended to perform the copy operation within a transaction block to ensure data consistency. Additionally, consider creating appropriate indexes on the target table, preferably after data insertion is complete, to improve replication performance.

Although tools like pg_dump and pg_restore can also be used for table data copying, they are more suitable for cross-database scenarios or backup and recovery. For table replication within the same database, the SQL methods described above are typically simpler and more efficient.

Conclusion

PostgreSQL offers flexible mechanisms for table data replication, allowing developers to choose the most suitable method based on specific needs. For simple full-table copying, CREATE TABLE AS is the optimal choice; when complete replication of table attributes is required, the LIKE clause combined with INSERT INTO SELECT should be used; and selective column replication provides the greatest flexibility. Understanding the differences and applicable scenarios of these methods will help optimize data management processes and improve development efficiency.