Avanti Fellows PostgreSQL MCP Server

Instructions

Returns column names, types, nullability, and defaults.
Use this to understand table structure before querying.

Args:
    table_name: Name of the table
    schema_name: Schema name (default: public)

Returns:
    JSON with columns, primary keys, and foreign keys

Implementation Reference

• mcp_postgres/server.py:103-171 (handler)

  The handler function decorated with @mcp.tool(), implementing the describe_table tool. It fetches column information, primary keys, and foreign keys from PostgreSQL system catalogs and returns a JSON description of the table schema.

  @mcp.tool()
  async def describe_table(table_name: str, schema_name: str = "public") -> str:
      """Get detailed schema information for a table.
  
      Returns column names, types, nullability, and defaults.
      Use this to understand table structure before querying.
  
      Args:
          table_name: Name of the table
          schema_name: Schema name (default: public)
  
      Returns:
          JSON with columns, primary keys, and foreign keys
      """
      columns_sql = """
          SELECT
              column_name,
              data_type,
              is_nullable,
              column_default,
              character_maximum_length
          FROM information_schema.columns
          WHERE table_schema = $1 AND table_name = $2
          ORDER BY ordinal_position
      """
  
      pk_sql = """
          SELECT a.attname as column_name
          FROM pg_index i
          JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
          JOIN pg_class c ON c.oid = i.indrelid
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE i.indisprimary
          AND n.nspname = $1
          AND c.relname = $2
      """
  
      fk_sql = """
          SELECT
              kcu.column_name,
              ccu.table_schema AS foreign_schema,
              ccu.table_name AS foreign_table,
              ccu.column_name AS foreign_column
          FROM information_schema.table_constraints AS tc
          JOIN information_schema.key_column_usage AS kcu
              ON tc.constraint_name = kcu.constraint_name
              AND tc.table_schema = kcu.table_schema
          JOIN information_schema.constraint_column_usage AS ccu
              ON ccu.constraint_name = tc.constraint_name
          WHERE tc.constraint_type = 'FOREIGN KEY'
          AND tc.table_schema = $1
          AND tc.table_name = $2
      """
  
      try:
          async with get_connection() as conn:
              columns = await conn.fetch(columns_sql, schema_name, table_name)
              pks = await conn.fetch(pk_sql, schema_name, table_name)
              fks = await conn.fetch(fk_sql, schema_name, table_name)
  
              result = {
                  "table": f"{schema_name}.{table_name}",
                  "columns": [dict(row) for row in columns],
                  "primary_keys": [row["column_name"] for row in pks],
                  "foreign_keys": [dict(row) for row in fks],
              }
              return json.dumps(result, indent=2, default=str)
      except Exception as e:
          return json.dumps({"error": str(e)})