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

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

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

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

---