Open In App

PostgreSQL – Copy a Table

Last Updated : 24 Jan, 2021
Improve
Improve
Like Article
Like
Save
Share
Report

This article will be focusing on copying an existing table to a new table in PostgreSQL. This might come in handy while creating new tables that would either have the same data or data of the same table with certain operations performed on them.

We will discuss the following 3 cases:

  1. Copy Table with the same structure and data.
  2. Copy Table with the same structure and no data.
  3. Copy Table with the same structure and partial data.

Copy Table with the Same Structure and Data

To copy a table with all its structure and data, use the following query:

Syntax:
CREATE TABLE new_table AS TABLE old_table;

Example:

Let’s add a table with columns id, first_name, last_name, and email to the database:

CREATE TABLE students(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR,
   last_name VARCHAR,
   email VARCHAR UNIQUE
);

Let’s insert some data into our students table:

INSERT INTO students(first_name, last_name, email)
VALUES('Virender', 'Sehwag', 'virender.sehwag@gfg.com'),
    ('Hardik', 'Pandiya', 'hardik.Pandiya@gfg.com');

Now check the data in the table:

SELECT * FROM students;

If everything works fine, the output will as below:

Now copy the students table to a new table named copy_students table.

CREATE TABLE copy_students AS TABLE students;

The above query will create a new table named copy_students with the same structure and data as the students table.

Now check the data of the copy_students table:

SELECT * FROM copy_students;

Output:

Copy Table with the Same Structure and No Data

WITH NO DATA clause is used to copy a table structure without the data using the below query:

Syntax:
CREATE TABLE new_table AS TABLE old_table WITH NO DATA;

Example:

Let’s use the students table that we created before:

CREATE TABLE without_data_students AS TABLE students WITH NO DATA;

Execute the above query to get the table without_data_students with the same structure as students with no data.

SELECT * FROM without_data_students;

Output:

Copy Table with the Same Structure and Partial Data

The below query can be used to copy a table according to a specified condition:

Syntax:
CREATE TABLE new_table AS TABLE old_table WHERE condition;

Example: 

Let’s insert some more rows into the students table:

INSERT INTO students(first_name, last_name, email)
VALUES('Shreyas', 'Iyer', 'shreyas.iyer@gfg.com'),
    ('Rishabh', 'Pant', 'rishabh.pant@gfg.com');

Now the students table will have the following data:

SELECT * FROM students;

Now the students table will look like this:

Let’s create a table copy_partial_students with id 1 and 3 only:

CREATE TABLE copy_partial_students AS SELECT * FROM students WHERE  id IN (1, 3);

Instead of  *, you can also define the column names that you want to copy. The result table columns will have the names and data types as same as the output columns of the SELECT clause.

Now check the data of the copy_partial_students table:

SELECT * FROM copy_partial_students;

Output:


Previous Article
Next Article

Similar Reads

PostgreSQL - Export PostgreSQL Table to CSV file
In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine. For Server-Side Export: Use the below syntax to copy a PostgreSQL table from the server itself: Syntax: COPY Table_Name TO 'Path/filename.csv' CSV HEADER; Note: If you have permissio
2 min read
PostgreSQL - Copy Table
PostgreSQL allows copying an existing table including the table structure and data by using various forms of PostgreSQL copy table statement. To copy a table completely, including both table structure and data use the below statement. Syntax: CREATE TABLE new_table AS TABLE existing_table; To copy a table structure without data, users need to add t
2 min read
PostgreSQL - Connect To PostgreSQL Database Server in Python
The psycopg database adapter is used to connect with PostgreSQL database server through python. Installing psycopg: First, use the following command line from the terminal: pip install psycopg If you have downloaded the source package into your computer, you can use the setup.py as follows: python setup.py build sudo python setup.py installCreate a
4 min read
PostgreSQL - Installing PostgreSQL Without Admin Rights on Windows
If you are a part of a corporation, it is highly unlikely that you have the admin privileges to install any external software. But the curious souls that all software developers are, in this article, we will see the detailed process of installation of PostgreSQL without having administrator rights on our Windows machine. Installation: Follow the be
4 min read
PostgreSQL - Copy Database
In this article, we will discuss the process of copying a PostgreSQL database on the same server or from a server to another. PostgreSQL copy database within the same server: If a situation arises where one needs to copy a PostgreSQL database within a database server for testing purposes. PostgreSQL makes it simple to do so using the CREATE DATABAS
2 min read
PostgreSQL - Size of a Table
In this article, we will look into the function that is used to get the size of the PostgreSQL database table. In this article, we will be using a sample database for reference which is described here and can be downloaded from here. The pg_relation_size() function is used to get the size of a table. Syntax: select pg_relation_size('table_name'); E
2 min read
PostgreSQL - Import CSV File Into Table
In this article, we will discuss the process of importing a .csv file into a PostgreSQL table. To do so we will require a table which can be obtained using the below command: CREATE TABLE persons ( id serial NOT NULL, first_name character varying(50), last_name character varying(50), dob date, email character varying(255), CONSTRAINT persons_pkey P
2 min read
PostgreSQL - CREATE TABLE
In PostgreSQL, the CREATE TABLE clause as the name suggests is used to create new tables. Syntax: CREATE TABLE table_name ( column_name TYPE column_constraint, table_constraint table_constraint ) INHERITS existing_table_name; Let's analyze the syntax above: First, you define the name of the new table after the CREATE TABLE clause. The TEMPORARY key
3 min read
PostgreSQL - Temporary Table
A temporary table, as the name implies, is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables at the end of a session or a transaction. Syntax: CREATE TEMPORARY TABLE temp_table( ... ); or, CREATE TEMP TABLE temp_table( ... ); A temporary table is visible only to the session t
1 min read
PostgreSQL - Temporary table name
In PostgreSQL, A temporary table can have the same name as of a permanent table, even though it is not recommended. When the user creates a temporary table with the same name as a permanent table, it cannot access the permanent table until the temporary table is removed. Example: First, create a table named customers: CREATE TABLE customers(id SERI
2 min read
Article Tags :