HeliosDB DDL Reference

Version: 7.0 (PostgreSQL 17 Compatible) Last Updated: January 2026

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
2. CREATE Statements
3. ALTER Statements
4. DROP Statements
5. GRANT/REVOKE Statements
6. Index Management
7. Constraint Management
8. 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	Cached query results
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.

-- 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.

-- 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

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

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

-- 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;

-- 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');

-- 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

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

-- 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).

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

Table with Inheritance

-- 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)

-- 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.

-- 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.

-- 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.

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

Concurrent Index Creation

Create index without blocking writes.

CREATE INDEX CONCURRENTLY idx_large_table_column ON large_table(column);

GiST Index

For geometric and range data.

-- 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.

-- 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.

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (created_at);

HNSW Index (Vector)

For approximate nearest neighbor search.

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.

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

---

CREATE VIEW

Create virtual tables based on queries.

-- 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 cached query results.

-- 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;

-- 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.

-- 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

-- 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

-- 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

-- 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.

-- 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.

-- 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.

-- 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

-- 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

-- 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

-- 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

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

-- Rename table
ALTER TABLE users RENAME TO customers;

Add Constraint

-- 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

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

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

Partition Management

-- 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.

-- 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.

-- 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.

-- 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.

-- 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

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

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

DROP TABLE

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

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

DROP VIEW

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

DROP MATERIALIZED VIEW

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

DROP SEQUENCE

DROP SEQUENCE order_id_seq;
DROP SEQUENCE IF EXISTS unused_seq;

DROP TYPE

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

DROP FUNCTION

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

DROP TRIGGER

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

DROP EXTENSION

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

---

GRANT/REVOKE Statements

Database Privileges

-- 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

-- 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

-- 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

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

Sequence Privileges

-- 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

-- 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.

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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 - SQL function reference
• HTTP REST API - REST API reference
• SQL API Reference - Complete SQL reference

---

Last Updated: January 2026 HeliosDB Version: 7.0.0