PostgreSQL MCP Server (Enhanced)

A Model Context Protocol server that provides both read and write access to PostgreSQL databases. This server enables LLMs to inspect database schemas, execute queries, modify data, and create/modify database schema objects.

Note: This is an enhanced version of the original PostgreSQL MCP server by Anthropic. The original server provides read-only access, while this enhanced version adds write capabilities and schema management.

Components

Tools

Data Query

• query
  • Execute read-only SQL queries against the connected database
  • Input: sql (string): The SQL query to execute
  • All queries are executed within a READ ONLY transaction

Data Modification

• execute

  • Execute a SQL statement that modifies data (INSERT, UPDATE, DELETE)
  • Input: sql (string): The SQL statement to execute
  • Executed within a transaction with proper COMMIT/ROLLBACK handling

• insert

  • Insert a new record into a table
  • Input:
    • table (string): The table name
    • data (object): Key-value pairs where keys are column names and values are the data to insert

• update

  • Update records in a table
  • Input:
    • table (string): The table name
    • data (object): Key-value pairs for the fields to update
    • where (string): The WHERE condition to identify records to update

• delete

  • Delete records from a table
  • Input:
    • table (string): The table name
    • where (string): The WHERE condition to identify records to delete

Schema Management

• createTable

  • Create a new table with specified columns and constraints
  • Input:
    • tableName (string): The table name
    • columns (array): Array of column definitions with name, type, and optional constraints
    • constraints (array): Optional array of table-level constraints

• createFunction

  • Create a PostgreSQL function/procedure
  • Input:
    • name (string): Function name
    • parameters (string): Function parameters
    • returnType (string): Return type
    • language (string): Language (plpgsql, sql, etc.)
    • body (string): Function body
    • options (string): Optional additional function options

• createTrigger

  • Create a trigger on a table
  • Input:
    • name (string): Trigger name
    • tableName (string): Table to apply trigger to
    • functionName (string): Function to call
    • when (string): BEFORE, AFTER, or INSTEAD OF
    • events (array): Array of events (INSERT, UPDATE, DELETE)
    • forEach (string): ROW or STATEMENT
    • condition (string): Optional WHEN condition

• createIndex

  • Create an index on a table
  • Input:
    • tableName (string): Table name
    • indexName (string): Index name
    • columns (array): Columns to index
    • unique (boolean): Whether the index is unique