Copy Tables in PostgreSQL

This article describes several ways to copy tables in PostgreSQL

In PostgreSQL, you can copy a table to a new table in several ways:

  1. Use the CREATE TABLE ... AS TABLE ... statement to copy a table.
  2. Use the CREATE TABLE ... AS SELECT ... statement to copy a table.
  3. Use the SELECT ... INTO ... statement to copy a table.

Use the CREATE TABLE ... AS TABLE ... statement to copy a table

To copy an existing table table_name into a new table new_table, including table structure and data, use the following statement:

CREATE TABLE new_table
AS TABLE table_name;

To copy only the table structure, not the data, add a WITH NO DATA clause to the above CREATE TABLE statement as follows:

CREATE TABLE new_table
AS TABLE table_name
WITH NO DATA;

Use the CREATE TABLE ... AS SELECT ... statement to copy a table

You can also use the CREATE TABLE ... AS SELECT ... statement to copy a table. This method can copy part of the data to the new table.

To copy an existing table table_name into a new table new_table, including table structure and data, use the following statement:

CREATE TABLE new_table AS
SELECT * FROM table_name;

If you only need to copy some of rows that meet the condition, add a WHERE clause to the SELECT statement as follows:

CREATE TABLE new_table AS
SELECT * FROM table_name
WHERE contidion;

If you only need to copy some of the columns to the new table, specify the list of columns to to copied in the SELECT statement as follows:

CREATE TABLE new_table AS
SELECT column1, column2, ... FROM table_name
WHERE contidion;

If you only need to replicate the table structure, use the following WHERE clause:

CREATE TABLE new_table AS
SELECT * FROM table_name
WHERE 1 = 2;

Here an always false condition is used in the WHERE clause.

Use the SELECT ... INTO ... statement to copy a table

To copy an existing table table_name to a new table new_table using the SELECT INTO statement, use the following syntax:

SELECT *
INTO new_table
FROM table_name

If you only need to copy some rows that meet the condition, add the WHERE clause as follows:

SELECT *
INTO new_table
FROM table_name
WHERE contidion;

If you only need to copy some of the columns to the new table, specify the list of columns to be copied in the SELECT statement as follows:

SELECT column1, column2, ...
INTO new_table
FROM table_name
WHERE contidion;

Conclusion

This article describes several ways to copy tables in PostgreSQL. Note that these methods can only copy the definition and data of the column, and cannot copy the index.