> ## Documentation Index
> Fetch the complete documentation index at: https://pgtofu.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Features

> Complete PostgreSQL feature support in pgtofu

## Tables

### Basic Tables

```sql theme={null}
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```

### All Column Types

pgtofu supports all PostgreSQL built-in types:

<AccordionGroup>
  <Accordion title="Numeric Types">
    | Type               | Aliases       | Description           |
    | ------------------ | ------------- | --------------------- |
    | `smallint`         | `int2`        | 2-byte integer        |
    | `integer`          | `int`, `int4` | 4-byte integer        |
    | `bigint`           | `int8`        | 8-byte integer        |
    | `numeric(p,s)`     | `decimal`     | Arbitrary precision   |
    | `real`             | `float4`      | 4-byte floating point |
    | `double precision` | `float8`      | 8-byte floating point |
    | `smallserial`      | `serial2`     | Auto-increment 2-byte |
    | `serial`           | `serial4`     | Auto-increment 4-byte |
    | `bigserial`        | `serial8`     | Auto-increment 8-byte |
  </Accordion>

  <Accordion title="Character Types">
    | Type                   | Description                 |
    | ---------------------- | --------------------------- |
    | `character varying(n)` | Variable-length with limit  |
    | `varchar(n)`           | Alias for character varying |
    | `character(n)`         | Fixed-length, blank padded  |
    | `char(n)`              | Alias for character         |
    | `text`                 | Variable unlimited length   |
  </Accordion>

  <Accordion title="Date/Time Types">
    | Type                       | Description                 |
    | -------------------------- | --------------------------- |
    | `timestamp`                | Date and time (no TZ)       |
    | `timestamp with time zone` | Date and time with TZ       |
    | `timestamptz`              | Alias for timestamp with TZ |
    | `date`                     | Date only                   |
    | `time`                     | Time only (no TZ)           |
    | `time with time zone`      | Time with TZ                |
    | `interval`                 | Time span                   |
  </Accordion>

  <Accordion title="Boolean & Binary">
    | Type      | Description       |
    | --------- | ----------------- |
    | `boolean` | True/false        |
    | `bool`    | Alias for boolean |
    | `bytea`   | Binary data       |
  </Accordion>

  <Accordion title="UUID & JSON">
    | Type    | Description               |
    | ------- | ------------------------- |
    | `uuid`  | UUID (requires uuid-ossp) |
    | `json`  | JSON text                 |
    | `jsonb` | Binary JSON (indexable)   |
  </Accordion>

  <Accordion title="Network & Geometric">
    | Type                        | Description          |
    | --------------------------- | -------------------- |
    | `inet`                      | IPv4 or IPv6 host    |
    | `cidr`                      | IPv4 or IPv6 network |
    | `macaddr`                   | MAC address          |
    | `point`, `line`, `box`      | Geometric types      |
    | `circle`, `path`, `polygon` | More geometric       |
  </Accordion>

  <Accordion title="Arrays">
    | Type               | Description        |
    | ------------------ | ------------------ |
    | `integer[]`        | 1D integer array   |
    | `text[][]`         | 2D text array      |
    | Any type with `[]` | Array of that type |
  </Accordion>
</AccordionGroup>

### Identity Columns

Modern alternative to SERIAL (PostgreSQL 10+):

```sql theme={null}
CREATE TABLE products (
    -- GENERATED ALWAYS (recommended)
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    -- GENERATED BY DEFAULT (allows manual inserts)
    legacy_id BIGINT GENERATED BY DEFAULT AS IDENTITY
);
```

### Generated Columns

Computed columns (PostgreSQL 12+):

```sql theme={null}
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    quantity INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    -- Stored generated column
    total NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);
```

## Constraints

### Primary Key

```sql theme={null}
-- Inline
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY
);

-- Named constraint
CREATE TABLE users (
    id BIGSERIAL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

-- Composite primary key
CREATE TABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    PRIMARY KEY (order_id, product_id)
);
```

### Foreign Keys

```sql theme={null}
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,

    -- Inline reference
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);
```

**Referential Actions:**

* `ON DELETE CASCADE` - Delete dependent rows
* `ON DELETE SET NULL` - Set FK to NULL
* `ON DELETE SET DEFAULT` - Set FK to default
* `ON DELETE RESTRICT` - Prevent deletion (default)
* `ON DELETE NO ACTION` - Similar to RESTRICT

### Unique Constraints

```sql theme={null}
-- Single column
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

-- Composite unique
ALTER TABLE order_items ADD CONSTRAINT order_items_unique
    UNIQUE (order_id, product_id);

-- Partial unique (with index)
CREATE UNIQUE INDEX idx_users_active_email ON users(email)
    WHERE status = 'active';
```

### Check Constraints

```sql theme={null}
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    price NUMERIC(10,2) NOT NULL,
    quantity INT NOT NULL,

    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_quantity_non_negative CHECK (quantity >= 0)
);
```

### Exclusion Constraints

```sql theme={null}
CREATE TABLE reservations (
    id BIGSERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    during TSTZRANGE NOT NULL,

    CONSTRAINT reservations_no_overlap
        EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
```

### Deferrable Constraints

```sql theme={null}
CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    manager_id BIGINT,

    CONSTRAINT fk_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(id)
        DEFERRABLE INITIALLY DEFERRED
);
```

## Indexes

### Basic Indexes

```sql theme={null}
-- B-tree (default)
CREATE INDEX idx_users_email ON users(email);

-- Descending order
CREATE INDEX idx_orders_date ON orders(created_at DESC);

-- Multiple columns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
```

### Index Types

```sql theme={null}
-- Hash (equality only)
CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- GIN (arrays, JSONB, full-text)
CREATE INDEX idx_products_tags ON products USING gin(tags);
CREATE INDEX idx_users_data ON users USING gin(metadata jsonb_path_ops);

-- GiST (geometric, range, full-text)
CREATE INDEX idx_locations ON places USING gist(location);

-- BRIN (very large tables, sorted data)
CREATE INDEX idx_logs_time ON logs USING brin(created_at);

-- SP-GiST (non-balanced data)
CREATE INDEX idx_ips ON connections USING spgist(ip_address inet_ops);
```

### Partial Indexes

```sql theme={null}
-- Only index active users
CREATE INDEX idx_users_active ON users(email)
    WHERE status = 'active';

-- Only index unprocessed orders
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE processed_at IS NULL;
```

### Covering Indexes (PostgreSQL 11+)

```sql theme={null}
-- Include columns for index-only scans
CREATE INDEX idx_orders_user ON orders(user_id)
    INCLUDE (status, total);
```

### Expression Indexes

```sql theme={null}
-- Index on lowercase email
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Index on JSONB field
CREATE INDEX idx_users_city ON users((data->>'city'));

-- Index on date part
CREATE INDEX idx_orders_month ON orders(date_trunc('month', created_at));
```

## Views

### Regular Views

```sql theme={null}
CREATE VIEW active_users AS
SELECT id, email, name, created_at
FROM users
WHERE status = 'active';

-- With check option (prevents invalid inserts)
CREATE VIEW premium_users AS
SELECT * FROM users WHERE plan = 'premium'
WITH CHECK OPTION;
```

### Materialized Views

```sql theme={null}
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', created_at) AS month,
    count(*) AS order_count,
    sum(total) AS revenue
FROM orders
GROUP BY 1
WITH DATA;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales;

-- Concurrently (requires unique index)
CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
```

## Functions

### PL/pgSQL Functions

```sql theme={null}
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT)
RETURNS TABLE(order_id BIGINT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT id, total FROM orders WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql STABLE;
```

### SQL Functions

```sql theme={null}
CREATE OR REPLACE FUNCTION user_order_count(uid BIGINT)
RETURNS INT AS $$
    SELECT count(*)::INT FROM orders WHERE user_id = uid;
$$ LANGUAGE sql STABLE;
```

### Function Attributes

```sql theme={null}
CREATE FUNCTION example()
RETURNS void AS $$
    -- function body
$$ LANGUAGE plpgsql
VOLATILE           -- Can modify data (default)
-- STABLE          -- Returns same result for same args in same statement
-- IMMUTABLE       -- Returns same result for same args always
STRICT             -- Returns NULL if any arg is NULL
SECURITY DEFINER   -- Runs with function owner's permissions
PARALLEL SAFE;     -- Can run in parallel query
```

## Triggers

### Row-Level Triggers

```sql theme={null}
-- Before insert/update
CREATE TRIGGER update_users_timestamp
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- After insert
CREATE TRIGGER audit_user_creation
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_created();
```

### Statement-Level Triggers

```sql theme={null}
CREATE TRIGGER refresh_stats_after_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION refresh_order_stats();
```

### Conditional Triggers

```sql theme={null}
CREATE TRIGGER notify_high_value_order
    AFTER INSERT ON orders
    FOR EACH ROW
    WHEN (NEW.total > 1000)
    EXECUTE FUNCTION notify_sales_team();
```

## Custom Types

### Enum Types

```sql theme={null}
CREATE TYPE order_status AS ENUM (
    'pending',
    'confirmed',
    'shipped',
    'delivered',
    'cancelled'
);

-- Use in table
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);
```

### Composite Types

```sql theme={null}
CREATE TYPE address AS (
    street VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    country VARCHAR(100),
    postal_code VARCHAR(20)
);

-- Use in table
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    billing_address address,
    shipping_address address
);
```

### Domain Types

```sql theme={null}
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN positive_int AS INTEGER
    CHECK (VALUE > 0);

-- Use in table
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email email_address NOT NULL,
    age positive_int
);
```

## Extensions

```sql theme={null}
-- Common extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";     -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";      -- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS "pg_trgm";       -- Trigram similarity
CREATE EXTENSION IF NOT EXISTS "btree_gin";     -- GIN for scalars
CREATE EXTENSION IF NOT EXISTS "btree_gist";    -- GiST for scalars
CREATE EXTENSION IF NOT EXISTS "hstore";        -- Key-value store
CREATE EXTENSION IF NOT EXISTS "citext";        -- Case-insensitive text
CREATE EXTENSION IF NOT EXISTS "postgis";       -- Geographic objects
CREATE EXTENSION IF NOT EXISTS "timescaledb";   -- Time-series
```

## Sequences

```sql theme={null}
-- Standalone sequence
CREATE SEQUENCE order_number_seq
    START WITH 1000
    INCREMENT BY 1
    NO MAXVALUE
    CACHE 10;

-- Use in table
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_number BIGINT NOT NULL DEFAULT nextval('order_number_seq')
);
```

## Comments

```sql theme={null}
COMMENT ON TABLE users IS 'Application users with authentication information';
COMMENT ON COLUMN users.status IS 'Account status: active, suspended, or deleted';
COMMENT ON INDEX idx_users_email IS 'Index for email lookups during authentication';
COMMENT ON FUNCTION update_updated_at() IS 'Trigger function to update updated_at timestamp';
```

## See Also

* [TimescaleDB Features](/features/timescaledb) - Time-series extensions
* [Partitioning](/features/partitioning) - Table partitioning
* [How It Works](/concepts/how-it-works) - Schema file format and processing