Let's face it: writing SQL can be a grind. Whether you're wrestling with a sluggish query, designing a schema from scratch, or trying to generate a monthly report, the syntax and optimization tricks often slow you down. But what if you could delegate the tedious parts to an AI assistant that already knows PostgreSQL inside out? This isn't about replacing your skills—it's about amplifying them with prompts that turn ChatGPT or Claude into a sharp DBA and data analyst. Here are 15 prompts I actually use in my workflow, each with a real-world example and customization tips.
1. The Query Generator: From Plain English to SQL
Prompt: "Act as a PostgreSQL expert. Convert the following business question into an optimized SQL query: [describe your question]. Assume tables: [table names with columns]. Use appropriate joins, aggregations, and window functions where suitable. Add comments to explain each part."
Example: I needed to find the top-selling products by revenue per month for an e-commerce app. The prompt generated a query with DATE_TRUNC('month', order_date) and ROW_NUMBER() OVER (PARTITION BY month ORDER BY revenue DESC). It saved me 15 minutes of manual writing.
Customization: Always provide the exact schema—column names, data types, and relationships. The more context, the more accurate the SQL.
2. The Performance Detective: Optimize a Slow Query
Prompt: "Analyze this slow PostgreSQL query: [paste EXPLAIN ANALYZE output and the query]. Identify bottlenecks (seq scans, missing indexes, inefficient joins). Suggest concrete optimizations: index creation, query rewrite, or configuration changes. Explain the expected impact."
Example: A query on a 5-million-row table took 8 seconds. The prompt noticed a full table scan on a WHERE user_id = ... and suggested a composite index. After creating it, runtime dropped to 50 ms.
Customization: Include the EXPLAIN (ANALYZE, BUFFERS) output—AI can parse it to spot issues.
3. The Schema Architect: Design a Database from Scratch
Prompt: "Design a PostgreSQL schema for [describe your project]. Include tables, primary keys, foreign keys, indexes, and constraints. Use appropriate data types (e.g., UUID, JSONB). Consider normalization and future scalability. Output as a set of CREATE TABLE statements."
Example: For a multi-tenant SaaS, the prompt yielded a clean schema with a tenants table and tenant_id on each child table, plus partial indexes for active records.
Customization: Mention if you prefer JSONB for flexible fields or normalized tables for strict integrity.
4. The Migration Maestro: Write a Safe Migration
Prompt: "Write a PostgreSQL migration script to [describe change, e.g., add a column, create a table]. Use a transaction block (BEGIN/COMMIT) and include safeguards: check for existing objects, handle errors, and provide a rollback script."
Example: I needed to add a status column to an orders table. The prompt generated a script with ALTER TABLE ... ADD COLUMN IF NOT EXISTS and a DO $$ block to update default values. It also produced a rollback ALTER TABLE ... DROP COLUMN.
Customization: Specify if you use a migration tool like Flyway or Alembic to match their format.
5. The Index Strategist: Choose the Right Indexes
Prompt: "Given this table schema and query patterns: [describe], recommend the most effective indexes. Consider B-tree, GIN, and partial indexes. Explain why each index helps and any trade-offs (write performance, storage)."
Example: A log table with frequent searches on user_id and created_at got a composite B-tree index, and a JSONB column with array queries got a GIN index.
Customization: Provide your actual EXPLAIN output or slow query log to ground the advice.
6. The Data Whisperer: Generate Complex Analytical Queries
Prompt: "Write a PostgreSQL query that [describe analytical task, e.g., calculate rolling averages, cohort analysis]. Use window functions and CTEs. Handle edge cases like NULLs and zero division. Include sample output."
Example: I needed a 30-day rolling average of user signups. The prompt produced a query with AVG(count) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), handling weeks with no signups.
Customization: Specify the exact time frame and aggregation level.
7. The Query Explainer: Decipher a Complex Query
Prompt: "Explain this PostgreSQL query step by step: [paste query]. Break down each CTE, join, and window function. Clarify what the final result set represents. Highlight potential performance issues."
Example: A legacy query with 5 CTEs was a mystery. The prompt dissected it, revealing a redundant join and a misplaced filter.
Customization: Ask for a simplified rewrite if the query is overly complex.
8. The Data Cleaning Pro: Write Queries to Clean Messy Data
Prompt: "Create a PostgreSQL script to clean [table] data. Handle duplicates, NULLs, inconsistent formats (e.g., dates, emails). Use CTEs to identify problematic rows and update or delete them safely. Include a dry-run SELECT before changes."
Example: Duplicate customer emails were causing issues. The prompt produced a query using ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) to flag duplicates, then a DELETE with a USING clause to keep the oldest.
Customization: Mention specific data quality rules you follow.
9. The Report Builder: Create a Monthly Sales Report
Prompt: "Generate a SQL query for a monthly sales report. Show total revenue, number of orders, and average order value per month for the last year. Include a comparison to the previous month (percentage change). Optimize for readability."
Example: The prompt produced a query with DATE_TRUNC('month', order_date) AS month, SUM(amount), and LAG to get previous month's value, plus a ROUND((current - previous) / previous * 100, 2) AS change.
Customization: Specify the date range and any filters (e.g., by product category).
10. The SQL Tutor: Learn and Understand Advanced Features
Prompt: "Explain the concept of [feature, e.g., recursive CTEs] in PostgreSQL with a practical example. Show a query that uses it to solve [problem]. Break down each part of the query."
Example: I struggled with recursive CTEs for a category tree. The prompt explained the anchor and recursive members, then provided a query to traverse a categories table with parent_id.
Customization: Ask for a simple and a complex example to solidify understanding.
11. The Backup and Recovery Planner: Write a Backup Strategy
Prompt: "Outline a PostgreSQL backup and recovery strategy for [size of DB, RPO/RTO]. Include commands for pg_dump, pg_basebackup, and WAL archiving. Discuss point-in-time recovery steps."
Example: I needed a strategy for a 200 GB database. The prompt suggested a mix of weekly pg_basebackup and continuous WAL archiving, with exact commands and a recovery scenario.
Customization: Specify your acceptable downtime and data loss.
12. The Security Auditor: Identify Vulnerabilities
Prompt: "Review this PostgreSQL database for security issues: [describe roles, privileges, and schema]. Suggest improvements: least privilege, password policies, SSL enforcement, and row-level security if needed."
Example: The prompt found that the app_user had SUPERUSER privileges—it recommended a dedicated role with only necessary grants.
Customization: Provide your current role structure.
13. The Performance Monitor: Write Queries to Check Health
Prompt: "Write a set of diagnostic queries to check PostgreSQL health: active connections, long-running queries, cache hit ratio, and table bloat. Include thresholds for alerts."
Example: I now run a script that flags queries running over 5 minutes using pg_stat_activity and checks pg_stat_user_tables for bloat.
Customization: Adapt thresholds to your workload.
14. The JSONB Explorer: Query Semi-Structured Data
Prompt: "Write a PostgreSQL query to extract data from a JSONB column. Filter, aggregate, and index JSONB fields. Explain the operators used (->, ->>, @>, ?)."
Example: For a metadata column storing event properties, the prompt generated a query using metadata ->> 'event_type' and a GIN index for @> containment checks.
Customization: Provide sample JSON to tailor the example.
15. The Data Model Validator: Check for Design Flaws
Prompt: "Analyze this PostgreSQL schema for common design flaws: missing primary keys, inappropriate data types, lack of indexes on foreign keys, or over-normalization. Suggest improvements."
Example: The prompt noticed that a status column was stored as TEXT but used a CHECK constraint; it recommended an ENUM or lookup table for better performance.
Customization: Ask for a report format.
Putting It All Together
These prompts aren't magic—they're structured conversations that leverage AI's vast knowledge of PostgreSQL. The key is to provide enough context: schema, query, or goal. Start with a specific problem, paste relevant details, and iterate. You'll find that AI becomes a reliable assistant, freeing you to focus on architecture and business logic.
My advice: Keep a library of your best prompts and refine them as you learn. And remember—AI is a tool, not a replacement for understanding. Verify every query with EXPLAIN and test on a staging environment. Happy querying!
Comments