5 ways to copy tables in PostgreSQL

PostgreSQL provides a number of different ways to replicate tables, and their differences are whether the replicate table structure or data is required.

CREATE TABLE AS SELECT statement

The CREATE TABLE AS SELECT statement can be used to copy table structures and data, but does not copy indexes.

We can use the following statement to copy a new table emp2 based on employee, including the data in the table:

CREATE TABLE emp2 
AS
SELECT * FROM employee;

If you only want to copy the table structure and not copy the data, you can add the WITH NO DATA clause:

CREATE TABLE emp2 
AS
SELECT * FROM employee
WITH NO DATA;

Or you can use a query statement that does not return any results, for example:

CREATE TABLE emp2 
AS
SELECT * FROM employee
WHERE FALSE;

This copy method does not create any indexes or constraints, such as primary keys, foreign keys, and NOT NULL constraints.

CREATE TABLE LIKE statement

The CREATE TABLE LIKE statement can also be used to copy table structures:

CREATE TABLE emp3 
(LIKE employee);

Parentheses in the syntax are essential, and this method does not copy the data, but copies the NOT NULL constraints of the field.

CREATE TABLE AS TABLE statement

The CREATE TABLE AS TABLE statement can copy table structures and data, for example:

CREATE TABLE emp4 
AS 
TABLE employee
WITH NO DATA;

This syntax does not copy indexes, foreign keys, non-empty constraints, etc.

If you do not need to copy data, you can use the WITH NO DATA clause:

CREATE TABLE emp4 
AS 
TABLE employee
WITH NO DATA;

SELECT INTO statement

The SELECT INTO statement can copy table structures and data, but does not include indexes, etc. For example:

SELECT * INTO emp5 FROM employee;

PostgreSQL recommends using CREATE TABLE AS instead of SELECT INTO statement to achieve similar effects, because the former is more applicable and has more complete functions.

CREATE TABLE INHERITS statement

PostgreSQL supports the INHERIT clause of the CREATE TABLE statement, which is used to inherit table structures. This method of copying a table is different from other methods, and any modification to the parent table will usually automatically modify the child table.

In addition, this method can also define additional fields for the subtable. For example:

CREATE TABLE emp5 (
    notes text NOT NULL
) 
INHERITS ( employee );

Where notes is the field we define additionally, and other fields inherit employees.

Use the psql \d command to view the structure of emp5 as follows:

\d emp5

                        Table "public.emp5"
  Column   |          Type          | Collation | Nullable | Default
-----------+------------------------+-----------+----------+---------
 emp_id    | integer                |           | not null |
 emp_name  | character varying(50)  |           | not null |
 sex       | character varying(10)  |           | not null |
 dept_id   | integer                |           | not null |
 manager   | integer                |           |          |
 hire_date | date                   |           | not null |
 job_id    | integer                |           | not null |
 salary    | numeric(8,2)           |           | not null |
 bonus     | numeric(8,2)           |           |          |
 email     | character varying(100) |           | not null |
 notes     | text                   |           | not null |
Check constraints:
    "ck_emp_salary" CHECK (salary > 0::numeric)
    "ck_emp_sex" CHECK (sex::text = ANY (ARRAY['male'::character varying, 'female'::character varying]::text[]))
Inherits: employee

This is the end of this article about 5 ways to copy PostgreSQL PostgreSQL tables. For more related PostgreSQL copy table content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!