---
title: "HeliosDB DDL Reference"
---

# HeliosDB DDL Reference

**Version:** 7.0 (PostgreSQL 17 Compatible)

Complete reference for Data Definition Language (DDL) statements in HeliosDB, including CREATE, ALTER, DROP, GRANT, and REVOKE statements for all database objects.

---

## Table of Contents

1. [Database Objects](#database-objects)
2. [CREATE Statements](#create-statements)
3. [ALTER Statements](#alter-statements)
4. [DROP Statements](#drop-statements)
5. [GRANT/REVOKE Statements](#grantrevoke-statements)
6. [Index Management](#index-management)
7. [Constraint Management](#constraint-management)
8. [Multi-Model Extensions](#multi-model-extensions)

---

## Database Objects

HeliosDB supports the following database objects:

| Object | Description |
|--------|-------------|
| DATABASE | Top-level container for all objects |
| SCHEMA | Namespace within a database |
| TABLE | Structured data storage |
| VIEW | Virtual table based on query |
| MATERIALIZED VIEW | Query results (runs as a view today; stored results planned) |
| INDEX | Data structure for fast lookups |
| SEQUENCE | Auto-incrementing number generator |
| TYPE | Custom data type |
| FUNCTION | Stored procedure or function |
| TRIGGER | Automatic action on data changes |
| EXTENSION | Add-on functionality module |

---

## CREATE Statements

### CREATE DATABASE

Create a new database.

```sql
-- Basic database creation
CREATE DATABASE my_database;

-- With options
CREATE DATABASE analytics
    WITH OWNER = admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0
    CONNECTION LIMIT = 100;

-- If not exists
CREATE DATABASE IF NOT EXISTS staging;
```

**Parameters:**

| Parameter | Description |
|-----------|-------------|
| `OWNER` | Database owner role |
| `ENCODING` | Character encoding (default: UTF8) |
| `LC_COLLATE` | Collation order |
| `LC_CTYPE` | Character classification |
| `TEMPLATE` | Template database |
| `TABLESPACE` | Default tablespace |
| `CONNECTION LIMIT` | Maximum connections |

---

### CREATE SCHEMA

Create a namespace within a database.

```sql
-- Basic schema creation
CREATE SCHEMA analytics;

-- With owner
CREATE SCHEMA reporting AUTHORIZATION admin;

-- If not exists
CREATE SCHEMA IF NOT EXISTS staging;

-- Create schema with objects
CREATE SCHEMA sales
    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        customer_id INTEGER,
        total DECIMAL(10,2)
    )
    CREATE VIEW recent_orders AS
        SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days';
```

---

### CREATE TABLE

Create a new table.

#### Basic Table

```sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```

#### With Constraints

```sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    total DECIMAL(12, 2) NOT NULL,
    shipping_address JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,

    -- Foreign key constraint
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    -- Check constraint
    CONSTRAINT check_total_positive
        CHECK (total >= 0),

    -- Status validation
    CONSTRAINT check_status_valid
        CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);
```

#### Partitioned Table

```sql
-- Range partitioning by date
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(100) NOT NULL,
    payload JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE events_2025_q2 PARTITION OF events
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

CREATE TABLE events_2025_q3 PARTITION OF events
    FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

CREATE TABLE events_2025_q4 PARTITION OF events
    FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');

-- Default partition for unmatched values
CREATE TABLE events_default PARTITION OF events DEFAULT;
```

```sql
-- List partitioning
CREATE TABLE orders_by_region (
    id BIGSERIAL,
    region VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_north_america PARTITION OF orders_by_region
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE orders_europe PARTITION OF orders_by_region
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES', 'IT');

CREATE TABLE orders_asia PARTITION OF orders_by_region
    FOR VALUES IN ('JP', 'CN', 'KR', 'IN');
```

```sql
-- Hash partitioning
CREATE TABLE sessions (
    id UUID PRIMARY KEY,
    user_id BIGINT NOT NULL,
    data JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);
```

#### Table with Vector Column

```sql
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    embedding VECTOR(1536),  -- OpenAI embedding dimension
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create HNSW index for vector similarity search
CREATE INDEX idx_documents_embedding ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
```

#### Temporary Table

```sql
-- Session-scoped temporary table
CREATE TEMPORARY TABLE temp_results (
    id SERIAL PRIMARY KEY,
    value TEXT
);

-- Temporary table that persists data on commit
CREATE TEMPORARY TABLE temp_processing (
    id SERIAL PRIMARY KEY,
    data JSONB
) ON COMMIT PRESERVE ROWS;

-- Temporary table that deletes rows on commit
CREATE TEMPORARY TABLE temp_transaction_log (
    action TEXT,
    timestamp TIMESTAMP
) ON COMMIT DELETE ROWS;

-- Temporary table dropped on commit
CREATE TEMPORARY TABLE temp_staging (
    id INTEGER,
    data TEXT
) ON COMMIT DROP;
```

#### Unlogged Table

Faster writes, but data is lost on crash (good for caching).

```sql
CREATE UNLOGGED TABLE cache_entries (
    key VARCHAR(255) PRIMARY KEY,
    value BYTEA,
    expires_at TIMESTAMP WITH TIME ZONE
);
```

#### Table with Inheritance

```sql
-- Parent table
CREATE TABLE base_audit (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_by VARCHAR(255)
);

-- Child table inherits columns
CREATE TABLE products (
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    sku VARCHAR(50) UNIQUE
) INHERITS (base_audit);
```

---

### CREATE INDEX

Create indexes for query optimization.

#### B-Tree Index (Default)

```sql
-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Multi-column index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

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

-- If not exists
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
```

#### Partial Index

Index only a subset of rows.

```sql
-- Index only active users
CREATE INDEX idx_active_users ON users(created_at)
    WHERE status = 'active';

-- Index non-null values
CREATE INDEX idx_products_discount ON products(discount_percent)
    WHERE discount_percent IS NOT NULL;

-- Index recent data
CREATE INDEX idx_recent_orders ON orders(created_at)
    WHERE created_at > '2025-01-01';
```

#### Expression Index

Index computed expressions.

```sql
-- Index on lowercase email
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Index on JSON field
CREATE INDEX idx_users_metadata_country ON users((metadata->>'country'));

-- Index on date part
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));

-- Index on text search vector
CREATE INDEX idx_articles_content_fts ON articles
    USING GIN (to_tsvector('english', content));
```

#### Covering Index (INCLUDE)

Include non-indexed columns for index-only scans.

```sql
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
    INCLUDE (total, created_at);
```

#### Concurrent Index Creation

Create index without blocking writes.

```sql
CREATE INDEX CONCURRENTLY idx_large_table_column ON large_table(column);
```

#### GiST Index

For geometric and range data.

```sql
-- Geometric data
CREATE INDEX idx_locations_point ON locations USING GIST (point);

-- Range data
CREATE INDEX idx_reservations_period ON reservations USING GIST (period);
```

#### GIN Index

For full-text search and JSON.

```sql
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (
    to_tsvector('english', title || ' ' || content)
);

-- JSONB containment
CREATE INDEX idx_products_attributes ON products USING GIN (attributes jsonb_path_ops);

-- Array containment
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
```

#### BRIN Index

For large, naturally ordered data.

```sql
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (created_at);
```

#### HNSW Index (Vector)

For approximate nearest neighbor search.

```sql
CREATE INDEX idx_embeddings_hnsw ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 200);

-- Available operators:
-- vector_l2_ops     - Euclidean distance (<->)
-- vector_ip_ops     - Inner product (<#>)
-- vector_cosine_ops - Cosine distance (<=>)
```

#### IVFFlat Index (Vector)

For larger datasets with quantization.

```sql
CREATE INDEX idx_embeddings_ivf ON documents
    USING ivfflat (embedding vector_l2_ops)
    WITH (lists = 100);
```

---

### CREATE VIEW

Create virtual tables based on queries.

```sql
-- Simple view
CREATE VIEW active_users AS
    SELECT id, username, email, created_at
    FROM users
    WHERE status = 'active';

-- View with joins
CREATE VIEW order_details AS
    SELECT
        o.id AS order_id,
        o.created_at AS order_date,
        o.total,
        u.username,
        u.email,
        COUNT(oi.id) AS item_count
    FROM orders o
    JOIN users u ON o.user_id = u.id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    GROUP BY o.id, o.created_at, o.total, u.username, u.email;

-- Updatable view (with check option)
CREATE VIEW pending_orders AS
    SELECT * FROM orders WHERE status = 'pending'
    WITH CHECK OPTION;

-- Recursive view
CREATE VIEW employee_hierarchy AS
    WITH RECURSIVE hierarchy AS (
        SELECT id, name, manager_id, 1 AS level
        FROM employees
        WHERE manager_id IS NULL
        UNION ALL
        SELECT e.id, e.name, e.manager_id, h.level + 1
        FROM employees e
        JOIN hierarchy h ON e.manager_id = h.id
    )
    SELECT * FROM hierarchy;

-- Replace existing view
CREATE OR REPLACE VIEW active_users AS
    SELECT id, username, email, created_at, last_login_at
    FROM users
    WHERE status = 'active';
```

---

### CREATE MATERIALIZED VIEW

Create a materialized view.

In the current release a materialized view runs as a view: its query runs each time the view is read, so results are always current, and `REFRESH MATERIALIZED VIEW` is accepted with nothing to refresh. Stored results, indexes on materialized views, `REFRESH ... CONCURRENTLY` and incremental or scheduled refresh are planned. View definitions are held in server memory: re-create them after a server restart.

```sql
-- Basic materialized view
CREATE MATERIALIZED VIEW monthly_sales AS
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        product_category,
        SUM(total) AS total_sales,
        COUNT(*) AS order_count
    FROM orders
    JOIN order_items ON orders.id = order_items.order_id
    JOIN products ON order_items.product_id = products.id
    GROUP BY DATE_TRUNC('month', order_date), product_category
WITH DATA;

-- Create without populating data
CREATE MATERIALIZED VIEW user_stats AS
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
WITH NO DATA;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales;

-- Planned: refresh concurrently (requires unique index)
CREATE UNIQUE INDEX idx_monthly_sales_pk ON monthly_sales(month, product_category);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
```

---

### CREATE SEQUENCE

Create auto-incrementing number generators.

```sql
-- Basic sequence
CREATE SEQUENCE order_id_seq;

-- With options
CREATE SEQUENCE invoice_number_seq
    START WITH 10000
    INCREMENT BY 1
    MINVALUE 10000
    MAXVALUE 99999999
    NO CYCLE
    CACHE 20;

-- Cycling sequence
CREATE SEQUENCE rotation_seq
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 100
    CYCLE;

-- Use sequence
SELECT nextval('order_id_seq');
SELECT currval('order_id_seq');
SELECT setval('order_id_seq', 1000);
```

---

### CREATE TYPE

Create custom data types.

#### Enum Type

```sql
-- Create enum
CREATE TYPE order_status AS ENUM (
    'pending',
    'processing',
    'shipped',
    'delivered',
    'cancelled',
    'refunded'
);

-- Use in table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- Add new value to enum
ALTER TYPE order_status ADD VALUE 'on_hold' AFTER 'processing';
```

#### Composite Type

```sql
-- Create composite type
CREATE TYPE address AS (
    street VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(100)
);

-- Use in table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    billing_address address,
    shipping_address address
);

-- Insert with composite type
INSERT INTO customers (name, billing_address, shipping_address)
VALUES (
    'John Doe',
    ROW('123 Main St', 'New York', 'NY', '10001', 'USA'),
    ROW('456 Oak Ave', 'Los Angeles', 'CA', '90001', 'USA')
);

-- Access composite type fields
SELECT
    name,
    (billing_address).city AS billing_city,
    (shipping_address).city AS shipping_city
FROM customers;
```

#### Domain Type

```sql
-- Create domain with constraints
CREATE DOMAIN email AS VARCHAR(255)
    CHECK (VALUE ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

CREATE DOMAIN positive_amount AS DECIMAL(12, 2)
    CHECK (VALUE > 0);

CREATE DOMAIN us_phone AS VARCHAR(20)
    CHECK (VALUE ~ '^\d{3}-\d{3}-\d{4}$');

-- Use domains
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    email email NOT NULL,
    phone us_phone
);

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount positive_amount NOT NULL
);
```

---

### CREATE FUNCTION

Create stored procedures and functions.

```sql
-- Simple function
CREATE OR REPLACE FUNCTION get_user_full_name(user_id BIGINT)
RETURNS TEXT AS $$
    SELECT first_name || ' ' || last_name
    FROM users
    WHERE id = user_id;
$$ LANGUAGE SQL;

-- Function with multiple statements
CREATE OR REPLACE FUNCTION calculate_order_total(order_id BIGINT)
RETURNS DECIMAL(12, 2) AS $$
DECLARE
    subtotal DECIMAL(12, 2);
    tax_rate DECIMAL(5, 4) := 0.0825;
    shipping DECIMAL(10, 2);
BEGIN
    SELECT SUM(quantity * unit_price)
    INTO subtotal
    FROM order_items
    WHERE order_items.order_id = calculate_order_total.order_id;

    SELECT COALESCE(shipping_cost, 0)
    INTO shipping
    FROM orders
    WHERE id = order_id;

    RETURN subtotal * (1 + tax_rate) + shipping;
END;
$$ LANGUAGE plpgsql;

-- Table-returning function
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT)
RETURNS TABLE (
    order_id BIGINT,
    order_date TIMESTAMP WITH TIME ZONE,
    total DECIMAL(12, 2),
    status VARCHAR(50)
) AS $$
    SELECT id, created_at, total, status
    FROM orders
    WHERE user_id = p_user_id
    ORDER BY created_at DESC;
$$ LANGUAGE SQL;

-- Aggregate function
CREATE OR REPLACE FUNCTION array_concat_agg_sfunc(state TEXT[], value TEXT)
RETURNS TEXT[] AS $$
    SELECT array_append(state, value);
$$ LANGUAGE SQL IMMUTABLE;

CREATE AGGREGATE array_concat_agg(TEXT) (
    SFUNC = array_concat_agg_sfunc,
    STYPE = TEXT[],
    INITCOND = '{}'
);
```

---

### CREATE TRIGGER

Create automatic actions on data changes.

```sql
-- Update timestamp trigger function
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- Audit trail trigger
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END,
        NOW()
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_changes();

-- Conditional trigger
CREATE TRIGGER orders_notify
    AFTER INSERT ON orders
    FOR EACH ROW
    WHEN (NEW.total > 1000)
    EXECUTE FUNCTION notify_large_order();
```

---

### CREATE EXTENSION

Enable additional functionality.

```sql
-- Enable vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enable cryptographic functions
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Enable full-text search dictionaries
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Enable PostGIS (spatial)
CREATE EXTENSION IF NOT EXISTS postgis;

-- View installed extensions
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;
```

---

## ALTER Statements

### ALTER TABLE

Modify table structure.

#### Add Column

```sql
-- Add single column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with default
ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT false;

-- Add column with constraint
ALTER TABLE users ADD COLUMN age INTEGER CHECK (age >= 0 AND age <= 150);

-- Add multiple columns
ALTER TABLE users
    ADD COLUMN first_name VARCHAR(100),
    ADD COLUMN last_name VARCHAR(100),
    ADD COLUMN middle_name VARCHAR(100);
```

#### Drop Column

```sql
-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Drop column cascade (removes dependent objects)
ALTER TABLE users DROP COLUMN IF EXISTS metadata CASCADE;
```

#### Alter Column

```sql
-- Change data type
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- Change type with conversion
ALTER TABLE products ALTER COLUMN price TYPE INTEGER USING (price * 100)::INTEGER;

-- Set default
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Drop default
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Set not null
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Drop not null
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;
```

#### Rename

```sql
-- Rename column
ALTER TABLE users RENAME COLUMN username TO login_name;

-- Rename table
ALTER TABLE users RENAME TO customers;
```

#### Add Constraint

```sql
-- Add primary key
ALTER TABLE orders ADD PRIMARY KEY (id);

-- Add foreign key
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

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

-- Add check constraint
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price > 0);

-- Add exclusion constraint
ALTER TABLE reservations ADD CONSTRAINT no_overlap
    EXCLUDE USING GIST (room_id WITH =, period WITH &&);
```

#### Drop Constraint

```sql
-- Drop constraint by name
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

-- Drop primary key
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
```

#### Partition Management

```sql
-- Attach partition
ALTER TABLE events ATTACH PARTITION events_2026_q1
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

-- Detach partition
ALTER TABLE events DETACH PARTITION events_2024_q1;

-- Detach concurrently
ALTER TABLE events DETACH PARTITION events_2024_q1 CONCURRENTLY;
```

---

### ALTER INDEX

Modify indexes.

```sql
-- Rename index
ALTER INDEX idx_users_email RENAME TO idx_users_email_btree;

-- Set storage parameters
ALTER INDEX idx_users_email SET (fillfactor = 90);

-- Rebuild index
REINDEX INDEX idx_users_email;

-- Rebuild all indexes on table
REINDEX TABLE users;
```

---

### ALTER SEQUENCE

Modify sequences.

```sql
-- Restart sequence
ALTER SEQUENCE order_id_seq RESTART WITH 1000;

-- Change increment
ALTER SEQUENCE order_id_seq INCREMENT BY 10;

-- Set min/max
ALTER SEQUENCE order_id_seq MINVALUE 1 MAXVALUE 999999999;

-- Owner change
ALTER SEQUENCE order_id_seq OWNED BY orders.id;
```

---

### ALTER VIEW

Modify views.

```sql
-- Change owner
ALTER VIEW active_users OWNER TO admin;

-- Rename view
ALTER VIEW active_users RENAME TO live_users;

-- Set options
ALTER VIEW active_users SET (security_barrier = true);
```

---

### ALTER TYPE

Modify custom types.

```sql
-- Add enum value
ALTER TYPE order_status ADD VALUE 'on_hold';

-- Add enum value at specific position
ALTER TYPE order_status ADD VALUE 'awaiting_payment' BEFORE 'processing';

-- Rename enum value (PostgreSQL 10+)
ALTER TYPE order_status RENAME VALUE 'cancelled' TO 'canceled';
```

---

## DROP Statements

### DROP DATABASE

```sql
DROP DATABASE my_database;
DROP DATABASE IF EXISTS staging;

-- Force disconnect users first
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_database';
DROP DATABASE my_database;
```

### DROP SCHEMA

```sql
DROP SCHEMA analytics;
DROP SCHEMA IF EXISTS staging;
DROP SCHEMA reporting CASCADE;  -- Drop all objects in schema
```

### DROP TABLE

```sql
DROP TABLE users;
DROP TABLE IF EXISTS temp_data;
DROP TABLE orders CASCADE;  -- Drop dependent objects
DROP TABLE logs, events, metrics;  -- Multiple tables
```

### DROP INDEX

```sql
DROP INDEX idx_users_email;
DROP INDEX IF EXISTS idx_old_index;
DROP INDEX CONCURRENTLY idx_large_table;  -- Without locking
```

### DROP VIEW

```sql
DROP VIEW active_users;
DROP VIEW IF EXISTS old_view;
DROP VIEW order_summary CASCADE;  -- Drop dependent views
```

### DROP MATERIALIZED VIEW

```sql
DROP MATERIALIZED VIEW monthly_stats;
DROP MATERIALIZED VIEW IF EXISTS cached_data;
```

### DROP SEQUENCE

```sql
DROP SEQUENCE order_id_seq;
DROP SEQUENCE IF EXISTS unused_seq;
```

### DROP TYPE

```sql
DROP TYPE order_status;
DROP TYPE IF EXISTS old_type;
DROP TYPE address CASCADE;
```

### DROP FUNCTION

```sql
DROP FUNCTION get_user_name(BIGINT);
DROP FUNCTION IF EXISTS old_function(TEXT, INTEGER);
DROP FUNCTION calculate_total CASCADE;
```

### DROP TRIGGER

```sql
DROP TRIGGER users_updated_at ON users;
DROP TRIGGER IF EXISTS old_trigger ON orders;
```

### DROP EXTENSION

```sql
DROP EXTENSION vector;
DROP EXTENSION IF EXISTS pg_trgm;
DROP EXTENSION postgis CASCADE;
```

---

## GRANT/REVOKE Statements

### Database Privileges

```sql
-- Grant database access
GRANT CONNECT ON DATABASE mydb TO analyst;

-- Grant all database privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;

-- Revoke access
REVOKE CONNECT ON DATABASE mydb FROM guest;
```

### Schema Privileges

```sql
-- Grant schema usage
GRANT USAGE ON SCHEMA public TO analyst;

-- Grant ability to create objects
GRANT CREATE ON SCHEMA analytics TO developer;

-- Grant all schema privileges
GRANT ALL ON SCHEMA reporting TO admin;
```

### Table Privileges

```sql
-- Grant specific privileges
GRANT SELECT ON users TO analyst;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT ALL ON products TO admin;

-- Grant on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Grant with grant option (can grant to others)
GRANT SELECT ON users TO team_lead WITH GRANT OPTION;

-- Revoke privileges
REVOKE INSERT, UPDATE ON users FROM guest;
REVOKE ALL ON orders FROM temp_user;
```

### Column Privileges

```sql
-- Grant access to specific columns
GRANT SELECT (id, name, email) ON users TO analyst;
GRANT UPDATE (status) ON orders TO support;
```

### Sequence Privileges

```sql
-- Grant sequence usage
GRANT USAGE ON SEQUENCE order_id_seq TO app_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO developer;
```

### Function Privileges

```sql
-- Grant execute privilege
GRANT EXECUTE ON FUNCTION calculate_total(BIGINT) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO developer;
```

### Default Privileges

Set default privileges for future objects.

```sql
-- Default privileges for tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO analyst;

-- Default privileges for sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO app_user;

-- Default privileges for functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT EXECUTE ON FUNCTIONS TO developer;
```

### Role Management

```sql
-- Create role
CREATE ROLE analyst;
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE admin WITH SUPERUSER CREATEDB CREATEROLE;

-- Grant role to user
GRANT analyst TO john;
GRANT developer TO mary;

-- Revoke role
REVOKE analyst FROM john;

-- Alter role
ALTER ROLE app_user WITH PASSWORD 'new_password';
ALTER ROLE developer WITH CONNECTION LIMIT 10;

-- Drop role
DROP ROLE IF EXISTS temp_user;
```

---

## Index Management

### Index Statistics

```sql
-- View index usage
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find unused indexes
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public';

-- Index size
SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
```

### Index Maintenance

```sql
-- Rebuild index
REINDEX INDEX idx_users_email;

-- Rebuild all indexes on table
REINDEX TABLE users;

-- Rebuild all indexes in database
REINDEX DATABASE mydb;

-- Concurrent reindex (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;
```

---

## Constraint Management

### Deferrable Constraints

```sql
-- Create deferrable foreign key
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    DEFERRABLE INITIALLY DEFERRED;

-- Set constraint timing in transaction
BEGIN;
SET CONSTRAINTS fk_orders_customer DEFERRED;
-- ... operations ...
COMMIT;  -- Constraints checked here
```

### Exclusion Constraints

```sql
-- Prevent overlapping ranges
ALTER TABLE reservations ADD CONSTRAINT no_double_booking
    EXCLUDE USING GIST (
        room_id WITH =,
        tstzrange(start_time, end_time) WITH &&
    );
```

---

## Multi-Model Extensions

### Vector Index DDL

```sql
-- Create vector table
CREATE TABLE embeddings (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    vector VECTOR(1536)
);

-- HNSW index with custom parameters
CREATE INDEX idx_embeddings_hnsw ON embeddings
    USING hnsw (vector vector_cosine_ops)
    WITH (
        m = 32,                -- Max connections per node
        ef_construction = 128  -- Size of dynamic candidate list
    );

-- IVFFlat index
CREATE INDEX idx_embeddings_ivf ON embeddings
    USING ivfflat (vector vector_l2_ops)
    WITH (lists = 100);  -- Number of clusters
```

### Time-Series Partitioning

```sql
-- Create time-series table with automatic partitioning
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    metric_name TEXT NOT NULL,
    value DOUBLE PRECISION,
    tags JSONB
) PARTITION BY RANGE (time);

-- Create partition template
CREATE TABLE metrics_template (LIKE metrics);

-- Function to create monthly partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(start_date DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_ts TIMESTAMPTZ;
    end_ts TIMESTAMPTZ;
BEGIN
    partition_name := 'metrics_' || to_char(start_date, 'YYYY_MM');
    start_ts := start_date::TIMESTAMPTZ;
    end_ts := (start_date + INTERVAL '1 month')::TIMESTAMPTZ;

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF metrics FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_ts, end_ts
    );
END;
$$ LANGUAGE plpgsql;
```

---

## See Also

- [SQL Functions Reference](https://www.heliosdb.com/docs/full/reference/api/sql_functions/) - SQL function reference
- [HTTP REST API](https://www.heliosdb.com/docs/full/reference/api/http_rest_api/) - REST API reference
- [SQL API Reference](https://www.heliosdb.com/docs/full/api/sql_api_reference/) - Complete SQL reference

---