PostgreSQL Complete Learning Tutorial

Overview

This comprehensive tutorial is designed to take you from a complete beginner to a proficient PostgreSQL database developer. Whether you're a software engineer, data analyst, or database administrator, this tutorial will provide you with the knowledge and skills needed to work effectively with PostgreSQL.

Tutorial Structure

Part 1: Getting Started

• Introduction to PostgreSQL
• Installation Guide
• pgAdmin Tool
• SQL Syntax Basics

Part 2: Database Fundamentals

• Data Types
• Create Database
• Select Database
• Drop Database
• Schema Management

Part 3: Table Operations

• Create Table
• Drop Table
• Alter Table
• Truncate Table
• Constraints

Part 4: Data Manipulation

• INSERT INTO
• SELECT (includes DISTINCT)
• UPDATE
• DELETE
• WHERE Clause (includes AND/OR)
• LIKE Operator

Part 5: Advanced Queries

• ORDER BY
• GROUP BY
• HAVING Clause
• LIMIT & OFFSET
• JOIN Operations
• UNION
• Subqueries
• WITH Clause (CTE)

Part 6: Database Objects

• Indexes
• Views
• Triggers
• Auto Increment

Part 7: Advanced Features

• Operators & Expressions
• Functions
• Date & Time
• NULL Values
• Aliases

Part 8: Management & Security

• Transactions
• Locks
• Permissions

Part 9: Resources

• Learning Resources

Quick Start

Install PostgreSQL

# Linux (Ubuntu/Debian)
sudo apt update
sudo apt install postgresql postgresql-contrib

# macOS
brew install postgresql@16

# Start service
sudo systemctl start postgresql

Connect to PostgreSQL

# Connect as postgres user
sudo -u postgres psql

# Or with password
psql -U postgres -d postgres

Create Your First Database

-- Create database
CREATE DATABASE myapp;

-- Connect to database
\c myapp

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

-- Insert data
INSERT INTO users (name, email) VALUES
    ('John Doe', 'john@example.com'),
    ('Jane Smith', 'jane@example.com');

-- Query data
SELECT * FROM users;

Core SQL Commands Quick Reference

Data Definition Language (DDL)

-- Create
CREATE DATABASE dbname;
CREATE TABLE tablename (column1 type1, column2 type2);
CREATE INDEX idxname ON tablename(column);

-- Alter
ALTER TABLE tablename ADD COLUMN columnname type;
ALTER TABLE tablename RENAME COLUMN oldname TO newname;

-- Drop
DROP TABLE tablename;
DROP DATABASE dbname;

Data Manipulation Language (DML)

-- Insert
INSERT INTO tablename (col1, col2) VALUES (val1, val2);
INSERT INTO tablename SELECT ... FROM ...;

-- Select
SELECT * FROM tablename;
SELECT col1, col2 FROM tablename WHERE condition;

-- Update
UPDATE tablename SET col1 = val1 WHERE condition;

-- Delete
DELETE FROM tablename WHERE condition;

Data Control Language (DCL)

-- Grant
GRANT SELECT, INSERT ON tablename TO username;
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- Revoke
REVOKE SELECT ON tablename FROM username;

Transaction Control Language (TCL)

-- Begin transaction
BEGIN;

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

-- Savepoint
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;

Data Types Quick Reference

Numeric Types

Type	Description	Range
SMALLINT	Small integer	-32,768 to 32,767
INTEGER	Integer	-2,147,483,648 to 2,147,483,647
BIGINT	Big integer	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
DECIMAL(p,s)	Exact numeric	User-specified precision
REAL	Single precision floating point	6 decimal digits precision
DOUBLE PRECISION	Double precision floating point	15 decimal digits precision
SERIAL	Auto-incrementing integer	1 to 2,147,483,647

String Types

Type	Description	Max Length
CHAR(n)	Fixed-length character string	10,485,760 characters
VARCHAR(n)	Variable-length character string	10,485,760 characters
TEXT	Unlimited variable-length string	Unlimited

Date/Time Types

Type	Description	Range
DATE	Date only	4713 BC to 5874897 AD
TIME	Time only	00:00:00 to 24:00:00
TIMESTAMP	Date and time	4713 BC to 294276 AD
TIMESTAMPTZ	Date and time with timezone	Same as above
INTERVAL	Time interval	±178,000,000 years

Other Types

Type	Description	Example
BOOLEAN	Boolean value	TRUE, FALSE, NULL
JSON	JSON data	'{"key": "value"}'
JSONB	Binary JSON	'{"key": "value"}'
UUID	Universal unique identifier	a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
ARRAY	Array	ARRAY[1, 2, 3]

Common Functions

String Functions

-- Length
SELECT LENGTH('Hello');  -- 5

-- Case conversion
SELECT UPPER('hello');   -- 'HELLO'
SELECT LOWER('HELLO');   -- 'hello'

-- String concatenation
SELECT CONCAT('Hello', ' ', 'World');  -- 'Hello World'
SELECT 'Hello' || ' ' || 'World';      -- 'Hello World'

-- Substring
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'

-- Replace
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');  -- 'Hello PostgreSQL'

-- Trim
SELECT TRIM('  Hello  ');  -- 'Hello'

Numeric Functions

-- Absolute value
SELECT ABS(-10);  -- 10

-- Round
SELECT ROUND(3.14159, 2);  -- 3.14

-- Ceiling
SELECT CEIL(3.14);  -- 4

-- Floor
SELECT FLOOR(3.14);  -- 3

-- Power
SELECT POWER(2, 3);  -- 8

-- Square root
SELECT SQRT(16);  -- 4

Date Functions

-- Current date/time
SELECT CURRENT_DATE;      -- 2024-01-15
SELECT CURRENT_TIME;      -- 14:30:00
SELECT CURRENT_TIMESTAMP; -- 2024-01-15 14:30:00

-- Date arithmetic
SELECT CURRENT_DATE + INTERVAL '1 day';      -- 2024-01-16
SELECT CURRENT_DATE - INTERVAL '1 week';     -- 2024-01-08

-- Extract date parts
SELECT EXTRACT(YEAR FROM CURRENT_DATE);  -- 2024
SELECT EXTRACT(MONTH FROM CURRENT_DATE); -- 1
SELECT EXTRACT(DAY FROM CURRENT_DATE);   -- 15

-- Age calculation
SELECT AGE('2000-01-15');  -- 24 years

Aggregate Functions

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM orders;

-- Sum
SELECT SUM(amount) FROM orders;

-- Average
SELECT AVG(price) FROM products;

-- Max/Min
SELECT MAX(price) FROM products;
SELECT MIN(price) FROM products;

-- String aggregation
SELECT STRING_AGG(name, ',') FROM users;

Best Practices

Naming Conventions

-- Use lowercase and underscores
CREATE TABLE user_accounts;  -- Recommended
CREATE TABLE userAccounts;   -- Avoid
CREATE TABLE USERACCOUNTS;   -- Avoid

-- Use descriptive names
CREATE TABLE customer_orders;  -- Good
CREATE TABLE co;              -- Avoid

-- Use plural for table names (optional)
CREATE TABLE users;           -- Common practice
CREATE TABLE user;            -- Also acceptable

Security Practices

-- Use strong passwords
-- Create users with limited privileges
CREATE USER app_user WITH PASSWORD 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE ON TABLE users TO app_user;
REVOKE DELETE ON TABLE users FROM app_user;

-- Prevent SQL injection
-- Use parameterized queries instead of string concatenation

Performance Optimization

-- Create appropriate indexes
CREATE INDEX idx_users_email ON users(email);

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Avoid SELECT *
SELECT id, name, email FROM users;  -- Specify needed columns

-- Use LIMIT to restrict results
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

Learning Path

Beginner (1-2 weeks)

• PostgreSQL introduction and installation
• pgAdmin tool usage
• Basic SQL syntax
• Data types
• Basic database and table operations

Intermediate (3-4 weeks)

• Advanced query techniques
• Data filtering and sorting
• Aggregation and grouping
• JOIN operations
• Subqueries

Advanced (5-6 weeks)

• Indexes and performance optimization
• Transactions and locks
• Views and stored procedures
• Triggers
• Security and permissions management

Resources

Official Documentation

• PostgreSQL Official Documentation: https://www.postgresql.org/docs/
• pgAdmin Documentation: https://www.pgadmin.org/docs/

Online Learning

• PostgreSQL Tutorial: https://www.postgresqltutorial.com/
• Mode SQL Tutorial: https://mode.com/sql-tutorial/

Community

• PostgreSQL Mailing Lists
• Stack Overflow
• Reddit: r/postgresql