In this Section we will see how to create the copy of table in PostgreSQL without data i.e. to create the table structure, with example. Let’s see
- How to create the copy of table in PostgreSQL without data.
- How to create the copy of table in PostgreSQL with partial data (with constraints)
- How to create the copy of table structure in PostgreSQL i.e. without data
With an example for each
We will be using employees table.
Create Table Structure in PostgreSQL using like:
We will be creating the table structure of the employee table, i.e. we will be creating a new table with the same structure as the original table but without copying the data. We will use everything like structure, constraints, defaults, and indexes from original_table to new_table.
CREATE TABLE employees_new (LIKE employees INCLUDING ALL)
Output:
Create only basic Table Structure in PostgreSQL:
We will be creating only the basic table structure of the employee table without constraints, indexes, and defaults
CREATE TABLE employees_new AS TABLE employees with NO DATA;
Output:
Create only basic Table Structure in PostgreSQL using like (without data and without constraints, structures and index):
We will be creating only the basic table structure of the employee table without constraints, indexes, and defaults .i.e. we will be creating a new table with the same structure as the original table but without copying the data and without constraints like primary key, index and defaults .
CREATE TABLE employees_new (LIKE employees)
Output:
Create Table Structure in PostgreSQL by copying structures and constraints:
We will be creating only the basic table structure of the employee table with constraints .i.e. we will be creating a new table with the same structure as the original table but without copying the data and with constraints like primary key, not null etc
CREATE TABLE employees_new (LIKE employees INCLUDING CONSTRAINTS)
Output:
Create the copy of table in PostgreSQL with partial data
We will be creating the partial data from PostgreSQL table on conditions
CREATE TABLE employees_new_partial AS SELECT * FROM employees WHERE zip=98225;
The partial copy of the table with condition zip =98225 is created and named as “employees_new_partial“
