PostgreSQL provides us with several options when it comes to cloning a table. Below are six methods we can use to clone a table in Postgres. The method we use will depend on things like, whether or not we want to transfer the data, indexes, etc.

The CREATE TABLE ... AS SELECT Statement

We can use the CREATE TABLE ... AS SELECT statement to copy the table and its data, but not its indexes.

Suppose we have a table called pets. We can use the following statement to copy that table, along with its data:

CREATE TABLE pets2 AS
(SELECT * FROM pets);

Here, I copied a table called pets and named the new table pets2. I also copied all data from the original table to its clone.

If we don’t want to insert the data, we can use the WITH NO DATA clause:

CREATE TABLE pets2 AS
(SELECT * FROM pets)
WITH NO DATA;

Alternatively, we can modify the query to return no results. For example:

CREATE TABLE pets2 AS 
(SELECT * FROM pets LIMIT 0);

As mentioned, this method doesn’t copy any indexes or constraints associated with the table (such as primary keys, foreign keys or NOT NULL constraints).

The CREATE TABLE ... LIKE Statement

PostgreSQL also has a CREATE TABLE ... LIKE option:

CREATE TABLE pets3 (LIKE pets);

This option doesn’t copy the data but it does copy any NOT NULL constraints associated with the table.

The CREATE TABLE ... AS TABLE Statement

Another way to copy the table and its data is to use the CREATE TABLE ... AS TABLE statement:

CREATE TABLE pets4 AS TABLE pets;

This created a table called pets4, based on the pets table, and copied the data from pets to pets4.

Note that this didn’t copy the indexes, foreign keys, or NOT NULL constraints.

By default, all data is copied to the new table. If we don’t want the data, we can use WITH NO DATA:

CREATE TABLE pets4 AS TABLE pets WITH NO DATA;

Note that the SQL standard actually requires either WITH DATA or WITH NO DATA to be included in the statement, but PostgreSQL makes this optional.

The SELECT INTO Statement

We can use the SELECT INTO statement to copy the table and its data, but not its indexes:

SELECT * INTO pets5 FROM pets;

Note that the Postgres documentation recommends that we use the CREATE TABLE AS option instead of SELECT INTO if we want a similar result. That’s because this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Also, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

The pg_dump Utility

Another option is to use the pg_dump utility. We can use this utility to generate the SQL CREATE TABLE required to recreate the table, as well as any indexes, constraints, etc. If we want to clone the table, we can then change the names before running the code.

This utility is run from its own terminal window.

Example:

pg_dump -t 'public.pets' --schema-only barney

In this example, pets is the table and barney is the name of the database.

This dumps out the SQL code that we can use to recreate the table. We can therefore update the name of the table and indexes/constraints in order to clone the table.

See the PostgreSQL documentation for more information about this utility.

The INHERITS Option

PostgreSQL also supports the INHERIT option. This is a bit different to the other options. With this option, any schema modifications to the parent table/s normally propagate to children as well, and by default the data of the child table is included in scans of the parent table/s.

Also, this option allows us to define extra columns that aren’t in the parent table/s.

Example:

CREATE TABLE pets6 (
    color varchar(60) NOT NULL
) 
INHERITS ( pets );

Here, we explicitly defined one column, and we inherited the rest of the columns from the pets table.

We can use the psql \d command to look at the new table’s definition:

\d pets6

Result:

                        Table "public.pets6"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 petid     | integer               |           | not null | 
 pettypeid | integer               |           | not null | 
 ownerid   | integer               |           | not null | 
 petname   | character varying(60) |           | not null | 
 dob       | date                  |           |          | 
 color     | character varying(60) |           | not null | 
Inherits: pets