PostgreSQL CREATE TABLE

Learn how to create tables in PostgreSQL database

🗄️ What is CREATE TABLE?

CREATE TABLE is a SQL command used to create a new table in PostgreSQL database. Tables store data in rows and columns, forming the foundation of your database structure. 


-- Create a simple users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Output:

✓ Table "users" created successfully

Key Table Components

🔑

Primary Key

Unique identifier for each row 

id SERIAL PRIMARY KEY
📝

Data Types

Define what kind of data to store 

name VARCHAR(100)
age INTEGER
⚠️

Constraints

Rules to ensure data validity 

email VARCHAR(100) NOT NULL
price DECIMAL CHECK (price > 0)
🔗

Foreign Key

Link tables together 

user_id INTEGER REFERENCES users(id)

🔹 Basic Table Creation

Creating a simple table requires specifying the table name and column definitions. Each column needs a name and data type. This is the most fundamental operation in database design. 

-- Create a products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200),
    price DECIMAL(10, 2),
    stock_quantity INTEGER
);

Output:

✓ Table "products" created with 4 columns

🔹 Table with Constraints

Constraints enforce rules on your data to maintain accuracy and reliability. Common constraints include NOT NULL, UNIQUE, CHECK, and DEFAULT values. These prevent invalid data from entering your database. 

-- Create table with various constraints
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT true
);

Output:

✓ Table "employees" created with constraints

🔹 Common Data Types

PostgreSQL offers various data types for different kinds of information. Choosing the right data type ensures efficient storage and proper data validation. Understanding these types is essential for effective database design. 

-- Table demonstrating common data types
CREATE TABLE data_types_demo (
    -- Numeric types
    id SERIAL,
    age INTEGER,
    price DECIMAL(10, 2),
    rating REAL,
    
    -- Text types
    username VARCHAR(50),
    description TEXT,
    
    -- Date and time
    created_at TIMESTAMP,
    birth_date DATE,
    
    -- Boolean
    is_verified BOOLEAN,
    
    -- JSON
    metadata JSONB
);

Output:

✓ Table "data_types_demo" created with multiple data types

🔹 Table with Foreign Key

Foreign keys create relationships between tables by referencing the primary key of another table. This maintains referential integrity and enables complex data relationships. It's fundamental for normalized database design. 

-- Create orders table with foreign key
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    
    -- Foreign key constraint
    FOREIGN KEY (customer_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Output:

✓ Table "orders" created with foreign key relationship

🔹 Create Table If Not Exists

The IF NOT EXISTS clause prevents errors when a table already exists. This is useful in scripts that might run multiple times. It makes your code more robust and reusable. 

-- Safe table creation
CREATE TABLE IF NOT EXISTS categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Output:

✓ Table "categories" created (or already exists)

🧠 Test Your Knowledge

Which keyword is used to create a unique identifier column?