Introduction to PostgreSQL

PostgreSQL is a powerful open-source relational database management system (RDBMS) used to store, manage, and query structured data efficiently.

PostgreSQL Basics

Databases & Tables

-- List all databases
\l

-- Create database
CREATE DATABASE School;

-- Connect to database
\c School

-- Create table
CREATE TABLE Students(
  ID SERIAL PRIMARY KEY,
  Name VARCHAR(50),
  Age INT,
  Grade VARCHAR(10)
);

-- List tables
\dt
      

Basic CRUD Operations

-- Insert data
INSERT INTO Students (Name, Age, Grade) VALUES ('John', 15, '10th');

-- Select data
SELECT * FROM Students;
SELECT * FROM Students WHERE Age>15;

-- Update data
UPDATE Students SET Age=16 WHERE Name='John';

-- Delete data
DELETE FROM Students WHERE Name='John';
      

SQL Queries

Filtering & Sorting

SELECT * FROM Students WHERE Grade='10th';
SELECT * FROM Students ORDER BY Age ASC;
SELECT * FROM Students ORDER BY Age DESC;
      

Aggregate Functions

SELECT COUNT(*) FROM Students;
SELECT AVG(Age) FROM Students;
SELECT MAX(Age), MIN(Age) FROM Students;
      

Grouping

SELECT Grade, COUNT(*) FROM Students GROUP BY Grade;
      

Joins & Relationships

Inner Join

SELECT S.Name, C.CourseName
FROM Students S
INNER JOIN Enrollments E ON S.ID=E.StudentID
INNER JOIN Courses C ON E.CourseID=C.ID;
      

Left Join

SELECT S.Name, C.CourseName
FROM Students S
LEFT JOIN Enrollments E ON S.ID=E.StudentID
LEFT JOIN Courses C ON E.CourseID=C.ID;
      

Advanced PostgreSQL

Views

CREATE VIEW TeenStudents AS
SELECT * FROM Students WHERE Age BETWEEN 13 AND 19;
SELECT * FROM TeenStudents;
      

Transactions

BEGIN;
UPDATE Students SET Age=16 WHERE Name='John';
COMMIT;
-- Or ROLLBACK to undo
ROLLBACK;
      

Indexes & Performance

CREATE INDEX idx_age ON Students(Age);
EXPLAIN SELECT * FROM Students WHERE Age>15;
      

Stored Procedures & Functions

CREATE FUNCTION get_student_count() RETURNS INT AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM Students);
END;
$$ LANGUAGE plpgsql;

SELECT get_student_count();