Use Of Create Table As Command In PostgreSQL

Posted By : Sanjay Saini | 31-Oct-2018

Hi Guys,

In this blog, I will show you how to create a copy of table with data or without data.Sometime we need to create exact copy of some tables with data and relations.


There can be various ways of achieving this task such as –


1. You can manually view the structure of the table using DESC command and then write a create table DML statement and create the table.

2. You can use the Export and Import service for creating the duplicate copy and many more.


I say, why to take the pain of preparing all these hard work and lose your precious time when you have a simple and easy-to-use tool at hand. PostgreSQL gives an easy way/command of creating a copy of a table either with the data or without the data. It is called “Create Table As” command.


This is a DDL command which authorizes you to produce a correct copy of a record of your DB with or without the data.



CREATE TABLE new_table AS 
  SELECT expressions
  FROM existing_tables
  [WHERE conditions];

Parameters or Arguments :

table_name - The name of the table that you want to build.

expressions - The columns from the existing_tables that you would want to be created in the new_table.The column descriptions from those columns listed will be assigned to the new_table that you build.

existing_tables - The present tables from which to copy the column descriptions and the associated records (as per the WHERE clause).

WHERE conditions - Optional. The requirements that must be satisfied for the records to be drawn to the new_table.


let's suppose an employee table exists in the database and we have used this in the example.


1.Copy Table Structure with Data Suppose you require to make an accurate copy of employee table and by exact copy, I mean copying the entire structure along with the data of employee table. Copying the exact structure and data of the employee table can be done by writing a very easy Create table statement.

That statement will be

CREATE TABLE employee_copy
SELECT * FROM employee;

Successful execution of the above command will create the employees_copy which will be having the same structure and data as of the Employee table.


2. Copy particular columns of a table along with their data. Now assume you need to copy the only first_name, last_name or email columns with data of employees table, in this case, CREATE TABLE statement will be.

CREATE TABLE employee_copy
SELECT first_name, last_name, email FROM employee;

Successful achievement of the above process will create the table employees_copy this time with only column first_name, last_name, and email and the data.


3. Copy only the structure of the table without the data. Sometimes you might need to draw only the structure of the table sans the data. In this case, what you can do is draw the entire table as given in the above examples and then truncate it succeeding but this will need two parts or two distinct commands to perform for a single job.Such a waste of time & resources when you can perform the same just by writing a single “Create Table As command”. 


Let’s consider a pattern of how it works :

CREATE TABLE employee_copy
SELECT first_name, last_name, email FROM employee WHERE 1=0;

Output : 

problem in Loading Image

The Create Table command will remain the same as shown in the above examples. You just need to add a where clause which will never set to be true or always false for example where clause such as ‘F’ = ‘M’ or 0=1.


Sanjay Saini

About Author

Author Image
Sanjay Saini

Sanjay has been working on web application development using frameworks like Java, groovy and grails. He loves listening to music , playing games and going out with friends in free time.

Request for Proposal

Name is required

Comment is required

Sending message..