postgres-consumption-cli

name

postgres-consumption-cli

description

Execute read-only SQL queries against Azure Database for PostgreSQL Flexible Server via CLI. Default skill for any PostgreSQL data query (SELECT, aggregation, schema exploration, performance analysis). Use when the user wants to: (1) query PostgreSQL data, (2) explore schemas and columns, (3) analyze query performance with EXPLAIN, (4) monitor active connections and locks, (5) export query results to CSV, (6) discover table sizes and statistics. Triggers: "query postgres", "select from", "explore schema", "show tables", "describe table", "explain query", "postgres performance", "export CSV", "count rows", "list columns", "database size", "active queries", "slow queries".

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

PostgreSQL Consumption — CLI Skill

Table of Contents

Task	Reference	Notes
Azure PostgreSQL Topology	COMMON-CORE.md § Azure PostgreSQL Topology
Authentication	COMMON-CLI.md § Authentication Recipes	Entra ID or native auth
SQL via psql	COMMON-CLI.md § SQL via psql	Connect, query, export
Schema Discovery	POSTGRES-CONSUMPTION-CORE.md § Schema Discovery	Tables, columns, indexes
Query Patterns	POSTGRES-CONSUMPTION-CORE.md § Query Patterns	CTEs, window functions, JSONB
Performance Analysis	POSTGRES-CONSUMPTION-CORE.md § Performance Analysis	EXPLAIN, pg_stat_statements
Monitoring	POSTGRES-CONSUMPTION-CORE.md § Monitoring	Connections, locks, sizes
Gotchas	POSTGRES-CONSUMPTION-CORE.md § Gotchas	Common issues and fixes

---

Tool Stack

Tool	Role	Install
psql	Primary: Execute SQL queries. Standard PostgreSQL CLI client.	apt-get install postgresql-client / brew install libpq / winget install PostgreSQL.psql
az CLI	Auth (az login), server discovery, token acquisition.	Pre-installed in most dev environments
jq	Parse JSON from az commands	Pre-installed or trivial

Agent check — verify before first SQL operation:

psql --version 2>/dev/null || echo "INSTALL: apt-get install postgresql-client OR brew install libpq"

---

Connection

Discover Server FQDN

RG="your-resource-group"
SERVER="your-server-name"

FQDN=$(az postgres flexible-server show \
  --resource-group $RG --name $SERVER \
  --query "fullyQualifiedDomainName" -o tsv)

Connect

psql "host=$FQDN port=5432 dbname=your-database user=your-admin sslmode=require"

Connect with Entra ID Token

export PGPASSWORD=$(az account get-access-token \
  --resource https://ossrdbms-aad.database.windows.net \
  --query accessToken -o tsv)

psql "host=$FQDN port=5432 dbname=your-database user=your-entra-user@your-tenant sslmode=require"

---

Agentic Exploration ("Chat With My Data")

Start here for any data exploration request.

Step 1: Discover Available Tables

psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema') ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;"

Step 2: Inspect Table Schema

psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name='your_table' ORDER BY ordinal_position;"

Step 3: Sample Data

psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "SELECT * FROM your_table LIMIT 10;"

Step 4: Run Targeted Query

Generate SQL based on the discovered schema and the user's question.

---

Script Generation

Export to CSV

psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "\COPY (SELECT * FROM your_table WHERE created_at >= '2026-01-01' LIMIT 10000) TO 'output.csv' CSV HEADER"

Formatted Output

# Expanded display (vertical)
psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -x -c "SELECT * FROM your_table LIMIT 5;"

# HTML output
psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -H -c "SELECT * FROM your_table LIMIT 10;" > output.html

---

Must

• Always discover schema before generating queries
• Use LIMIT on exploratory queries against large tables
• Use sslmode=require in all connections
• Prefer EXPLAIN (ANALYZE, BUFFERS) for performance analysis
• Display row counts before running expensive aggregations

Prefer

• CTEs over nested subqueries for readability
• Window functions over self-joins
• pg_stat_statements for identifying slow queries
• \COPY for data export (client-side, no server permissions needed)
• Table/column aliases for readability

Avoid

• SELECT * without LIMIT on tables with unknown row counts
• Running EXPLAIN ANALYZE on INSERT/UPDATE/DELETE (it executes!)
• Unbounded aggregations without WHERE clauses
• Hardcoded connection strings or passwords