-- Create database
CREATE DATABASE mydb;

-- Connect to database
c mydb

-- List databases
l

-- Drop database
DROP DATABASE mydb;

-- List tables
dt

-- Describe table
d users

-- List schemas
dn

-- List functions
df

-- Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Alter table
ALTER TABLE users
    ADD COLUMN phone VARCHAR(20),
    ALTER COLUMN name TYPE VARCHAR(150),
    DROP COLUMN age;

-- Drop table
DROP TABLE users;

-- Truncate table
TRUNCATE TABLE users;

-- Rename table
ALTER TABLE old_name RENAME TO new_name;

-- Numeric Types
INTEGER, BIGINT, SMALLINT
NUMERIC(10,2), DECIMAL(10,2)
REAL, DOUBLE PRECISION
SERIAL, BIGSERIAL

-- String Types
CHAR(10), VARCHAR(255), TEXT
CITEXT (case-insensitive)

-- Date and Time
DATE, TIME, TIMESTAMP
TIMESTAMPTZ (with timezone)
INTERVAL

-- Binary Types
BYTEA

-- Other Types
BOOLEAN, ENUM
JSON, JSONB
ARRAY
UUID
GEOMETRY, GEOGRAPHY

-- Basic SELECT
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With conditions
SELECT * FROM users WHERE age > 18;

-- Sorting
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY age DESC, name ASC;

-- Limiting results
SELECT * FROM users LIMIT 10;
SELECT * FROM users OFFSET 20 LIMIT 10;

-- Distinct values
SELECT DISTINCT ON (country) country, name
FROM users
ORDER BY country, created_at DESC;

-- Aliases
SELECT u.name AS user_name, p.title AS post_title
FROM users u
JOIN posts p ON u.id = p.user_id;

-- Inner Join
SELECT users.name, orders.order_number
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- Left Join
SELECT users.name, orders.order_number
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Right Join
SELECT users.name, orders.order_number
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- Full Outer Join
SELECT users.name, orders.order_number
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

-- Cross Join
SELECT users.name, products.name
FROM users
CROSS JOIN products;

-- Lateral Join
SELECT u.name, p.title
FROM users u
CROSS JOIN LATERAL (
    SELECT title FROM posts
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) p;

-- Basic aggregates
SELECT 
    COUNT(*) as total_users,
    AVG(age) as average_age,
    MAX(age) as max_age,
    MIN(age) as min_age,
    SUM(points) as total_points
FROM users;

-- Group By
SELECT 
    country,
    COUNT(*) as user_count,
    AVG(age) as avg_age
FROM users
GROUP BY country
HAVING COUNT(*) > 10;

-- Window functions
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as salary_rank,
    LAG(salary) OVER (ORDER BY salary) as prev_salary,
    LEAD(salary) OVER (ORDER BY salary) as next_salary
FROM employees;

-- Single row insert
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

-- Multiple rows
INSERT INTO users (name, email, age)
VALUES 
    ('Jane Doe', 'jane@example.com', 25),
    ('Bob Smith', 'bob@example.com', 35);

-- Insert from select
INSERT INTO new_users (name, email)
SELECT name, email FROM old_users
WHERE status = 'active';

-- Insert with returning
INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com')
RETURNING id, name;

-- Upsert (insert or update)
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

-- Basic update
UPDATE users
SET name = 'John Smith',
    email = 'john.smith@example.com'
WHERE id = 1;

-- Update with join
UPDATE users u
SET total_orders = u.total_orders + 1
FROM orders o
WHERE u.id = o.user_id
AND o.status = 'completed';

-- Update with returning
UPDATE users
SET status = 'inactive'
WHERE last_login < NOW() - INTERVAL '1 year'
RETURNING id, name;

-- Update with case
UPDATE products
SET price = CASE
    WHEN category = 'premium' THEN price * 1.1
    WHEN category = 'standard' THEN price * 1.05
    ELSE price
END;

-- Basic delete
DELETE FROM users WHERE id = 1;

-- Delete with join
DELETE FROM users u
USING inactive_users i
WHERE u.id = i.user_id
AND i.last_login < NOW() - INTERVAL '1 year';

-- Delete with returning
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '1 month'
RETURNING id;

-- Delete with order and limit
DELETE FROM users
WHERE status = 'inactive'
ORDER BY last_login ASC
LIMIT 100;

-- Create index
CREATE INDEX idx_email ON users(email);

-- Create unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Create composite index
CREATE INDEX idx_name_age ON users(name, age);

-- Create partial index
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Create expression index
CREATE INDEX idx_name_lower ON users(LOWER(name));

-- Create GIN index
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- Drop index
DROP INDEX idx_email;

-- Show indexes
di

-- Basic transaction
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Transaction with rollback
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    UPDATE users SET balance = balance - 100 WHERE id = 1;
    
    IF (SELECT balance FROM users WHERE id = 1) < 0 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

-- Savepoints
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    SAVEPOINT order_created;
    
    UPDATE users SET balance = balance - 100 WHERE id = 1;
    IF (SELECT balance FROM users WHERE id = 1) < 0 THEN
        ROLLBACK TO SAVEPOINT order_created;
    END IF;
COMMIT;

-- Create function
CREATE OR REPLACE FUNCTION get_user_orders(user_id INTEGER)
RETURNS TABLE (
    order_id INTEGER,
    order_number VARCHAR,
    total DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.order_number, o.total
    FROM orders o
    WHERE o.user_id = get_user_orders.user_id;
END;
$$ LANGUAGE plpgsql;

-- Create procedure
CREATE OR REPLACE PROCEDURE update_user_balance(
    p_user_id INTEGER,
    p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users 
    SET balance = balance + p_amount 
    WHERE id = p_user_id;
    
    INSERT INTO transactions (user_id, amount)
    VALUES (p_user_id, p_amount);
    
    COMMIT;
END;
$$;

-- Explain query
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

-- Force index
SELECT * FROM users
WHERE email = 'john@example.com'
USING INDEX idx_email;

-- Vacuum analyze
VACUUM ANALYZE users;

-- Show query plan
EXPLAIN (FORMAT JSON) SELECT * FROM users;

-- Show running queries
SELECT * FROM pg_stat_activity;

-- Cancel query
SELECT pg_cancel_backend(pid);

-- Show configuration
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

-- Set configuration
ALTER SYSTEM SET shared_buffers = '1GB';
ALTER SYSTEM SET work_mem = '64MB';

-- Show statistics
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;

-- Show table size
SELECT pg_size_pretty(pg_total_relation_size('users'));

-- Show index usage
SELECT * FROM pg_stat_user_indexes;

-- Create backup
pg_dump -U username database_name > backup.sql

-- Restore from backup
psql -U username database_name < backup.sql

-- Backup specific tables
pg_dump -U username -t table1 -t table2 database_name > backup.sql

-- Backup with compression
pg_dump -U username database_name | gzip > backup.sql.gz

-- Restore compressed backup
gunzip -c backup.sql.gz | psql -U username database_name

-- Backup with custom format
pg_dump -U username -Fc database_name > backup.dump

-- Restore custom format
pg_restore -U username -d database_name backup.dump