PostgreSQL Introduction

🐘 What is PostgreSQL?

PostgreSQL is an advanced open-source relational database management system (RDBMS) that uses SQL for querying. It emphasizes extensibility, standards compliance, and handles structured data with exceptional reliability.


-- PostgreSQL stores data in tables
SELECT version();

Output:

PostgreSQL 16.1 on x86_64-pc-linux-gnu

Key PostgreSQL Concepts

📊

Tables

Data organized in rows and columns

CREATE TABLE products (
  id INT,
  name TEXT
);

🔑

Primary Keys

Unique identifiers for each row

CREATE TABLE orders (
  id SERIAL PRIMARY KEY
);

🔗

Relationships

Connect tables using foreign keys

FOREIGN KEY (user_id) 
REFERENCES users(id)

📝

SQL Queries

Retrieve and manipulate data

SELECT * FROM users
WHERE age > 18;

🔹 PostgreSQL History

PostgreSQL originated from the POSTGRES project at UC Berkeley in 1986. It evolved into PostgreSQL in 1996 when SQL support was added, becoming one of the most advanced open-source databases available today.

Key Milestones:

1986: POSTGRES project begins at Berkeley
1996: Renamed to PostgreSQL with SQL support
2005: Native Windows support added
2012: JSON support introduced
2023: PostgreSQL 16 with performance improvements

🔹 Database Structure

PostgreSQL organizes data hierarchically. A server can host multiple databases, each containing schemas with tables, views, and functions for logical data organization and security.


-- Create a database
CREATE DATABASE mystore;

-- Create a table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

Result:

✓ Database "mystore" created

✓ Table "customers" created

🔹 Basic Data Types

PostgreSQL supports various data types for different kinds of information. Choosing the right type ensures data integrity, optimizes storage, and improves query performance significantly.


CREATE TABLE employees (
    -- Numeric types
    id SERIAL,
    age INTEGER,
    salary NUMERIC(10, 2),
    
    -- Text types
    name VARCHAR(100),
    bio TEXT,
    
    -- Date/Time types
    hire_date DATE,
    created_at TIMESTAMP,
    
    -- Boolean type
    is_active BOOLEAN
);

Common Data Types:

INTEGER: Whole numbers
VARCHAR(n): Variable-length text
DATE: Calendar dates
BOOLEAN: True/False values

🔹 ACID Properties

PostgreSQL guarantees ACID compliance for reliable transactions. These properties ensure data consistency and integrity even during system failures, concurrent access, or unexpected errors.

ACID Explained:

Atomicity: Transactions complete fully or not at all
Consistency: Data remains valid after transactions
Isolation: Concurrent transactions don't interfere
Durability: Committed data persists permanently


-- Transaction example
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

🔹 PostgreSQL vs Other Databases

PostgreSQL stands out with advanced features like custom data types, full-text search, and JSON support. It balances performance with standards compliance better than most alternatives.

Advantages:

More SQL standard compliant than MySQL
Better handling of complex queries
Advanced indexing options (GiST, GIN, BRIN)
Native JSON and array support
Extensible with custom functions and types
Strong community and enterprise support