Database Tutorial #2: PostgreSQL Setup and Basics

PostgreSQL is the most popular open-source database in the world. It is fast, reliable, and packed with features. This tutorial gets you up and running with PostgreSQL 17.

We use Docker so you do not need to install PostgreSQL on your computer.

Start PostgreSQL with Docker

docker run -d \
  --name postgres17 \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_USER=admin \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  postgres:17

This starts PostgreSQL 17 in the background. Let’s break down the options:

-d — run in background (detached)
--name postgres17 — name the container
POSTGRES_PASSWORD=secret — set the password
POSTGRES_USER=admin — create a user named “admin”
POSTGRES_DB=myapp — create a database named “myapp”
-p 5432:5432 — expose port 5432

Verify it is running:

docker ps

Connect with psql

psql is the official PostgreSQL command-line client. Connect to your running container:

docker exec -it postgres17 psql -U admin -d myapp

You should see:

psql (17.0)
Type "help" for help.

myapp=#

The myapp=# prompt means you are connected to the myapp database as a superuser.

Useful psql Commands

\l          -- list all databases
\c myapp    -- connect to database "myapp"
\dt         -- list all tables
\d users    -- describe table "users"
\q          -- quit psql
\?          -- help for psql commands
\h SELECT   -- help for SQL command

Data Types

PostgreSQL has rich data types. Here are the ones you will use most:

Type	Use for	Example
`integer` (or `int`)	whole numbers	42
`bigint`	large whole numbers	9999999999
`decimal(p, s)`	exact numbers (money)	19.99
`real` / `double precision`	floating point	3.14
`text`	any-length string	‘hello’
`varchar(n)`	limited-length string	‘Alex’
`boolean`	true/false	true
`uuid`	unique IDs	‘a1b2…’
`timestamptz`	timestamp with timezone	NOW()
`date`	date only	‘2026-01-01’
`jsonb`	JSON (binary, queryable)	‘{“key”: “val”}’
`text[]`	array of text	ARRAY[‘a’,‘b’]

Always use timestamptz instead of timestamp. It stores the timezone, which avoids bugs when your app runs in multiple timezones.

Always use jsonb instead of json. It is stored as binary, so queries are faster.

Create Your First Table

Let’s create a users table:

CREATE TABLE users (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  name       text NOT NULL,
  email      text UNIQUE NOT NULL,
  age        integer CHECK (age >= 0),
  active     boolean DEFAULT true,
  created_at timestamptz DEFAULT NOW()
);

Key points:

gen_random_uuid() generates a UUID automatically
NOT NULL means the field is required
UNIQUE means no two rows can have the same value
CHECK adds a validation constraint
DEFAULT sets a default value

INSERT — Add Rows

-- Insert one row
INSERT INTO users (name, email, age)
VALUES ('Alex', 'alex@example.com', 28);

-- Insert multiple rows
INSERT INTO users (name, email, age)
VALUES
  ('Sam', 'sam@example.com', 35),
  ('Jordan', 'jordan@example.com', 22);

SELECT — Read Rows

-- Get all users
SELECT * FROM users;

-- Get specific columns
SELECT name, email FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE active = true;

-- Filter with multiple conditions
SELECT * FROM users WHERE age > 25 AND active = true;

-- Sort results
SELECT * FROM users ORDER BY created_at DESC;

-- Limit results
SELECT * FROM users LIMIT 10;

-- Skip rows (pagination)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

The OFFSET 20 skips the first 20 rows. This gives you page 3 (with 10 items per page).

UPDATE — Modify Rows

-- Update one field
UPDATE users SET active = false WHERE email = 'alex@example.com';

-- Update multiple fields
UPDATE users SET name = 'Alexander', age = 29 WHERE id = '...';

-- Update all rows (be careful!)
UPDATE users SET active = true;

DELETE — Remove Rows

-- Delete a specific row
DELETE FROM users WHERE id = '...';

-- Delete rows matching a condition
DELETE FROM users WHERE active = false;

-- Delete all rows (dangerous!)
TRUNCATE users;

TRUNCATE is faster than DELETE with no condition. In PostgreSQL, TRUNCATE is transaction-safe and can be rolled back inside a transaction. However, it acquires a strong lock that blocks all other access to the table. Use it with care.

Python Example

First install the psycopg driver (psycopg3):

pip install "psycopg[binary]"

import psycopg
import uuid

# Connect to PostgreSQL
conn = psycopg.connect(
    host="localhost",
    port=5432,
    dbname="myapp",
    user="admin",
    password="secret"
)

# Create the users table
with conn.cursor() as cur:
    cur.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
            name       text NOT NULL,
            email      text UNIQUE NOT NULL,
            age        integer,
            created_at timestamptz DEFAULT NOW()
        )
    """)
    conn.commit()

# Insert a user
with conn.cursor() as cur:
    cur.execute(
        "INSERT INTO users (name, email, age) VALUES (%s, %s, %s) RETURNING id",
        ("Alex", "alex@example.com", 28)
    )
    user_id = cur.fetchone()[0]
    conn.commit()
    print(f"Created user: {user_id}")

# Query users
with conn.cursor() as cur:
    cur.execute("SELECT id, name, email FROM users WHERE age > %s", (25,))
    users = cur.fetchall()
    for user in users:
        print(user)

conn.close()

Always use parameterized queries (%s placeholders). Never format SQL strings yourself — this prevents SQL injection.

TypeScript Example

Install the pg package (node-postgres v8):

npm install pg
npm install -D @types/pg

import { Pool } from "pg";

const pool = new Pool({
  host: "localhost",
  port: 5432,
  database: "myapp",
  user: "admin",
  password: "secret",
});

// Create the users table
async function createTable(): Promise<void> {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
      name       text NOT NULL,
      email      text UNIQUE NOT NULL,
      age        integer,
      created_at timestamptz DEFAULT NOW()
    )
  `);
}

// Insert a user
async function createUser(name: string, email: string, age: number): Promise<string> {
  const result = await pool.query(
    "INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id",
    [name, email, age]
  );
  return result.rows[0].id;
}

// Query users
async function getUsers(minAge: number): Promise<any[]> {
  const result = await pool.query(
    "SELECT id, name, email FROM users WHERE age > $1",
    [minAge]
  );
  return result.rows;
}

async function main(): Promise<void> {
  await createTable();

  const userId = await createUser("Alex", "alex@example.com", 28);
  console.log("Created user:", userId);

  const users = await getUsers(25);
  console.log("Users over 25:", users);

  await pool.end();
}

main();

Note: In TypeScript, parameters use $1, $2, $3 (not %s like Python).

Connection Strings

You can also connect with a connection URL:

postgresql://admin:secret@localhost:5432/myapp

This format is useful for environment variables:

# In your .env file
DATABASE_URL=postgresql://admin:secret@localhost:5432/myapp

Useful SELECT Tricks

-- Count rows
SELECT COUNT(*) FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT email) FROM users;

-- Get min, max, average
SELECT MIN(age), MAX(age), AVG(age) FROM users;

-- Group by and count
SELECT age, COUNT(*) as total
FROM users
GROUP BY age
ORDER BY age;

-- Check if a row exists
SELECT EXISTS(SELECT 1 FROM users WHERE email = 'alex@example.com');

What We Learned

Start PostgreSQL 17 with a single Docker command
Use psql to connect and run queries
Key data types: uuid, text, boolean, timestamptz, jsonb
CRUD operations: INSERT, SELECT, UPDATE, DELETE
Connect from Python (psycopg3) and TypeScript (pg v8)
Always use parameterized queries to prevent SQL injection

What’s Next?

In the next tutorial, we go deeper with JOINs, CTEs, window functions, and advanced SELECT patterns.

Start PostgreSQL with Docker#

Connect with psql#

Useful psql Commands#

Data Types#

Create Your First Table#

INSERT — Add Rows#

SELECT — Read Rows#

UPDATE — Modify Rows#

DELETE — Remove Rows#

Python Example#

TypeScript Example#

Connection Strings#

Useful SELECT Tricks#

What We Learned#

Related Articles#

What’s Next?#