How to duplicate a table in PostgreSQL

Copying a database table (or duplicating it, for that matter) is one of those basic operations that can be performed for a variety of reasons. Let's have an overview of the most common situations in which you need to copy PostgreSQL tables—and the methods of copying them.

We will illustrate those methods using dbForge Studio for PostgreSQL, an intuitive and feature-rich IDE whose capabilities include SQL coding assistance and formatting, data management and analysis, database comparison and synchronization, query optimization, test data generation, and much more.

Methods of copying tables in PostgreSQL

The easiest way to copy a PostgreSQL table is to use an appropriate query, which mostly depends on whether you are copying into a new or an existing table. That's what we will explore below, covering the following cases:

How to copy a table in PostgreSQL
How to copy PostgreSQL table structure only
How to copy table data to another table
How to copy a table from one database to another
An example of copying a PostgreSQL table

How to copy a table in PostgreSQL

The simplest method of copying a complete PostgreSQL table, including both the structure and the data it contains, is to use the CREATE TABLE ... AS statement in the following way.

CREATE TABLE new_table AS
TABLE source_table;

If you need to filter out some of the contained data, you can do it using a condition expressed in the WHERE clause.

CREATE TABLE new_table AS
SELECT
*
FROM
    source_table
WHERE
    condition;

For instance, the condition specified in the WHERE clause can indicate the rows of the source table that will be copied to the new table. Also note that while the aforementioned statements copy the table structure and data, they do not copy the indexes and constraints of the source table.

How to copy PostgreSQL table structure only

If you need to copy a PostgreSQL table structure without data, simply add the WITH NO DATA clause to your CREATE TABLE ... AS statement.

CREATE TABLE new_table AS
TABLE source_table
WITH NO DATA;

How to copy PostgreSQL table structure only

How to copy table data to another table

To copy data from a source table to a target table, you may use the INSERT INTO statement, having specified the columns to be copied.

INSERT INTO target_table
SELECT column1, column2, column3 ... columnN
FROM source_table;

This method works perfectly well if you have already copied the table structure, and now you only need to copy data.

In case you want to duplicate all rows from the source table, you can simply select them using the following syntax:

INSERT INTO target_table
SELECT * FROM source_table;

Duplicate all rows from one table to another

How to copy a table from one database to another

You can just as easily copy a table to another PostgreSQL database with the same previously shown syntax. You only need to make sure you provide the source and target database names as table prefixes. This is what it looks like if you need to create a new table that will be a duplicate of your source table.

CREATE TABLE target_database.new_table AS
SELECT * FROM source_database.source_table;

And if you need to copy to an existing table, you might as well use the previously mentioned INSERT INTO statement.

INSERT INTO target_database.target_table
SELECT * FROM source_database.source_table;

How to copy a table from one database to another

An example of copying a PostgreSQL table

Let's take the simplest example. We need to take an existing table with customers and create a new one excluding the first 100 entries. For that purpose, we can use the CREATE TABLE statement, where we will set a WHERE condition to exclude the first 100 records from the customerid column. It looks as follows.

CREATE TABLE new_customers AS
SELECT
*
FROM
    customers
WHERE
    customerid > 100;

Once we execute this query and refresh our Database Explorer, we'll see the new_customers table on the list. And if we run a simple SELECT query, we will see that the new table begins with customerid that equals 101, just as we required. You can see what it looks like in the screenshot.

An example of copying a table to a new table

Conclusion

In this article, we have shown you the easiest ways to duplicate tables in PostgreSQL; for that purpose, we have applied dbForge Studio for PostgreSQL, our robust IDE that packs a selection of the most vital features for database development and management. It is available for a free 30-day trial, so feel free to download it and see it in action!