Article Overview

In this article, we will discuss the 'COPY' functionality of PostgreSQL database which is one of the most important and often used features in practical world. There are two variants of 'COPY' statement, copy a database and copy a table. We will discuss both in this article.

First we will discuss the process of copying a PostgreSQL database on the same server or from a server to another and then we will talk about various ways of copying an existing table to a new table in PostgreSQL.

Copy database within the same server

At times it is required to create an exact copy of an existing database within the same server for development or testing purposes. PostgreSQL helps to achieve the same using the CREATE DATABASE statement as shown below:

Basic Syntax:

CREATE DATABASE target_database 
WITH TEMPLATE source_database;

This statement copies the source_database to the target_database. Let us take a look at the below query query to explain the scenario.

CREATE DATABASE demo2 
WITH TEMPLATE demo;

The above query instructs PostgreSQL engine to create a new database named 'demo2' using the template of the existing database 'demo'. Database is copied keeping its internal structure intact which means all schemas and their tables and other objects are copied and made available in the new database.

The images below explain the scenario in detail.

Before copying 'demo' database. The 'demo' database contains a couple of schemas as shown above in the 1st image.

After executing the COPY query, 'demo2' database is created with the same structure.

Copy database from a server to another

The most efficient way to copy a database from one server to another is explained below. There are 4 steps in total. Lets us take a look at the steps and their respective commands.

1. Dump the source database into a SQL file.

pg_dump -U postgres -d sourcedb -f sourcedb.sql

2. Copy the file to target server via putty or any other file transfer process.

3. Create a fresh database in target server.

CREATE DATABASE targetdb;

4. Restore the dump file on the target server.

psql -U postgres -d targetdb -f sourcedb.sql

PostgreSQL – Copy Table

The copy table functionality helps to copy an existing table with all its entirety. Let us look at the basic syntaxes of all the possible ways to do so:

Copy table along with its data

CREATE TABLE new_table AS TABLE existing_table;

Example

This image depicts the current scenario of the database. Next we will execute the below COPY query to create the new table from the existing table 'books'.

CREATE TABLE books2 AS TABLE books;

From this image, we can see a new table named 'books2' is created which is having the same structure and same set of data.

Copy table without its data

It is also possible to copy only the table structure and not its data. This scenario is particularly important for testing the application across different environments.

CREATE TABLE new_table AS TABLE existing_table 
WITH NO DATA;

Let us create a new table named 'books3' from the existing table 'books' to better understand the scenario.

CREATE TABLE books3 AS TABLE books 
WITH NO DATA;

The important point to observe here is the response from the PostgreSQL engine post table creation.  In the previous scenario, the response was the text 'SELECT 2' which indicated 2 rows of data were inserted in the new table and in this example the system's response is a different text 'CREATE TABLE AS' which means table is created but with no data.

Copy table with partial data

In this last scenario we will see how it is also possible to create a new table from an existing one with partial data. Here, an inner query is used along with the 'WHERE' clause to achieve the desired outcome.

CREATE TABLE new_table AS 
SELECT * FROM existing_table WHERE condition;

Example

CREATE TABLE books4 AS 
SELECT * FROM books WHERE id=19;

As explained above, we see the new table is created but with only 1 row of data based on the input passed in the where clause.

Conclusion

In this article we learned about the various ways of copying a database or a table within or across servers. We hope this article will help work more effectively and efficiently with PostgreSQL.