🐘 PostgreSQL - Map of Content

What is PostgreSQL?

PostgreSQL (“Postgres”) is a powerful, open-source, object-relational database system known for standards compliance, extensibility, and advanced features like JSONB, window functions, full-text search, and custom types. This folder is a full command and syntax reference, organized the same way as the Pandas, NumPy, and Scikit-learn folders.

-- The classic sanity check
SELECT version();

Version note

This vault assumes PostgreSQL 16+. Syntax notes flag anything version-specific (e.g. MERGE requires 15+, multirange types require 14+).

📂 Folder Contents

🗺️ Conceptual Map

graph TD
    A[PostgreSQL] --> B[Schema Design]
    A --> C[Data Manipulation]
    A --> D[Querying]
    A --> E[Server-Side Logic]
    A --> F[Operations]

    B --> B1[Data Types]
    B --> B2[Tables & Constraints]
    B --> B3[Indexes]

    C --> C1[INSERT / UPDATE / DELETE]
    C --> C2[Transactions]

    D --> D1[SELECT / WHERE / JOIN]
    D --> D2[GROUP BY / Aggregates]
    D --> D3[CTEs / Subqueries]
    D --> D4[Window Functions]

    E --> E1[Views]
    E --> E2[Functions / Procedures]
    E --> E3[Triggers]

    F --> F1[psql / Admin]
    F --> F2[Backup & Restore]

⚡ Quick Reference - Most-Used Statements

-- Connect
psql -U username -d database_name -h localhost
 
-- Table lifecycle
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;
 
-- CRUD
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT * FROM users WHERE age > 25 ORDER BY name LIMIT 10;
UPDATE users SET age = 26 WHERE id = 1;
DELETE FROM users WHERE id = 1;
 
-- Joins & aggregation
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name
HAVING COUNT(o.id) > 0;
 
-- Transactions
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

🔗 Related in LORE

• Pandas: IO Reading & Writing - pd.read_sql() pulls query results straight into a DataFrame
• Git Complete Setup Guide
• ML Study Notes - SQL is typically the data-extraction layer feeding into pandas/sklearn pipelines

How to use this vault section

Same skeleton throughout: Definition -> Syntax -> Key Parameters/Clauses -> Examples -> Notes/Gotchas. Use Ctrl/Cmd+O and type “PostgreSQL” or the note number to jump around.