Module 4 — Basic CRUD Operations

1. INSERT — Adding Data

The INSERT command adds new rows to a table. You should always specify the columns you are filling to keep your code stable.

Single Row Insert

INSERT INTO users (username, email, age) 
VALUES ('alice_dev', 'alice@example.com', 25);

Multi-Row Insert (Efficient)

Instead of running 100 commands, you can run one. This is much faster in production environments.

INSERT INTO users (username, email, age)
VALUES 
    ('bob_codes', 'bob@example.com', 30),
    ('charlie_writes', 'charlie@example.com', 22),
    ('diana_dev', 'diana@example.com', 28);

The RETURNING Clause

In modern app development, you often need the id of the row you just created. Postgres makes this easy with RETURNING.

INSERT INTO users (username, email) 
VALUES ('new_user', 'new@example.com')
RETURNING id, created_at;

2. SELECT — Reading Data

SELECT is the most common command you will ever run. It tells the database: "Give me this specific data from this table."

The "All Columns" shortcut

While common in learning, SELECT * (Select All) is usually avoided in production because it can be slow if you have 100 columns.

SELECT * FROM users;

Selecting Specific Columns (Best Practice)

Only request the data you actually need.

SELECT username, email FROM users;

Column Aliasing (AS)

You can rename the output columns for your reports or for your application's API.

SELECT username AS user_handle, email AS contact_info 
FROM users;

3. Data Transformations in SELECT

You can do more than just "read" data; you can transform it as it's being retrieved.

Basic Expressions

-- Assuming a products table with price in cents
SELECT name, price / 100 AS price_in_dollars 
FROM products;

String Concatenation

-- Combine first and last names (standard SQL uses ||)
SELECT first_name || ' ' || last_name AS full_name 
FROM users;

CRUD Flow

Common Mistakes to Avoid

1. Forgetting Column Names in INSERT: INSERT INTO users VALUES (...) is dangerous because if you add a new column to the table later, this command will break. Always list your columns.
2. The "Select All" Trap: In production, SELECT * can significantly slow down your application and consume unnecessary memory.
3. Mismatched Types: Trying to insert a string into an integer column will cause an error. Postgres is strict about types (which is a good thing!).

Practice Exercise

Using the courses table you created in Module 3:

1. Insert a single course: ('SQL Basics', 'John Doe', 2999, true).
2. Insert three more courses in a single command.
3. Use RETURNING id to see the generated IDs of your new courses.
4. Write a SELECT query that returns the course title and the price divided by 100, renamed as price_in_rupees.