PostgreSQL - CREATE TABLE

The CREATE TABLE statement in PostgreSQL is used to create a new table in a database by defining its structure.

• Defines column names and their data types.
• Supports constraints to maintain data integrity
• Organizes data in a structured format for storing records.

Syntax

CREATE TABLE table_name (
    column_1 data_type [constraint],
    column_2 data_type [constraint],
    ...
    column_n data_type [constraint]
);

Where:

• table_name: The name of the table to be created.
• column_1, column_2, ... : The names of the table columns.
• data_type: Specifies the type of data each column can store.
• constraint (optional): Defines rules such as PRIMARY KEY, NOT NULL, UNIQUE, CHECK or DEFAULT.

Create an employee Table

First, create an employee table with columns for employee details, including a primary key, a NOT NULL constraint, a CHECK constraint and a default joining date.

Query:

CREATE TABLE employee ( employee_id INT PRIMARY KEY,    
first_name VARCHAR(50) NOT NULL,    
last_name VARCHAR(50) NOT NULL,    
department VARCHAR(50),   
 salary NUMERIC(10,2) CHECK (salary >= 0),   
 joining_date DATE DEFAULT CURRENT_DATE);

Output:

Inserting Data into the employee Table

After creating the table, use the INSERT INTO statement to add records.

Query:

INSERT INTO employee (employee_id, first_name, last_name, department, salary)
VALUES
(101, 'John', 'Smith', 'HR', 45000.00),
(102, 'Emma', 'Johnson', 'Finance', 55000.00),
(103, 'Liam', 'Brown', 'IT', 68000.00)
,(104, 'Olivia', 'Davis', 'Marketing', 52000.00),
(105, 'Noah', 'Wilson', 'Sales', 48000.00);

Output:

Creating a Table from an Existing Table

PostgreSQL allows you to create a new table from an existing table using the CREATE TABLE AS statement. This copies the selected columns and their data into a new table.

Syntax:

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table WHERE condition;

Example: Create a Backup of the employee Table

The following query creates a new table named employee_backup containing all records from the existing employee table.

Query:

CREATE TABLE employee_backup AS
SELECT * FROM employee;

Output: