PostgreSQL Lessons

-- Using psql command-line tool
psql -U postgres -d mydb

-- Connection string format
postgresql://user:password@host:5432/database

-- Common psql meta-commands
l          -- list databases
c mydb     -- connect to database
dt         -- list tables
d table    -- describe table
q          -- quit

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY columns]
[LIMIT count]
[OFFSET skip];

SELECT * FROM employees WHERE salary > 70000;
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE hire_date >= '2022-01-01';
SELECT * FROM employees WHERE status != 'inactive';  -- or <>

-- Ascending (default)
SELECT * FROM employees ORDER BY last_name;
SELECT * FROM employees ORDER BY last_name ASC;

-- Descending
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

-- Integer types
SMALLINT        -- 2 bytes, -32768 to 32767
INTEGER / INT   -- 4 bytes, ~±2.1 billion
BIGINT          -- 8 bytes, ~±9.2 quintillion

-- Auto-increment (serial types)
SMALLSERIAL     -- 2-byte auto-increment
SERIAL          -- 4-byte auto-increment
BIGSERIAL       -- 8-byte auto-increment

-- Exact decimal (financial data)
NUMERIC(10, 2)  -- 10 digits total, 2 decimal places
DECIMAL(10, 2)  -- Same as NUMERIC

-- Floating point (approximate)
REAL            -- 4 bytes, 6 decimal digits precision
DOUBLE PRECISION -- 8 bytes, 15 decimal digits precision

CREATE TABLE table_name (
  column1  datatype  [constraints],
  column2  datatype  [constraints],
  ...
  [table_constraints]
);

-- Column-level
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  ...
);

-- Composite primary key
CREATE TABLE order_items (
  order_id    INT,
  product_id  INT,
  quantity    INT,
  PRIMARY KEY (order_id, product_id)
);

-- Departments table
CREATE TABLE departments (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO departments VALUES
  (1, 'Engineering'),
  (2, 'Marketing'),
  (3, 'HR'),
  (4, 'Finance');  -- no employees

-- Employees table
CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  dept_id     INT REFERENCES departments(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',  1),  -- Engineering
  (2, 'Bob',    2),  -- Marketing
  (3, 'Carol',  1),  -- Engineering
  (4, 'Dave',   NULL), -- no department
  (5, 'Eve',    5);  -- non-existent dept

-- Get employees with their department names
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Result: only employees with a valid dept_id
-- Alice | Engineering
-- Bob   | Marketing
-- Carol | Engineering

-- All employees, even those without a department
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Result:
-- Alice | Engineering
-- Bob   | Marketing
-- Carol | Engineering
-- Dave  | NULL          ← Dave has no dept

SELECT
  COUNT(*)              AS total_rows,
  COUNT(salary)         AS non_null_salaries,
  COUNT(DISTINCT dept)  AS unique_depts,
  SUM(salary)           AS total_salary,
  AVG(salary)           AS average_salary,
  MIN(salary)           AS min_salary,
  MAX(salary)           AS max_salary,
  ROUND(AVG(salary), 2) AS avg_rounded
FROM employees;

function_name() OVER (
  [PARTITION BY column, ...]
  [ORDER BY column, ...]
  [ROWS/RANGE frame_spec]
)

-- WITH clause defines the CTE
WITH high_earners AS (
  SELECT name, salary, department
  FROM employees
  WHERE salary > 80000
)
SELECT department, COUNT(*) AS count, AVG(salary) AS avg
FROM high_earners
GROUP BY department;

-- Employees earning above overall average
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

CREATE TABLE products (
  id       SERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  attrs    JSONB,          -- flexible attributes
  tags     TEXT[],         -- array for simple tags
  metadata JSONB
);

INSERT INTO products (name, attrs, tags) VALUES
  ('Widget Pro',
   '{"color": "blue", "weight": 1.5, "dimensions": {"w": 10, "h": 5}}',
   ARRAY['electronics', 'gadget']);

CREATE TABLE articles (
  id       SERIAL PRIMARY KEY,
  title    TEXT NOT NULL,
  tags     TEXT[],           -- array of text
  scores   INTEGER[],        -- array of integers
  matrix   INTEGER[][]       -- 2D array
);

-- Inserting arrays
INSERT INTO articles (title, tags, scores) VALUES
  ('PostgreSQL Tips', ARRAY['database', 'sql', 'tips'], ARRAY[95, 87, 92]),
  ('JavaScript Guide', '{javascript, web, frontend}', '{88, 90, 85}');

-- tsvector: preprocessed document
SELECT to_tsvector('english', 'PostgreSQL is a powerful database system');
-- 'databas':5 'power':4 'postgresql':1 'system':6
-- (stops words removed, stems applied)

-- tsquery: search query
SELECT to_tsquery('english', 'database & powerful');
SELECT plainto_tsquery('english', 'powerful database');

-- B-tree: default, range queries, equality, sorting
CREATE INDEX idx_employees_salary ON employees(salary);
CREATE INDEX idx_employees_name ON employees(last_name, first_name);

-- Hash: only equality checks (rarely needed)
CREATE INDEX idx_hash_email ON users USING HASH (email);

-- GIN: arrays, JSONB, full-text search
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);
CREATE INDEX idx_articles_tags  ON articles USING GIN (tags);

-- BRIN: very large tables with natural ordering (timestamps, sequential IDs)
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- GiST: geometric types, ranges, full-text with ranking
CREATE INDEX idx_events_range ON events USING GIST (during);

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Call it
SELECT add_numbers(10, 20);  -- 30

-- BEFORE: fires before the operation, can modify NEW
-- AFTER: fires after the operation, useful for auditing
-- INSTEAD OF: for views

-- FOR EACH ROW: fires once per affected row
-- FOR EACH STATEMENT: fires once per SQL statement

-- Start transaction
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Commit if all good
COMMIT;

-- Or rollback on error
ROLLBACK;

-- Create a view
CREATE VIEW active_employees AS
SELECT e.id, e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.is_active = TRUE;

-- Query it like a table
SELECT * FROM active_employees WHERE department = 'Engineering';

-- Drop a view
DROP VIEW active_employees;

-- Create or replace
CREATE OR REPLACE VIEW active_employees AS
SELECT ...;