Home›PostgreSQL for Developers›Day 1
Day 1 of 5
⏱ ~45 minutes
PostgreSQL for Developers — Day 1

SELECT & INSERT: PostgreSQL Fundamentals

PostgreSQL is the database behind most serious web and AI applications. This lesson covers the SQL you use every day: querying data, filtering it, and writing new records. Everything hands-on with a real database.

Install and Connect to PostgreSQL

You need two things: the PostgreSQL server and a client to connect to it. We'll use psql (the command line client) and optionally TablePlus or DBeaver for a visual interface.

bash
# macOS
brew install postgresql@16
brew services start postgresql@16

# Ubuntu
sudo apt-get install postgresql postgresql-contrib
sudo systemctl start postgresql

# Connect as the default superuser
psql -U postgres

# Or connect to a specific database
psql -U postgres -d mydb

Create a Database and Table

sql
-- In psql
CREATE DATABASE aiprojects;

-- Connect to it
\c aiprojects

-- Create a table
CREATE TABLE projects (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    status      VARCHAR(20)  DEFAULT 'active',
    created_at  TIMESTAMP    DEFAULT NOW(),
    budget      NUMERIC(10,2)
);

-- See the table structure
\d projects
ℹ️
SERIAL auto-increments the ID on every insert. In PostgreSQL 10+ you can also use GENERATED ALWAYS AS IDENTITY — same idea, more standard SQL.

INSERT — Adding Data

sql
-- Single row
INSERT INTO projects (name, status, budget)
VALUES ('AI Dashboard', 'active', 5000.00);

-- Multiple rows at once
INSERT INTO projects (name, status, budget)
VALUES
    ('Chatbot v2', 'planning', 8000.00),
    ('Data Pipeline', 'active', 12000.00),
    ('Report Generator', 'complete', 3500.00);

-- Insert and return the new ID
INSERT INTO projects (name, budget)
VALUES ('New Project', 9500.00)
RETURNING id, created_at;

SELECT — Querying Data

sql
-- All rows, all columns
SELECT * FROM projects;

-- Specific columns
SELECT id, name, status FROM projects;

-- Filter with WHERE
SELECT * FROM projects WHERE status = 'active';

-- Multiple conditions
SELECT * FROM projects
WHERE status = 'active' AND budget > 5000;

-- ORDER and LIMIT
SELECT * FROM projects
ORDER BY budget DESC
LIMIT 5;

-- Pattern matching
SELECT * FROM projects
WHERE name ILIKE '%ai%';  -- case-insensitive LIKE

UPDATE and DELETE

sql
-- Update specific rows
UPDATE projects
SET status = 'complete', budget = 4200.00
WHERE id = 1;

-- Update with a calculation
UPDATE projects
SET budget = budget * 1.10
WHERE status = 'active';

-- Delete specific rows
DELETE FROM projects WHERE status = 'complete';

-- ALWAYS use WHERE with UPDATE/DELETE
-- Without WHERE you affect every row
⚠️
Always test with SELECT first. Before running UPDATE or DELETE, run the equivalent SELECT with the same WHERE clause to see exactly which rows you're about to modify.

Aggregate Functions

sql
SELECT
    COUNT(*)                    AS total_projects,
    SUM(budget)                 AS total_budget,
    AVG(budget)                 AS avg_budget,
    MIN(budget)                 AS min_budget,
    MAX(budget)                 AS max_budget
FROM projects;

-- GROUP BY
SELECT
    status,
    COUNT(*)        AS count,
    SUM(budget)     AS total_budget
FROM projects
GROUP BY status
ORDER BY total_budget DESC;
📝 Exercise
Set Up Your First PostgreSQL Database
  1. Create a database called learningdb.
  2. Create a table tasks with columns: id, title, priority (1-5), done (boolean), created_at.
  3. Insert 5 rows with different priorities.
  4. Write a SELECT query that returns only incomplete tasks, ordered by priority desc.
  5. Update one task to mark it done.
  6. Write a GROUP BY query showing count of tasks per priority level.

Lesson Summary

  • Use CREATE TABLE with data types: SERIAL, VARCHAR, TEXT, INTEGER, NUMERIC, BOOLEAN, TIMESTAMP.
  • INSERT ... RETURNING id gets the auto-generated ID back.
  • Always use WHERE with UPDATE and DELETE — test with SELECT first.
  • COUNT, SUM, AVG, GROUP BY summarize data without loading it into Python.
Challenge

Add a tags column to your table (use TEXT[] — PostgreSQL's array type). Insert rows with multiple tags. Write a query using the @> contains operator to find rows with a specific tag.

Next →
Day 2: JOINs and Indexes
→
Finished this lesson?