In PostgreSQL, the copy table functionality is a powerful feature that allows us to efficiently duplicate existing tables, including their structure and data. This capability is essential for various database management tasks such as backing up data, migrating tables, or testing modifications in a separate environment
In this article, we will explain different methods to copy a table in PostgreSQL, providing detailed syntax, practical examples, and outputs to ensure that we can effectively utilize this feature in our projects.
Copying a Table Completely (Structure and Data)
To copy an entire table, including both its structure and data, use the ‘CREATE TABLE AS' statement. The given below command creates a new table (‘new_table') with the same structure and data as the existing table (‘existing_table'). This method is particularly useful for creating backups or duplicates of tables for testing or data migration purposes.
Using this approach, we can easily replicate a table, ensuring that all the original data is preserved in the new table, allowing for uninterrupted workflow and data integrity.
Syntax:
CREATE TABLE new_table AS
TABLE existing_table;
Example
In this example, the CREATE TABLE statement creates a new table called employees_backup with the same structure and data as the employees table. After creating the table, data is inserted into the employees table, which will also be present in employees_backup.
Query:
CREATE TABLE employees_backup AS
TABLE employees;
INSERT INTO employees (id, first_name, last_name, email)
VALUES
(1, 'John', 'Doe', '[email protected]'),
(2, 'Jane', 'Smith', '[email protected]');
Output
Explanation:
This output shows that both the structure and data of the employees table have been successfully copied into employees_backup.
Copying a Table Structure Without Data
If we need to copy only the table structure without any data, we can add the ‘WITH NO DATA' clause to the ‘CREATE TABLE' statement. This allows us to create a new table that maintains the original table’s column definitions, data types, and constraints while excluding the actual rows of data.
Query:
CREATE TABLE new_table AS
TABLE existing_table
WITH NO DATA;
Example
In this example, the CREATE TABLE statement creates a new table called employees_structure with the same column structure as the employees table but does not copy any data, as the WITH NO DATA clause is used.
Query:
CREATE TABLE employees_structure AS
TABLE employees
WITH NO DATA;
Output
This command creates a new table named employees_structure with the same structure as the employees table but without any data.
Copying a Table with Partial Data
To copy a table with partial data from an existing table, we can use a SELECT statement with a WHERE clause to specify the rows to be copied. The condition in the WHERE clause defines which rows of the existing table will be copied to the new table.
Syntax:
CREATE TABLE new_table AS
SELECT *
FROM existing_table
WHERE condition;
Example
This example creates a new table named recent_employees is created by copying only the rows from the employees table where the hire date is after January 1, 2023. The SELECT statement fetches all columns from the employees table, but only for employees hired after the specified date.
Query:
CREATE TABLE recent_employees AS
SELECT *
FROM employees
WHERE hire_date > '2023-01-01';
Output
| id | first_name | last_name | hire_date |
| 3 | Mark | Taylor | 2023-05-01 |
| 4 | Lucy | Brown | 2023-07-15 |
Explanation:
The output shows two employees, Mark Taylor and Lucy Brown, who were hired on May 1, 2023, and July 15, 2023, respectively.
Conclusion
PostgreSQL provides powerful and flexible methods for copying tables. We can choose to copy an existing table along with its structure and data, copy only the structure, or copy partial data based on specific conditions. Mastering these techniques will enable us to perform efficient data management, create backups, and streamline our database operations.
Similar Reads
PostgreSQL - Copy a Table
This article will focus 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. Ways to Copy a TableWe will discuss the following 3 cases to
3 min read
PostgreSQL - DROP TABLE
In PostgreSQL, the DROP TABLE statement is a powerful and permanent command used to delete one or more tables from a database. Since this operation cannot be undone, it is essential to understand how to use it safely and to be aware of its options to prevent accidental data loss. In this article, we
5 min read
PostgreSQL - CREATE TABLE
In PostgreSQL, the CREATE TABLE statement is used to define a new table within a database. It allows us to specify the table's structure, including column names, data types, and constraints, ensuring data integrity and consistency. Understanding the PostgreSQL table creation process is essential for
5 min read
SQL Server Copy Table
Copying or replicating tables is one of the crucial functions of database management systems. Copy table is a crucial option to create table data backups or to create duplicate data from a table to another table with few columns or some of the data for various purposes. In this article, We will lear
4 min read
PL/SQL Copy Table
Copying tables in PL/SQL is a common task in database management. It involves duplicating the structure and data of an existing table into a new one. This operation can be accomplished using the CREATE TABLE AS SELECT statement, which allows for the creation of a new table based on the result set of
3 min read
PostgreSQL - ALTER TABLE
In PostgreSQL, the ALTER TABLE statement is a powerful and essential tool that allows us to modify the structure of an existing table to meet evolving database needs. With PostgreSQL ALTER TABLE, we can perform various modifications on the table without disrupting the ongoing operations of our datab
6 min read
PostgreSQL - Describe Table
Unlike MySQL, PostgreSQL does not have a 'DESCRIBE' statement to view table column details. However, PostgreSQL provides several methods to access information about table columns. In this article, we'll learn two effective ways to Describe Tables in PostgreSQL. 1. Using the pSQL shellThe 'psql' comm
2 min read
PostgreSQL - CREATE TABLE AS
The CREATE TABLE AS statement in PostgreSQL is a powerful tool used to create a new table and populate it with data returned by a query. This functionality allows you to generate tables on the fly based on query results, which can be very useful for reporting, analysis, and other tasks. Let us bette
3 min read
PostgreSQL - Show Tables
In PostgreSQL, viewing tables is an essential task for managing and organizing our database. Although PostgreSQL does not support the SHOW TABLES command like MySQL, it offers alternative commands like \dt in the psql tool, which helps users list all tables within a specific database In this article
4 min read
PostgreSQL - Rename Table
Renaming a table in PostgreSQL is a common task that can be quickly done using the RENAME clause in combination with the ALTER TABLE statement. This article will walk you through the process of renaming an existing table in PostgreSQL, explaining the syntax, and providing a detailed example. SyntaxA
2 min read