PostgreSQL

Get information about the connected database. Returns comprehensive database information including: - PostgreSQL version - Database name and size - Current user and schema - Server encoding and timezone - Connection limits and active connections

List installed PostgreSQL extensions. Returns all extensions currently installed in the database. Returns: List of extensions with: - Extension name and version - Schema where installed - Description

Get statistics for a table. Returns detailed statistics about table usage and maintenance. Args: - table: Table name (required) - schema: Schema name (default: 'public') Returns: Table statistics including: - Live and dead tuple counts - Last vacuum/analyze timestamps - Sequential and index scan counts - Insert/update/delete counts

List functions and procedures in a schema. Args: - schema: Schema name (default: 'public') Returns: List of functions with: - Function name and type (function, procedure, aggregate, window) - Arguments and return type - Volatility (IMMUTABLE, STABLE, VOLATILE) - Language (SQL, plpgsql, etc.)

Get the full definition of a function or procedure. Args: - name: Function name (required) - schema: Schema name (default: 'public') Returns: Complete function definition including: - CREATE FUNCTION statement - All parameters and return type - Function body

List triggers on a table or all triggers in a schema. Args: - table: Table name (optional - if omitted, lists all triggers in schema) - schema: Schema name (default: 'public') Returns: List of triggers with: - Trigger name and table - Event (INSERT, UPDATE, DELETE, TRUNCATE) - Timing (BEFORE, AFTER, INSTEAD OF) - Level (ROW, STATEMENT) - Associated function

List all sequences in a schema. Args: - schema: Schema name (default: 'public') Returns: List of sequences with: - Sequence name and data type - Start, min, max values - Increment and cycle settings - Current/last value - Owner table (if serial/identity column)

List database roles. Returns all user-created roles (excluding system roles starting with pg_). Returns: List of roles with: - Role name and capabilities - Login/superuser/createdb permissions - Connection limit - Role memberships

List privileges granted on a table. Args: - table: Table name (required) - schema: Schema name (default: 'public') Returns: List of privileges with: - Grantee (role name) - Privilege type (SELECT, INSERT, UPDATE, DELETE, etc.) - Whether privilege is grantable

List active database connections. Returns all active connections to the current database. Returns: List of connections with: - Process ID (pid) - User and application name - Client address and port - Connection state and current query - Wait events (if blocking)

Get PostgreSQL configuration settings. Args: - pattern: Filter settings by name pattern (optional) e.g., 'work_mem', 'shared', 'log' Returns: List of settings with: - Setting name - Current value - Description

Execute a read-only SQL query (SELECT, WITH, EXPLAIN, SHOW). This tool is for querying data from the database. It only allows read operations for safety. Use postgresql_execute for write operations. Args: - sql: The SQL query to execute (must be SELECT, WITH, EXPLAIN, or SHOW) - params: Optional array of parameters for parameterized queries (prevents SQL injection) - format: Response format ('json', 'markdown', or 'table') Examples: - SELECT * FROM users WHERE active = $1 - WITH cte AS (SELECT ...) SELECT * FROM cte - SHOW search_path Returns: Query results with rows, row count, column info, and execution time.

Execute a write SQL command (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, etc.). This tool executes commands that modify the database. Use with caution. For safety, always use parameterized queries to prevent SQL injection. Args: - sql: The SQL command to execute - params: Optional array of parameters for parameterized queries Examples: - INSERT INTO users (name, email) VALUES ($1, $2) - UPDATE users SET active = $1 WHERE id = $2 - DELETE FROM users WHERE id = $1 - CREATE TABLE new_table (id serial PRIMARY KEY, name text) Returns: Command result with affected row count and execution time.

Get the execution plan for a SQL query. Analyzes how PostgreSQL will execute the query without actually running it. Use ANALYZE option to execute the query and see actual timings. Args: - sql: The SQL query to explain - params: Optional query parameters - analyze: Run the query and show actual timings (default: false) - buffers: Show buffer usage information (requires analyze) - format: Output format ('json' or 'text') Returns: Query execution plan showing: - Scan types (sequential, index, etc.) - Join strategies - Cost estimates - Actual timings (if analyze=true)

List all database schemas. Returns all user-created schemas in the database, excluding system schemas (pg_catalog, information_schema, pg_toast). Returns: List of schemas with name and owner.

List all tables in a schema. Returns tables, views, and foreign tables with size information. Args: - schema: Schema name (default: 'public') - format: Response format Returns: List of tables with: - Table name and type (BASE TABLE, VIEW, FOREIGN TABLE) - Estimated row count - Total size, table size, index size

Get complete information about a table. Returns detailed information including columns, indexes, constraints, and foreign keys for the specified table. Args: - table: Table name (required) - schema: Schema name (default: 'public') Returns: Complete table structure: - Table info (type, size, row estimate) - All columns with types, nullability, defaults - All indexes with columns and types - All constraints (PK, FK, UNIQUE, CHECK) - Foreign key relationships

List all columns in a table. Args: - table: Table name (required) - schema: Schema name (default: 'public') Returns: List of columns with: - Column name and position - Data type with precision/length - Nullability and default value - Identity/generated column info - Comments

List all indexes on a table. Args: - table: Table name (required) - schema: Schema name (default: 'public') Returns: List of indexes with: - Index name and type (btree, hash, gin, gist, etc.) - Indexed columns - Unique/primary flags - Index size - Full definition

List all constraints on a table. Args: - table: Table name (required) - schema: Schema name (default: 'public') Returns: List of constraints: - PRIMARY KEY - FOREIGN KEY (with references) - UNIQUE - CHECK - EXCLUSION

List all foreign keys for a table. Args: - table: Table name (required) - schema: Schema name (default: 'public') Returns: List of foreign keys with: - Constraint name - Local columns - Referenced table and columns - ON UPDATE and ON DELETE actions