Use Of Create Table As Command In PostgreSQL

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.

Syntax

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
AS 
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
AS 
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
AS 
SELECT first_name, last_name, email FROM employee WHERE 1=0;

Output : 

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.

Thanks

Sanjay Saini