Kenhuang Academy
  • English

      • 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

      Part 2: Database Fundamentals

      Part 3: Table Operations

      Part 4: Data Manipulation

      Part 5: Advanced Queries

      Part 6: Database Objects

      Part 7: Advanced Features

      Part 8: Management & Security

      Part 9: 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

      TypeDescriptionRange
      SMALLINTSmall integer-32,768 to 32,767
      INTEGERInteger-2,147,483,648 to 2,147,483,647
      BIGINTBig integer-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
      DECIMAL(p,s)Exact numericUser-specified precision
      REALSingle precision floating point6 decimal digits precision
      DOUBLE PRECISIONDouble precision floating point15 decimal digits precision
      SERIALAuto-incrementing integer1 to 2,147,483,647

      String Types

      TypeDescriptionMax Length
      CHAR(n)Fixed-length character string10,485,760 characters
      VARCHAR(n)Variable-length character string10,485,760 characters
      TEXTUnlimited variable-length stringUnlimited

      Date/Time Types

      TypeDescriptionRange
      DATEDate only4713 BC to 5874897 AD
      TIMETime only00:00:00 to 24:00:00
      TIMESTAMPDate and time4713 BC to 294276 AD
      TIMESTAMPTZDate and time with timezoneSame as above
      INTERVALTime interval±178,000,000 years

      Other Types

      TypeDescriptionExample
      BOOLEANBoolean valueTRUE, FALSE, NULL
      JSONJSON data'{"key": "value"}'
      JSONBBinary JSON'{"key": "value"}'
      UUIDUniversal unique identifiera0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
      ARRAYArrayARRAY[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

      Online Learning

      Community

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