How to Create Tables in PostgreSQL

In PostgreSQL, you can create tables using the CREATE TABLE statement. A table consists of columns, each with a specific data type, and optional constraints such as primary keys, foreign keys, and unique constraints.

Basic Syntax

CREATE TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);
Explanation:
- table_name → The name of the table.
- column_name → The name of the column.
- data_type → The data type of the column (e.g., `INTEGER`, `TEXT`, `VARCHAR`, `DATE`).
- constraints → Optional rules such as `PRIMARY KEY`, `NOT NULL`, `UNIQUE`, etc.

1. Creating a Simple Table

Let’s create a “customers” table:

CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
address TEXT,
date_of_birth DATE
);
Explanation:  
- `customer_id SERIAL PRIMARY KEY`: A unique ID that auto-increments.
- `first_name`, `last_name`: Cannot be `NULL`.
- `email`: Must be unique and cannot be `NULL`.
- `phone`, `address`: Optional fields.
- `date_of_birth`: Stores the customer's date of birth.

2. Creating a Table with Foreign Key

If a table needs to reference another table, use a foreign key. Example: Creating an “orders” table linked to “customers”

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);
Explanation:  
- `order_id SERIAL PRIMARY KEY`: Auto-incrementing order ID.
- `customer_id INT NOT NULL`: Links to `customers.customer_id`.
- `order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP`: Automatically sets the current time.
- `total_price DECIMAL(10,2) NOT NULL`: Stores the order amount.
- `FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE`: If a customer is deleted, their orders will be deleted too.

3. Creating a Table with Constraints

PostgreSQL supports several constraints for data integrity. Example: Employees Table with Constraints

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15) CHECK (phone ~ '^[0-9]+$'), -- Ensures only digits
    position VARCHAR(50) CHECK (position IN ('Manager', 'Staff', 'Clerk')),
    salary DECIMAL(10,2) CHECK (salary > 0)
);
Constraints Used:  
- `UNIQUE`: Ensures the email is unique.
- `CHECK (phone ~ '^[0-9]+$')`: Ensures the phone number contains only digits.
- `CHECK (position IN ('Manager', 'Staff', 'Clerk'))`: Restricts the position to specific values.
- `CHECK (salary > 0)`: Ensures salary is positive.

Until this stage, we have learned how to create a new table in multiple options.

Listing All Tables in PostgreSQL

After creating tables, you can view them using: \d, as shown below :

Or, list all tables in a specific schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’;

Dropping a Table

If you need to delete a table:

DROP TABLE table_name;

To delete it only if it exists:

DROP TABLE IF EXISTS table_name;

Conclusion

Creating tables in PostgreSQL is straightforward with the `CREATE TABLE` command. You can define columns, set data types, apply constraints, and establish relationships using primary keys and foreign keys. Always consider data integrity when designing your database schema.