-- Create roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin WITH LOGIN PASSWORD 'secure_password';

-- Grant permissions to roles

-- Create table with tsvector column
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  author VARCHAR(100),

-- PostgreSQL Declarative Partitioning (10+)

-- Create partitioned table by date range
CREATE TABLE measurements (
  id BIGSERIAL,
  sensor_id INT NOT NULL,

-- Primary server configuration (postgresql.conf)
-- wal_level = replica
-- max_wal_senders = 10
-- wal_keep_size = 64MB
-- hot_standby = on

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

-- Unnormalized (0NF): Repeating groups
CREATE TABLE orders_bad (
  order_id INT,
  customer_name VARCHAR(100),
  customer_email VARCHAR(100),
  product1 VARCHAR(100),

-- Basic transaction
BEGIN;

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

-- Basic EXPLAIN
EXPLAIN
SELECT * FROM users WHERE email = 'alice@example.com';

-- EXPLAIN with cost and row estimates
-- Output shows: Seq Scan on users (cost=0.00..15.50 rows=1 width=100)

-- Create table with JSONB column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  metadata JSONB DEFAULT '{}'::jsonb

-- Basic CTE
WITH high_value_customers AS (
  SELECT
    user_id,
    SUM(total) as lifetime_value
  FROM orders

-- ROW_NUMBER: Unique sequential number
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank,

-- Create basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);