PostgreSQL Query Skill | Agent Skills

PostgreSQL Query

Execute SQL queries and data modifications on PostgreSQL databases running in Docker containers with automatic container detection.

Quick Start

Execute a query using the automated script:

scripts/query_db.sh <database_name> "<sql_query>" [container_id]

Examples:

# Auto-detect container and query users table
scripts/query_db.sh order_development "SELECT * FROM users LIMIT 10"

# Insert data
scripts/query_db.sh order_development "INSERT INTO logs (level, message) VALUES ('INFO', 'Test log')"

# Update records
scripts/query_db.sh order_development "UPDATE users SET active = true WHERE id = 123"

# Specify container explicitly
scripts/query_db.sh order_development "SELECT * FROM orders WHERE status = 'pending'" c091f5a68780

Scripts

query_db.sh

Main query execution script with auto-detection of PostgreSQL containers.

Features:

Auto-detects single running PostgreSQL container
Lists multiple containers when more than one is found
Colored output for better readability
Error handling and validation
Supports all SQL operations (SELECT, INSERT, UPDATE, DELETE)

Arguments:

database_name - Target database (e.g., order_development, postgres)
sql_query - SQL query to execute (must be quoted)
container_id - Optional container ID (auto-detected if omitted)

Connection Details:

User: postgres (default PostgreSQL superuser)
Connects via docker exec to running containers

list_containers.sh

List all running PostgreSQL containers with database information.

scripts/list_containers.sh

Shows:

Container ID and name
Container status
Available databases in each container

Use this when:

Multiple PostgreSQL containers are running
Unsure which database names exist
Need to identify the correct container

Common Workflows

Inspect Table Data

# View table structure
scripts/query_db.sh mydb "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users'"

# View recent records
scripts/query_db.sh mydb "SELECT * FROM orders ORDER BY created_at DESC LIMIT 20"

# Count records
scripts/query_db.sh mydb "SELECT COUNT(*) FROM users WHERE active = true"

Modify Data

# Insert test data
scripts/query_db.sh mydb "INSERT INTO users (email, name) VALUES ('test@example.com', 'Test User') RETURNING id"

# Update records
scripts/query_db.sh mydb "UPDATE products SET price = 29.99 WHERE id = 456"

# Delete old records
scripts/query_db.sh mydb "DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days'"

Database Analysis

# List all tables
scripts/query_db.sh mydb "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"

# Check table sizes
scripts/query_db.sh mydb "SELECT pg_size_pretty(pg_total_relation_size('users')) as size"

# Active connections
scripts/query_db.sh mydb "SELECT COUNT(*) FROM pg_stat_activity WHERE state != 'idle'"

Advanced Usage

Complex Queries

For multi-line queries or queries with special characters, use proper quoting:

scripts/query_db.sh mydb "
  SELECT
    users.email,
    COUNT(orders.id) as order_count,
    SUM(orders.total) as revenue
  FROM users
  LEFT JOIN orders ON users.id = orders.user_id
  GROUP BY users.id, users.email
  ORDER BY revenue DESC
  LIMIT 10
"

Working with Specific Containers

When multiple containers are running, specify the container:

# List containers first
scripts/list_containers.sh

# Query specific container
scripts/query_db.sh order_development "SELECT * FROM users" c091f5a68780

Transaction-like Operations

For related operations, chain multiple queries:

# Create and populate table
scripts/query_db.sh mydb "CREATE TABLE temp_data (id SERIAL, value TEXT)"
scripts/query_db.sh mydb "INSERT INTO temp_data (value) VALUES ('test1'), ('test2')"
scripts/query_db.sh mydb "SELECT * FROM temp_data"

Query Examples

See references/common_queries.md for comprehensive examples of:

Data retrieval patterns
Data modification operations
Table and schema inspection
Joins and relationships
Aggregation and grouping
Database administration queries

Load this reference when working on complex queries or need query pattern examples.

Troubleshooting

No containers found

# Verify PostgreSQL containers are running
docker ps | grep postgres

# Start a container if needed
docker start <container_name>

Multiple containers found

The script will list all found containers. Either:

Specify container ID as third argument
Stop unused containers: docker stop <container_id>

Database does not exist

# List available databases
scripts/list_containers.sh

# Create database if needed
scripts/query_db.sh postgres "CREATE DATABASE mydb"

Permission denied

Ensure Docker is running and accessible:

docker ps  # Should list containers without sudo

Query syntax errors

Ensure query is properly quoted
Test query syntax first with simple SELECT
Check PostgreSQL version compatibility

Connection Information

This skill uses the same connection pattern as the postgres-backup-restore skill:

User: postgres (default superuser)
Method: docker exec -it <container_id> psql
Container detection: Auto-detect from docker ps filtering PostgreSQL images

Best Practices

Quote queries: Always quote SQL queries to handle spaces and special characters
Limit results: Use LIMIT for large tables to avoid overwhelming output
Test first: Test UPDATE/DELETE queries with SELECT first
Use RETURNING: For INSERT/UPDATE/DELETE, use RETURNING * to see affected rows
Transaction safety: The skill executes single queries; use multiple calls for related operations
Database naming: Use consistent database names across development team

Integration with Other Skills

Works well with:

postgres-backup-restore - Use this skill to query restored backup data
Database migrations - Verify migration results with queries
Data debugging - Inspect and modify data during development

Agent Skills: PostgreSQL Query

Install this agent skill to your local

Skill Files