PostgreSQL MCP Server

List Tables

List all tables and views in the exposed PostgreSQL schema. Returns table names, types (table/view), and basic metadata. Essential for discovering available data sources and understanding database structure.

Describe Table

Get detailed schema information about a specific table including column names, data types, nullability, defaults, and constraints. Essential for understanding table structure before querying or modifying data.

Get Table Sample

Retrieve sample rows from a table for quick data exploration. Returns a limited number of rows to understand data format, content, and structure without reading the entire table. Use this to preview data before writing complex queries.

Search Tables

Search for tables and views by name pattern across the database. Useful for finding specific datasets when you don't know their exact location or name. Returns matching table names and types.

Get Table Row Count

Get the total number of rows in a table quickly without fetching data. Uses PostgREST's count header for efficient counting. Useful for understanding table size and data volume.

Query Table

Query a table with flexible filtering, sorting, column selection, and pagination. This is the main data retrieval tool. Supports PostgREST's powerful filter operators (eq, gt, gte, lt, lte, like, ilike, in, is, etc.). Use this for most data retrieval needs.

Full-Text Search

Perform full-text search on text columns using PostgreSQL's text search capabilities. Supports different search types (plain, phrase, websearch) and multiple languages. Useful for finding records by keywords or phrases in text fields.

Query with Logical Operators

Execute complex queries with OR, AND, NOT logic. Allows combining multiple conditions in flexible ways. For example, find users who are either under 18 OR over 65, or students who have grade >= 90 AND (age is 14 OR age is NOT between 11 and 17).

Query JSON Columns

Query tables with JSON/JSONB columns and extract specific fields. Use arrow operators to access nested JSON data: -> for JSON objects, ->> for text values. Supports filtering and selecting JSON paths. Example: data->>'email' or data->phones->0->>'number'

Select Distinct Values

Get unique values from a column. Useful for discovering what values exist in a column, building filters, or understanding data distribution. Automatically removes duplicates and sorts results.

Aggregate Query

Perform aggregate calculations on table data: count rows, sum values, calculate averages, find min/max. Supports grouping and filtering. Use this for analytics, reporting, and statistical queries.

Insert Row

Insert a single row into a table. Provide column names and values as a JSON object. Missing columns will use default values or NULL. Returns the inserted row if successful.

Insert Rows (Bulk)

Insert multiple rows into a table in a single operation. Much more efficient than multiple single inserts. Provide an array of objects where each object represents one row. All objects should have the same keys (column names).

Update Rows

Update rows in a table that match the specified filters. You must provide at least one filter to prevent accidentally updating all rows. Returns the updated rows. Use this to modify existing data based on conditions.

Upsert Rows

Insert rows or update them if they already exist (based on primary key or unique constraint). Useful for sync operations where you want to insert new records and update existing ones in a single operation. Handles conflicts automatically.

Delete Rows

Delete rows from a table that match the specified filters. WARNING: You must provide at least one filter to prevent accidentally deleting all rows. Returns the deleted rows. Use with caution - this operation cannot be undone.

List Functions

List all available PostgreSQL functions in the exposed schema. Shows function names, return types, and argument types. Use this to discover what stored procedures and custom functions are available for calling.

Call Function (Read-Only)

Execute a read-only PostgreSQL function using GET. Use this for functions that only read data and don't modify the database. Supports functions that return tables, records, or scalar values. Arguments are passed as query parameters.

Call Function (POST)

Execute a PostgreSQL function that may modify data using POST. Use this for functions that insert, update, delete data, or perform complex operations. Arguments are passed in the request body as JSON. Supports any function regardless of return type.

Describe Function

Get detailed information about a specific function including parameter names, types, return type, and description. Use this to understand how to call a function and what arguments it expects.

List Views

List all views in the exposed PostgreSQL schema. Views are saved queries that can be accessed like tables. Returns view names and definitions. Use this to discover available views for querying.

Query with Embedding

Query a table and automatically include related data from other tables in a single request. Uses foreign key relationships to join data. Much more efficient than making multiple separate queries. Example: fetch posts with their authors, or orders with customer details.

Get View Definition

Get the SQL definition of a view. Shows the underlying query that the view executes. Useful for understanding what data a view provides and how it's constructed.

Execute Raw Query

Execute arbitrary SQL queries through a stored function. NOTE: This requires you to create an 'execute_sql' function in your PostgreSQL database that accepts a query parameter. Use this for complex queries not covered by other tools. WARNING: Be careful with destructive operations.

Get Schema Metadata

Get comprehensive metadata about the database schema including tables, columns, constraints, indexes, and foreign keys. Use this for deep schema introspection, documentation generation, or understanding database structure in detail.