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:

1. database_name - Target database (e.g., order_development, postgres)
2. sql_query - SQL query to execute (must be quoted)
3. 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

1. Quote queries: Always quote SQL queries to handle spaces and special characters
2. Limit results: Use LIMIT for large tables to avoid overwhelming output
3. Test first: Test UPDATE/DELETE queries with SELECT first
4. Use RETURNING: For INSERT/UPDATE/DELETE, use RETURNING * to see affected rows
5. Transaction safety: The skill executes single queries; use multiple calls for related operations
6. 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