Create Table from Another Table in PostgreSQL Database

PostgreSQL allows you to create a new table based on an existing table using the CREATE TABLE ... AS or CREATE TABLE ... LIKE statements. These methods are useful for copying table structure, data, or both. This tutorial explains how to create a table from another table in PostgreSQL, with practical examples and detailed explanations.

Method 1: Using CREATE TABLE … AS

The CREATE TABLE ... AS statement creates a new table from an existing table. You can copy the data, the structure, or a subset of the data using a query.

Syntax:

</>
Copy
CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;

Example: Let’s create a new table named active_employees that contains data for employees who are currently active, based on an existing employees table.

First, create the employees table and insert some sample data:

</>
Copy
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    status VARCHAR(10)
);

INSERT INTO employees (employee_name, department, status)
VALUES 
('Alice', 'HR', 'active'),
('Bob', 'IT', 'inactive'),
('Charlie', 'Finance', 'active');
PostgreSQL - Create Table

Create the active_employees table with only active employees:

</>
Copy
CREATE TABLE active_employees AS
SELECT employee_id, employee_name, department
FROM employees
WHERE status = 'active';
PostgreSQL - Create Table from Another Table

Query the active_employees table to verify the data:

</>
Copy
SELECT * FROM active_employees;

Result:

PostgreSQL - select from Table

Explanation: The CREATE TABLE ... AS statement copies the selected columns and rows from the employees table into the new active_employees table. Only employees with a status of 'active' are included in the new table.

Method 2: Using CREATE TABLE … LIKE

The CREATE TABLE ... LIKE statement creates a new table with the same structure (schema) as an existing table, but without copying the data. This is useful when you want to create an empty table with the same structure as another table.

Syntax:

</>
Copy
CREATE TABLE new_table (LIKE existing_table [INCLUDING ALL | EXCLUDING ALL]);

Example: Create a new table named archived_employees with the same structure as the employees table.

</>
Copy
CREATE TABLE archived_employees (LIKE employees INCLUDING ALL);
PostgreSQL - Create Table from Another Table - Using CREATE TABLE ... LIKE

Explanation: The INCLUDING ALL clause ensures that all constraints, indexes, and default values from the original table are included in the new table.

Insert data into the archived_employees table:

</>
Copy
INSERT INTO archived_employees (employee_id, employee_name, department, status)
VALUES 
(4, 'Eve', 'Marketing', 'inactive');

Query the archived_employees table:

</>
Copy
SELECT * FROM archived_employees;

Result:

Explanation: The new table has the same structure as the employees table but starts out empty. You can insert data into it independently of the original table.

Differences Between CREATE TABLE … AS and CREATE TABLE … LIKE

While both methods create a new table from an existing table, they serve different purposes:

  • CREATE TABLE … AS: Copies both structure and data (or a subset of data) from the existing table. The new table does not retain constraints, indexes, or default values from the original table.
  • CREATE TABLE … LIKE: Copies only the structure (schema) of the existing table. You can choose whether to include constraints, indexes, and default values.

Conclusion

PostgreSQL provides two powerful methods to create a new table from an existing one: CREATE TABLE ... AS and CREATE TABLE ... LIKE. Each method serves unique use cases, whether you need to copy data, replicate structure, or both. By practicing the examples provided, you can effectively use these methods to simplify table creation and data management in PostgreSQL.