Getting Started with PostgreSQL

PostgreSQL is the world's most advanced open source database. It handles everything from simple apps to enterprise workloads. Here's how to get started.

Why PostgreSQL?

ACID Compliant: Reliable transactions
Feature-Rich: JSON, full-text search, spatial data
Extensible: Custom types, functions, indexes
Free: Completely open source

Installing PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Connecting

sudo -u postgres psql
# Or create a user
sudo -u postgres createuser -s myuser
sudo -u postgres psql
\password myuser

Creating a Database

CREATE DATABASE myapp;
\c myapp  -- Connect to it

Creating Tables

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Basic SQL Commands

-- Insert data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Query data
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;

-- Update data
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

Joins

-- Create orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2)
);

-- Join tables
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Aggregations

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

-- Group by
SELECT country, COUNT(*) as count
FROM users
GROUP BY country;

-- Average
SELECT AVG(age) FROM users;

What's Next?

PostgreSQL has much more to offer: JSON support, full-text search, indexes, views, stored procedures, and more. Check out our PostgreSQL guide for the full course!