Setting Up Your SQL Environment (PostgreSQL)

Introduction

Now that you understand why SQL databases dominate, it's time to get hands-on! In this lesson, we'll set up PostgreSQL on your computer and write your very first SQL query.

By the end of this lesson, you'll have:

• PostgreSQL installed and running
• A database client to write and execute queries
• Your first database created
• Confidence to start exploring SQL

Why PostgreSQL?

We're using PostgreSQL because it:

• Is free and open-source
• Is the #1 database in Stack Overflow's survey (55.6%)
• Has excellent documentation
• Supports modern features (JSONB, full-text search, vectors)
• Is used by Instagram, Uber, Netflix, Airbnb
• Teaches you standard SQL that works everywhere

Installation Options

You have several options for setting up PostgreSQL. Choose the one that works best for you.

Option 1: PostgreSQL App (Mac - Easiest)

Recommended for Mac users

1. Visit https://postgresapp.com
2. Download Postgres.app
3. Drag to Applications folder
4. Double-click to start PostgreSQL
5. Click "Initialize" to create your first database

Pros: One-click setup, easy to start/stop Cons: Mac only

Option 2: Official Installer (Windows/Mac/Linux)

Recommended for Windows users

1. Visit https://www.postgresql.org/download
2. Choose your operating system
3. Download the installer (version 15 or newer)
4. Run the installer:
   • Remember the password you set for the postgres user
   • Default port: 5432 (keep this)
   • Skip Stack Builder (optional tools)

Pros: Official installation, includes pgAdmin GUI Cons: More setup steps

Option 3: Docker (All Platforms)

Recommended if you already use Docker

# Pull PostgreSQL image
docker pull postgres:16

# Run PostgreSQL container
docker run --name postgres-local \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -d postgres:16

# Verify it's running
docker ps

Pros: Isolated environment, easy cleanup Cons: Requires Docker knowledge

Option 4: Cloud Database (No Installation)

Recommended for quick start without installation

Use a free cloud PostgreSQL service:

Neon - https://neon.tech

• Serverless PostgreSQL
• Generous free tier
• No credit card required

Pros: No installation, accessible anywhere Cons: Requires internet, limited free storage

Verifying Your Installation

After installing, verify PostgreSQL is running:

Mac (Postgres.app)

Look for the elephant icon in your menu bar. If it's there, you're good!

Windows/Mac (Official Installer)

Open a terminal/command prompt and run:

psql --version

You should see something like:

psql (PostgreSQL) 16.1

Docker

docker exec -it postgres-local psql -U postgres

Choosing a Database Client

You need a client to write and run SQL queries. Here are the best options:

Option 1: psql (Command Line - Built-in)

Best for: Learning SQL fundamentals, quick queries

PostgreSQL includes psql, a command-line tool.

To start psql:

# Mac (Postgres.app) - click "Open psql" button

# Official installer
psql -U postgres

# Docker
docker exec -it postgres-local psql -U postgres

Common psql commands:

\l                  -- List all databases
\c database_name    -- Connect to a database
\dt                 -- List all tables
\d table_name       -- Describe a table
\q                  -- Quit psql

Option 2: pgAdmin (GUI - Recommended for Beginners)

Best for: Visual interface, exploring databases

pgAdmin is included with the official PostgreSQL installer.

To use pgAdmin:

1. Open pgAdmin
2. Create a server connection:
   • Right-click "Servers" → "Register" → "Server"
   • Name: Local
   • Host: localhost
   • Port: 5432
   • Username: postgres
   • Password: (your password)
3. Click "Save"

Features:

• Visual query editor
• Table browser
• Query history
• Explain plans

Option 3: DBeaver (Cross-platform, Free)

Best for: Multiple database types, advanced features

Download from https://dbeaver.io

To connect:

1. New Database Connection → PostgreSQL
2. Host: localhost
3. Port: 5432
4. Database: postgres
5. Username: postgres
6. Password: (your password)
7. Test Connection → Finish

Option 4: TablePlus (Mac/Windows - Beautiful UI)

Best for: Modern interface, fast performance

Download from https://tableplus.com

Free version is sufficient for learning.

Recommendation

For this course, I recommend:

• Beginners: pgAdmin (visual, easier to understand)
• Comfort with terminals: psql (faster, teaches SQL fundamentals)
• Mac users: TablePlus (beautiful, native)

Choose what feels comfortable—you can always switch later!

Creating Your First Database

Let's create a database for our course exercises.

Using psql

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE sql_basics;

# Connect to the new database
\c sql_basics

# Confirm you're connected
SELECT current_database();

Using pgAdmin

1. Right-click "Databases" → "Create" → "Database"
2. Database name: sql_basics
3. Click "Save"

Using DBeaver or TablePlus

1. Right-click connection → "Create" → "Database"
2. Name: sql_basics
3. Click "OK"

Creating Your First Table

Now let's create a simple table to test everything works:

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

-- Insert some sample data
INSERT INTO users (name, email, age)
VALUES
    ('Alice Smith', 'alice@example.com', 28),
    ('Bob Jones', 'bob@example.com', 34),
    ('Carol White', 'carol@example.com', 22);

-- Query the data
SELECT * FROM users;

Expected output:

 id |    name      |        email         | age
----+--------------+----------------------+-----
  1 | Alice Smith  | alice@example.com    |  28
  2 | Bob Jones    | bob@example.com      |  34
  3 | Carol White  | carol@example.com    |  22

Congratulations! You just:

• Created a table
• Inserted data
• Queried data

That's the foundation of SQL!

Understanding What Just Happened

Let's break down that SQL:

CREATE TABLE

CREATE TABLE users (
    id SERIAL PRIMARY KEY,      -- Auto-incrementing ID
    name VARCHAR(100),           -- Text up to 100 characters
    email VARCHAR(100),          -- Text up to 100 characters
    age INTEGER                  -- Whole number
);

• SERIAL: Auto-generates IDs (1, 2, 3, ...)
• PRIMARY KEY: Makes id unique and indexed
• VARCHAR(100): Variable-length text, max 100 characters
• INTEGER: Whole numbers

INSERT

INSERT INTO users (name, email, age)
VALUES
    ('Alice Smith', 'alice@example.com', 28);

Adds a new row to the table.

SELECT

SELECT * FROM users;

Retrieves all columns (*) from all rows in the users table.

Troubleshooting Common Issues

"psql: command not found"

Solution: Add PostgreSQL to your PATH

Mac:

echo 'export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

Windows: Reinstall with "Add to PATH" checked

"FATAL: password authentication failed"

Solution: Use the correct password you set during installation

Reset password (if forgotten):

# Edit pg_hba.conf to trust local connections temporarily
# Then run:
ALTER USER postgres WITH PASSWORD 'newpassword';

"Connection refused on port 5432"

Solution: PostgreSQL isn't running

Mac (Postgres.app): Click the elephant icon and start Windows: Services → PostgreSQL → Start Docker: docker start postgres-local

"Database does not exist"

Solution: Create it first

CREATE DATABASE sql_basics;
\c sql_basics

Practice Exercise

Try these queries on your new users table:

-- 1. Find users older than 25
SELECT * FROM users WHERE age > 25;

-- 2. Find Alice's email
SELECT email FROM users WHERE name = 'Alice Smith';

-- 3. Count total users
SELECT COUNT(*) FROM users;

-- 4. Find the average age
SELECT AVG(age) FROM users;

-- 5. Sort users by age (youngest first)
SELECT * FROM users ORDER BY age ASC;

Don't worry if you don't understand all these yet—we'll cover them in detail in the next lessons!

Tips for Success

1. Use a dedicated workspace

Create a folder for your SQL learning:

mkdir ~/sql-course
cd ~/sql-course

2. Save your queries

Keep a file practice.sql with queries you try:

-- practice.sql
-- Practice queries from Lesson 2.1

SELECT * FROM users;
SELECT name, age FROM users WHERE age > 25;

3. Experiment fearlessly

You can always drop and recreate tables:

DROP TABLE users;  -- Delete the table
-- Then recreate it with CREATE TABLE...

4. Use comments

-- This is a comment
SELECT * FROM users;  -- This is also a comment

5. Keep PostgreSQL running

Leave it running while you work through lessons. You can stop it when done.

SQL Formatting Best Practices

Use consistent formatting for readability:

Good:

SELECT
    id,
    name,
    email
FROM users
WHERE age > 25
ORDER BY name;

Also fine:

SELECT id, name, email
FROM users
WHERE age > 25
ORDER BY name;

Avoid:

select id,name,email from users where age>25 order by name;

SQL is case-insensitive (SELECT = select), but uppercase keywords are conventional.

What's Next

You now have:

• ✅ PostgreSQL installed and running
• ✅ A database client configured
• ✅ Your first database and table created
• ✅ Experience running basic SQL queries

In the next lesson, we'll dive deep into SELECT queries—the most fundamental SQL command. You'll learn how to retrieve exactly the data you need from your tables.

Let's keep going!