# PostgreSQL Full Schema Queries

This document captures the recommended queries for extracting full schema details from a PostgreSQL database.

## 1) Full Schema (All Tables + Columns)
```sql
SELECT 
    c.table_name,
    c.ordinal_position,
    c.column_name,
    c.data_type,
    c.character_maximum_length,
    c.numeric_precision,
    c.numeric_scale,
    c.is_nullable,
    c.column_default
FROM information_schema.columns c
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position;
```

## 2) Primary Keys + Unique + Check Constraints
```sql
SELECT 
    tc.table_name,
    tc.constraint_type,
    tc.constraint_name,
    kcu.column_name
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
       ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
WHERE tc.table_schema = 'public'
ORDER BY tc.table_name, tc.constraint_type;
```

## 3) All Foreign Keys (With delete/update rules)
```sql
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column,
    rc.update_rule,
    rc.delete_rule,
    tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints rc
  ON rc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name, tc.constraint_name;
```

## 4) All Indexes (With full definition)
```sql
SELECT
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
```

## 5) Single Query (Everything Together)
```sql
SELECT
    t.table_name,

    -- Columns
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'column', c.column_name,
                'type', c.data_type,
                'nullable', c.is_nullable,
                'default', c.column_default
            )
            ORDER BY c.ordinal_position
        )
        FROM information_schema.columns c
        WHERE c.table_schema = 'public'
          AND c.table_name = t.table_name
    ) AS columns,

    -- Indexes
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'index', i.indexname,
                'definition', i.indexdef
            )
        )
        FROM pg_indexes i
        WHERE i.schemaname = 'public'
          AND i.tablename = t.table_name
    ) AS indexes,

    -- Foreign Keys
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'column', kcu.column_name,
                'ref_table', ccu.table_name,
                'ref_column', ccu.column_name,
                'on_update', rc.update_rule,
                'on_delete', rc.delete_rule,
                'constraint', tc.constraint_name
            )
        )
        FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu
          ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu
          ON ccu.constraint_name = tc.constraint_name
        JOIN information_schema.referential_constraints rc
          ON rc.constraint_name = tc.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY'
          AND tc.table_schema = 'public'
          AND tc.table_name = t.table_name
    ) AS foreign_keys

FROM information_schema.tables t
WHERE t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
ORDER BY t.table_name;
```

## Best Method (Most Accurate): pg_dump Schema
```bash
pg_dump -s mtv_sms > schema.sql
```

### Single Table Only
```bash
pg_dump -s -t tbl_customer_subscriptions mtv_sms
```