brew install postgresql
brew services start postgresql

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Connect
psql -U postgres

# With database
psql -U postgres myapp

# With host and port
psql -h localhost -p 5432 -U postgres myapp

-- Meta commands (backslash commands)
\help                    -- SQL help
\?                       -- psql help
\l                       -- List databases
\c myapp                 -- Connect to database
\d                       -- List tables
\d users                 -- Describe table
\dt                      -- List tables only
\di                      -- List indexes
\du                      -- List users
\dv                      -- List views
\df                      -- List functions
\x                       -- Toggle expanded display
\i file.sql              -- Execute file
\o output.txt            -- Output to file
\timing                  -- Toggle query timing

CREATE DATABASE myapp;
CREATE DATABASE myapp WITH OWNER = myuser;
CREATE DATABASE myapp ENCODING = 'UTF8';
CREATE DATABASE myapp TEMPLATE = template0;

\c myapp

\l
\l+

DROP DATABASE myapp;
DROP DATABASE IF EXISTS myapp;

SELECT current_database();
SELECT pg_database.datname FROM pg_database WHERE datistemplate = false;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

\d users
\d+ users

-- Add column
ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users ADD COLUMN phone VARCHAR(20) UNIQUE;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

-- Drop column
ALTER TABLE users DROP COLUMN age;

-- Modify column type
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- Rename column
ALTER TABLE users RENAME COLUMN created_at TO registration_date;

-- Rename table
ALTER TABLE users RENAME TO customers;

DROP TABLE orders;
DROP TABLE IF EXISTS orders;
DROP TABLE orders CASCADE;  -- Drop dependent objects

-- Integers
SMALLINT       -- -32768 to 32767
INTEGER         -- -2.1B to 2.1B
BIGINT          -- Very large numbers
SERIAL          -- Auto-incrementing integer
BIGSERIAL       -- Auto-incrementing bigint

-- Arbitrary precision
NUMERIC(10, 2)  -- Exact decimal
DECIMAL(10, 2)  -- Same as NUMERIC

-- Floating point
REAL            -- Single precision (6 digits)
DOUBLE PRECISION -- Double precision (15 digits)

VARCHAR(n)      -- Variable length with limit
CHAR(n)         -- Fixed length, padded
TEXT            -- Variable unlimited length

-- Examples
username VARCHAR(50)
postal_code CHAR(6)
content TEXT

DATE            -- Calendar date
TIME            -- Time of day
TIME WITH TIME ZONE
TIMESTAMP       -- Date and time
TIMESTAMP WITH TIME ZONE  -- Timezone aware
INTERVAL        -- Time span

-- Current values
SELECT NOW();                    -- Current timestamp
SELECT CURRENT_DATE;             -- Current date
SELECT CURRENT_TIME;             -- Current time
SELECT CURRENT_TIMESTAMP;         -- Current timestamp (timezone aware)

BOOLEAN         -- true, false, NULL
-- Also: 't', 'f', 'true', 'false', 'yes', 'no', '1', '0'

CREATE TABLE products (
    name TEXT,
    tags TEXT[]              -- Array of text
);

CREATE TABLE schedules (
    days INTEGER[],
    times TIME[]
);

-- Using arrays
INSERT INTO products VALUES ('Laptop', ARRAY['electronics', 'computer']);
INSERT INTO products VALUES ('Phone', '{"mobile", "electronics"}');

SELECT tags[1] FROM products;  -- First element (1-indexed)
SELECT array_length(tags, 1) FROM products;  -- Array length
SELECT UNNEST(tags) FROM products;  -- Expand array to rows

JSON            -- Validated JSON
JSONB           -- Binary JSON, faster operations

CREATE TABLE events (
    data JSONB
);

INSERT INTO events VALUES ('{"name": "John", "age": 30}');

UUID            -- Universally unique identifier
ENUM            -- Custom enumerated types
POINT           -- Geometric point
ARRAY           -- Dynamic arrays
XML             -- XML data
INET            -- IP address
CIDR            -- Network address
MACADDR         -- MAC address
BYTEA           -- Binary data

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered');

CREATE TABLE orders (
    status order_status DEFAULT 'pending'
);

-- Insert single row
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- Insert with default
INSERT INTO users (username) VALUES ('jane');

-- Insert multiple rows
INSERT INTO users (username, email) VALUES
    ('bob', 'bob@example.com'),
    ('alice', 'alice@example.com');

-- Insert from select
INSERT INTO users (username, email)
SELECT username, email FROM old_users;

-- INSERT ... ON CONFLICT (Upsert)
INSERT INTO users (username, email) VALUES ('john', 'johnnew@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

-- INSERT ... ON CONFLICT DO NOTHING
INSERT INTO users (username, email) VALUES ('john', 'john@example.com')
ON CONFLICT DO NOTHING;

-- RETURNING
INSERT INTO users (username, email) VALUES ('test', 'test@example.com')
RETURNING id, username;

SELECT * FROM users;
SELECT id, username FROM users;
SELECT username AS "User Name" FROM users;
SELECT DISTINCT country FROM users;
SELECT username, price * quantity AS total FROM orders;

-- Update single column
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET email = 'new@example.com',
    username = 'newname'
WHERE id = 1;

-- Update with expression
UPDATE products SET price = price * 0.9 WHERE category = 'sale';

-- RETURNING
UPDATE users SET status = 'active' WHERE id = 1
RETURNING id, username, status;

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'banned';
DELETE FROM users WHERE id IN (SELECT user_id FROM banned_users)
RETURNING id, username;

SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age >= 18 AND country = 'USA';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE status IN ('active', 'premium');
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE
SELECT * FROM users WHERE name LIKE 'J%';      -- Starts with J
SELECT * FROM users WHERE name LIKE '%ohn%';    -- Contains 'ohn'
SELECT * FROM users WHERE name LIKE 'J_hn';     -- J + any + hn

-- ILIKE (case-insensitive)
SELECT * FROM users WHERE name ILIKE 'j%';

-- SIMILAR TO (regex)
SELECT * FROM users WHERE name SIMILAR TO '(John|Jane)%';

-- POSIX regex (PostgreSQL)
SELECT * FROM users WHERE name ~ '^J.*';

SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- COALESCE
SELECT COALESCE(phone, email, 'No contact') AS contact FROM users;

-- NULLIF
SELECT NULLIF(value, 0);  -- Returns NULL if value is 0

-- NVL alias
SELECT COALESCE(phone, email);

SELECT * FROM users
WHERE (age >= 18 AND status = 'active')
   OR role = 'admin';

SELECT * FROM users WHERE NOT status = 'banned';

SELECT * FROM users WHERE country IN ('USA', 'Canada', 'Mexico');
SELECT * FROM users WHERE country NOT IN ('USA', 'Canada');

SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

SELECT username FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

SELECT * FROM users ORDER BY username ASC;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY country ASC, username ASC;

-- NULLS positioning
SELECT * FROM users ORDER BY phone NULLS FIRST;
SELECT * FROM users ORDER BY phone DESC NULLS LAST;

SELECT * FROM users
ORDER BY
    CASE status
        WHEN 'premium' THEN 1
        WHEN 'active' THEN 2
        ELSE 3
    END,
    username ASC;

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users ORDER BY id DESC LIMIT 5;

-- Pagination
SELECT * FROM users LIMIT 20 OFFSET 40;

SELECT * FROM users FETCH FIRST 10 ROWS ONLY;
SELECT * FROM users OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;
SELECT SUM(amount) FROM orders;
SELECT AVG(price) FROM products;
SELECT MIN(created_at) FROM users;
SELECT MAX(salary) FROM employees;

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
HAVING COUNT(*) > 10;

SELECT category, SUM(amount) AS total
FROM products
GROUP BY category
HAVING SUM(amount) > 10000;

SELECT
    date_trunc('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY date_trunc('month', created_at)
ORDER BY month;

SELECT
    department,
    AVG(salary) AS avg_salary,
    AVG(salary) FILTER (WHERE status = 'fulltime') AS avg_fulltime_salary
FROM employees
GROUP BY department;

SELECT u.username, o.order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Multiple joins
SELECT u.username, o.order_id, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Users without orders
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

SELECT u.username, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Exclude matches from both sides
SELECT u.username, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.id IS NULL;

SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p;

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 5
);

SELECT
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

SELECT * FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_salary > 50000;

SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

SELECT u.username, latest_order.order_id, latest_order.order_date
FROM users u
LEFT JOIN LATERAL (
    SELECT order_id, order_date
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY order_date DESC
    LIMIT 1
) latest_order ON true;

CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';

CREATE OR REPLACE VIEW order_summary AS
SELECT
    o.id AS order_id,
    u.username,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity * p.price) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY o.id, u.username;

SELECT * FROM active_users;
SELECT * FROM order_summary WHERE total > 100;

-- Create materialized view
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', order_date) AS month,
    SUM(total) AS revenue
FROM orders
GROUP BY date_trunc('month', order_date);

-- Refresh
REFRESH MATERIALIZED VIEW monthly_sales;
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- Query
SELECT * FROM monthly_sales;

DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;
DROP MATERIALIZED VIEW monthly_sales;

CREATE OR REPLACE PROCEDURE update_user_status(
    p_id INTEGER,
    p_status VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET status = p_status WHERE id = p_id;
END;
$$;

-- Call procedure
CALL update_user_status(1, 'active');

-- Transaction control
CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INTEGER,
    to_account INTEGER,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

CREATE OR REPLACE PROCEDURE example()
LANGUAGE plpgsql
AS $$
DECLARE
    user_count INTEGER;
    user_name VARCHAR(50);
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
    SELECT username INTO user_name FROM users LIMIT 1;
    RAISE NOTICE 'User count: %, first user: %', user_count, user_name;
END;
$$;

CREATE OR REPLACE FUNCTION get_discount(price DECIMAL)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
BEGIN
    IF price > 100 THEN
        RETURN price * 0.20;
    ELSIF price > 50 THEN
        RETURN price * 0.10;
    ELSE
        RETURN 0;
    END IF;
END;
$$;

CREATE OR REPLACE PROCEDURE generate_numbers(count INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 1;
BEGIN
    WHILE i <= count LOOP
        RAISE NOTICE 'Number: %', i;
        i := i + 1;
    END LOOP;
END;
$$;

-- FOR loop over query
CREATE OR REPLACE PROCEDURE process_users()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR user_rec IN SELECT id, username FROM users LOOP
        RAISE NOTICE 'Processing: %', user_rec.username;
    END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END;
$$;

SELECT get_user_count();

CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS TABLE (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100)
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username, u.email
    FROM users u
    WHERE u.id = user_id;
END;
$$;

SELECT * FROM get_user_by_id(1);

CREATE OR REPLACE FUNCTION get_users_by_country(country_name VARCHAR)
RETURNS SETOF users
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM users WHERE country = country_name;
END;
$$;

CREATE OR REPLACE FUNCTION calculate_stats()
RETURNS TABLE (
    total_count BIGINT,
    avg_value NUMERIC,
    max_value NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), AVG(price), MAX(price)
    INTO total_count, avg_value, max_value
    FROM products;
    RETURN;
END;
$$;

-- Function always returns same result for same input
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN a + b;
END;
$$;

-- Function might modify database
CREATE OR REPLACE PROCEDURE update_user_status(
    p_id INTEGER,
    p_status VARCHAR(20)
)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    UPDATE users SET status = p_status WHERE id = p_id;
END;
$$;

-- Single column
CREATE INDEX idx_username ON users(username);

-- Composite
CREATE INDEX idx_country_status ON users(country, status);

-- Unique
CREATE UNIQUE INDEX idx_email ON users(email);

-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- Partial index
CREATE INDEX idx_active_users ON users(last_login)
WHERE status = 'active';

-- Covering index (INCLUDE)
CREATE INDEX idx_covering ON orders(user_id, created_at)
INCLUDE (total, status);

-- B-tree (default)
CREATE INDEX idx_btree ON users(username);

-- Hash
CREATE INDEX idx_hash ON users USING HASH (email);

-- GiST (spatial)
CREATE INDEX idx_location ON locations USING GIST (coordinates);

-- GIN (full-text, JSON)
CREATE INDEX idx_gin_tags ON products USING GIN (tags);

-- BRIN (block range, large tables)
CREATE INDEX idx_brin ON orders USING BRIN (created_at);

-- Show indexes
\d users
\d+ users

-- Force index usage
SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'john';

-- Disable index
ALTER INDEX idx_username DISABLE;
ALTER INDEX idx_username ENABLE;

DROP INDEX idx_username;
DROP INDEX IF EXISTS idx_username;
DROP INDEX CONCURRENTLY idx_username;  -- No lock

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50)
);

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

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10, 2)
);

-- With actions
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE SET NULL
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50),
    UNIQUE (username)
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price > 0),
    quantity INTEGER CHECK (quantity >= 0)
);

-- Named constraint
CREATE TABLE employees (
    salary DECIMAL(10, 2) CONSTRAINT positive_salary CHECK (salary > 0)
);

-- Table-level check
CREATE TABLE orders (
    subtotal DECIMAL(10, 2),
    tax DECIMAL(10, 2),
    total DECIMAL(10, 2),
    CHECK (subtotal + tax = total)
);

-- Prevent overlapping reservations
CREATE TABLE room_reservations (
    room_id INTEGER,
    time_range TSRANGE,
    EXCLUDE USING GIST (
        room_id WITH =,
        time_range WITH &&
    )
);

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

-- Or rollback on error
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
COMMIT;

BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    SAVEPOINT after_order;

    INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
    -- If error:
    ROLLBACK TO SAVEPOINT after_order;

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- At session level
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- JSON (stored as text)
-- JSONB (binary, faster, indexed)

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO events (data) VALUES
    ('{"name": "John", "age": 30}'),
    ('{"name": "Jane", "city": "NYC"}');

-- Access operators
SELECT data->>'name' FROM events;  -- As text
SELECT data->'name' FROM events;    -- As JSON

-- Where clause
SELECT * FROM events WHERE data->>'name' = 'John';
SELECT * FROM events WHERE data->'age' > 25;

-- Containment
SELECT * FROM events WHERE data @> '{"name": "John"}';
SELECT * FROM events WHERE data <@ '{"name": "John", "age": 30}';

-- Create JSON
SELECT json_build_object('name', 'John', 'age', 30);
SELECT row_to_json(users.*) FROM users WHERE id = 1;

-- Parse JSON
SELECT json_array_elements('["a", "b", "c"]');
SELECT json_object_keys('{"name": "John", "age": 30}');

-- Extract values
SELECT json_extract_path(data, 'name') FROM events;
SELECT json_extract_path_text(data, 'name') FROM events;

CREATE INDEX idx_events_data ON events USING GIN (data);

-- Query using index
SELECT * FROM events WHERE data @> '{"name": "John"}';
SELECT * FROM events WHERE data ? 'name';  -- Has key 'name'
SELECT * FROM events WHERE data ?| array['name', 'age'];  -- Has any key
SELECT * FROM events WHERE data ?& array['name', 'age'];  -- Has all keys

-- PostgreSQL 14+
CREATE USER john WITH PASSWORD 'password123';
CREATE USER app WITH PASSWORD 'password' VALID UNTIL '2025-12-31';

-- Legacy
CREATE ROLE john LOGIN PASSWORD 'password123';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app;
GRANT ALL PRIVILEGES ON DATABASE myapp TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app;

-- Default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app;

REVOKE INSERT ON users FROM app;
REVOKE ALL PRIVILEGES ON DATABASE myapp FROM app;

CREATE ROLE reader;
GRANT reader TO app;

-- Role with admin option
GRANT reader TO admin WITH ADMIN true;

DROP USER IF EXISTS john;
DROP ROLE IF EXISTS john;

-- Default schema is 'public'
CREATE SCHEMA myschema;

CREATE TABLE myschema.users (...);

-- Search path
SHOW search_path;
SET search_path TO myschema, public;

CREATE SEQUENCE my_sequence START 100 INCREMENT 5;
SELECT nextval('my_sequence');
SELECT currval('my_sequence');
ALTER SEQUENCE my_sequence RESTART WITH 100;

-- Running total
SELECT
    order_date,
    total,
    SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Rank
SELECT
    username,
    total,
    RANK() OVER (ORDER BY total DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY total DESC) AS row_num
FROM orders;

-- Partition
SELECT
    department,
    username,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, SUM(total) AS total
    FROM orders
    GROUP BY user_id
)
SELECT
    au.username,
    COALESCE(uo.total, 0) AS total_spent
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

-- Recursive CTE
WITH RECURSIVE subordinates AS (
    SELECT id, username, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.username, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

-- Get first order per user
SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at;

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...

ANALYZE users;
ANALYZE VERBOSE users;

VACUUM users;
VACUUM FULL users;
VACUUM ANALYZE users;

-- Autovacuum
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.01);

-- Show configuration
SHOW all;
SHOW shared_buffers;

-- Set configuration
ALTER SYSTEM SET shared_buffers = '256MB';
SELECT pg_reload_conf();

-- Session level
SET work_mem = '64MB';
SET LOCAL statement_timeout = '30s';

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Query statistics
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;