describe_table
Retrieve detailed PostgreSQL table schema information including column names, data types, nullability, defaults, primary keys, and foreign keys to understand database structure before querying.
Instructions
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 keysInput Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| table_name | Yes | ||
| schema_name | No | public |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| result | Yes |
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)})