PostgreSQL CREATE TABLE

PostgreSQL CREATE TABLE Statement

The CREATE TABLE statement in PostgreSQL is used to define a new table in a database. You must specify the table name, columns, data types, and constraints.

1. Basic Syntax

CREATE TABLE table_name (
    column1 data_type constraints,
    column2 data_type constraints,
    ...
);

• table_name → The name of the table.
• column1, column2, ... → Column names.
• data_type → Specifies the type of data allowed (e.g., INTEGER, VARCHAR(255), DATE).
• constraints → Define rules for the columns (e.g., PRIMARY KEY, NOT NULL, UNIQUE).

2. Creating a Simple Table

Example: Create an employees Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2),
    hire_date DATE DEFAULT CURRENT_DATE
);

🔹 Breakdown:

• id SERIAL PRIMARY KEY → Auto-incrementing ID.
• first_name VARCHAR(50) NOT NULL → Cannot be NULL.
• email VARCHAR(100) UNIQUE → Ensures no duplicate emails.
• salary DECIMAL(10,2) → Stores salary with 2 decimal places.
• hire_date DATE DEFAULT CURRENT_DATE → Default value is today’s date.

View Table Structure

After creating the table, check its structure:

\d employees

3. Adding Table Constraints

Constraints enforce rules on data.

Common Constraints

Constraint	Description
PRIMARY KEY	Uniquely identifies each row.
NOT NULL	A column cannot be empty.
UNIQUE	Ensures no duplicate values.
CHECK	Adds a condition to the column.
DEFAULT	Sets a default value if not provided.
FOREIGN KEY	Establishes a relationship with another table.

Example: Table with Constraints

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2) CHECK (salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE,
    department_id INT REFERENCES departments(dept_id) ON DELETE CASCADE
);

🔹 New Features:

• CHECK (salary > 0) → Ensures salary is positive.
• department_id INT REFERENCES departments(dept_id) ON DELETE CASCADE
  • Links employees to a department.
  • If a department is deleted, related employees are also deleted.

4. Creating Tables with Auto-Incrementing IDs

PostgreSQL provides the SERIAL data type, which automatically assigns increasing numbers to new rows.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) CHECK (price >= 0)
);

• The SERIAL column starts from 1 and increments automatically.

5. Creating a Table from Another Table

You can create a new table based on an existing table.

CREATE TABLE employees_backup AS
SELECT * FROM employees;

✅ Copies the structure and data from employees to employees_backup.

6. Deleting a Table (DROP TABLE)

To remove a table permanently:

DROP TABLE employees;

To delete a table only if it exists:

DROP TABLE IF EXISTS employees;

7. Modifying an Existing Table (ALTER TABLE)

To add a new column:

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);

To modify a column data type:

ALTER TABLE employees ALTER COLUMN salary TYPE FLOAT;

To remove a column:

ALTER TABLE employees DROP COLUMN phone;

8. Summary

Operation	SQL Query
Create a Table	CREATE TABLE employees (id SERIAL PRIMARY KEY, first_name VARCHAR(50));
Check Table Structure	\d employees
Delete a Table	DROP TABLE employees;
Add a Column	ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
Modify Column Type	ALTER TABLE employees ALTER COLUMN salary TYPE FLOAT;
Remove a Column	ALTER TABLE employees DROP COLUMN phone;

Would you like an example for a real-world application? 🚀