Let's face it: writing SQL can sometimes feel like watching paint dry. You know the SELECT * FROM table drill by heart, but the moment you need to untangle a nested JSON blob or debug a query that's slower than a Monday morning, the enthusiasm fades. But what if you could offload that grind to an AI? Not for the trivial stuff, but for the heavy lifting—the complex joins, the performance tuning, the data storytelling. That's exactly what this collection of prompts is about. I've been using these in my own daily workflow for months, and they've transformed how I interact with PostgreSQL. They're not magic spells; they're structured ways to get an AI to act as a senior SQL analyst, a performance tuner, and a data viz consultant simultaneously.
Whether you're a data analyst who's been in the trenches for years or a product manager who occasionally needs to pull a report, these prompts are designed to save you hours. Each one comes with a real-world example, showing you the exact input and output, so you can adapt them to your own tables and schemas. No fluff, just working code and the reasoning behind it. Let's dive in.
1. The Schema Whisperer: From Raw DDL to Entity-Relationship Map
The Problem: You inherit a database with dozens of tables and no documentation. You need to understand the relationships fast.
The Prompt:
Act as a senior PostgreSQL DBA. I'm providing you with the DDL statements for my database. Generate a Markdown table listing all tables, their primary keys, foreign keys, and a brief description of the business entity each table represents. Also, identify any tables that appear to be missing foreign key constraints but logically should have them.
[Paste DDL here]
Why It Works: This prompt forces the AI to act as a documentation tool, not just a query generator. The explicit request for missing constraints often reveals design flaws you'd otherwise miss.
Example Use: I once ran this on a 15-table schema for an e-commerce platform. The AI instantly flagged that the order_items table had no foreign key to products, which was causing orphaned records. We fixed it before it became a data integrity nightmare.
2. The Query Builder: Turning Plain English into Complex SQL
The Problem: You know what you want, but translating it into a window function or a recursive CTE is painful.
The Prompt:
I need a PostgreSQL query that does the following: [describe your business question]. Use modern SQL features like window functions, CTEs, and proper indexing hints. Explain the logic step-by-step, and provide an example of the expected output based on sample data.
Why It Works: By asking for a step-by-step explanation, you get a query that's both correct and educational. It's like having a mentor look over your shoulder.
Example Use: My go-to is asking for a rolling 30-day average of sales per product. The AI generates a clean query using AVG() OVER (PARTITION BY product_id ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), saving me from writing a clunky self-join.
3. The Performance Doctor: Diagnosing Slow Queries
The Problem: A query that used to be instant now takes 10 seconds. You need to find the bottleneck.
The Prompt:
Here's a slow PostgreSQL query and its EXPLAIN ANALYZE output. Identify the most likely performance bottlenecks. Suggest specific indexes to create, query rewrites to try, or configuration changes (like work_mem or effective_cache_size) that could help. Be concrete and explain why each suggestion will help.
Query: [SQL]
EXPLAIN: [Output]
Why It Works: It combines the query with the execution plan, giving the AI the full context. The request for concrete suggestions prevents generic advice like "add indexes."
Example Use: I had a query that was doing a full table scan on a 10-million-row table. The AI noticed a WHERE clause on a lower(email) function and suggested a functional index CREATE INDEX ON users (lower(email)). The query went from 8 seconds to 50ms.
4. The Data Cleaner: Finding Anomalies and Duplicates
The Problem: Your data is messy. You need to find duplicates, nulls in critical columns, and out-of-range values.
The Prompt:
I have a table named 'users' with columns: id, email, created_at, last_login. Write a set of SQL queries to:
1. Find duplicate emails (show the count of each).
2. Find rows where created_at is in the future.
3. Find rows where last_login is older than 1 year.
4. Suggest a cleanup strategy for each anomaly.
Why It Works: It tackles a common data quality issue in one go, and the cleanup strategy ensures you don't just find problems but also resolve them.
Example Use: This is a lifesaver for marketing teams. I ran it on a CRM export and found 2,000 duplicate records that were skewing campaign metrics. A quick DELETE USING with a self-join fixed it.
5. The JSON Explorer: Navigating PostgreSQL's JSONB
The Problem: You have a jsonb column with lots of nested data, and you need to extract and aggregate it.
The Prompt:
I have a table 'events' with a 'payload' jsonb column. The structure looks like: [sample JSON]. Write a query that extracts the customer's name and the total amount of an order, and groups by the customer's country. Use jsonb operators like -> and ->>. Explain how the query handles missing keys.
Why It Works: JSONB is powerful but tricky. This prompt gets you a working query and an explanation of the operators, which helps you learn the syntax for future use.
Example Use: I used this to build a real-time dashboard on user behavior. The prompt generated a query using payload->'customer'->>'name' and a LATERAL JOIN to flatten an array of items, which I would have spent an hour on.
6. The Schema Migrator: Safe ALTER TABLE Strategies
The Problem: You need to add a column or change a data type without locking the table for hours.
The Prompt:
I need to add a 'phone_number' column to a large 'customers' table (100M rows) in PostgreSQL. What's the safest way to do this without downtime? Consider using the 'ADD COLUMN IF NOT EXISTS' syntax, but also discuss the trade-offs of using 'DEFAULT' and how to backfill data efficiently. Provide example migration scripts.
Why It Works: It forces the AI to think about operational concerns, not just syntax. The mention of downtime and backfilling is key.
Example Use: I followed the advice to add the column without a default, then ran a batch UPDATE in chunks. The table stayed online, and the migration took 20 minutes instead of an hour of downtime.
7. The Window Function Wizard: Ranking and Running Totals
The Problem: You need to rank products by sales, calculate a running total, or find the top-N per group.
The Prompt:
Using the 'orders' table (id, customer_id, total, order_date), write a query that:
1. Ranks customers by total spending (using RANK() and DENSE_RANK()).
2. Shows a running total of sales per day.
3. For each customer, shows their top 3 most recent orders.
Explain the PARTITION BY and ORDER BY clauses.
Why It Works: It covers three classic window function patterns in one shot, with an explanation that solidifies the concept.
Example Use: This is my default for any "top N" report. The prompt generated a query with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) that I use weekly for client dashboards.
8. The Recursive CTE Master: Handling Hierarchies
The Problem: You have a table with a self-referencing foreign key (like employees with a manager_id) and need to traverse the hierarchy.
The Prompt:
I have an 'employees' table (id, name, manager_id). Write a recursive CTE to show the full org chart, including the level of each employee and the path from the CEO. Explain the anchor and recursive members. Also, provide a way to find all direct and indirect reports of a specific manager.
Why It Works: Recursive CTEs are notoriously confusing. This prompt gets a working query and a clear explanation, turning a complex topic into a reference you can reuse.
Example Use: I used this to build an org chart for a client's HR system. The AI's query with UNION ALL and a LEVEL counter was perfect, and the explanation helped me explain it to the client's IT team.
9. The Date/Time Ninja: Handling Time Zones and Intervals
The Problem: Your data is in UTC, but your reports need to be in 'America/New_York'. Or you need to generate a series of dates for a calendar.
The Prompt:
I have a 'sessions' table with a 'started_at' timestamptz column. Write a query that:
1. Converts 'started_at' to 'America/New_York' timezone.
2. Extracts the week number and weekday name.
3. Generates a series of all dates in the last 30 days.
4. Calculates the average session length in minutes, handling cases where sessions span midnight.
Why It Works: Time zone and date handling are a constant source of bugs. This prompt ensures you get the correct AT TIME ZONE syntax and generate_series usage.
Example Use: I ran this to build a report for a global user base. The AI's query using started_at AT TIME ZONE 'America/New_York' gave us the right local times, and generate_series helped fill in days with zero sessions, which was crucial for the chart.
10. The Data Storyteller: From SQL to Chart-Ready Output
The Problem: You've written the query, but now you need to present the results in a way that tells a story.
The Prompt:
Given the following SQL query result, suggest the best type of chart for each column and explain what story it tells. Also, provide a narrative summary of the key insights from the data.
[Paste query and sample results]
Why It Works: It bridges the gap between data and business decision-making. The AI acts as a data analyst, not just a query runner.
Example Use: I pasted a monthly sales query result, and the AI suggested a line chart for trends, a bar chart for category comparisons, and wrote a two-sentence summary that I used directly in a board meeting.
11. The Debugger: Fixing Broken Queries with Error Messages
The Problem: You have a query that throws an error, and you can't figure out why.
The Prompt:
This PostgreSQL query is throwing an error: [error message]. Here's the query: [SQL]. Explain the cause of the error and provide a corrected version. Also, suggest best practices to avoid this class of errors in the future.
Why It Works: It turns a frustrating moment into a learning opportunity. The AI explains the root cause, not just the fix.
Example Use: I once got a GROUP BY error because I selected columns not in the group by clause. The AI explained the SQL standard rule and showed me how to use DISTINCT ON instead, which was a more elegant solution.
12. The Index Architect: Designing the Optimal Indexing Strategy
The Problem: You're not sure which indexes to create, and you're worried about over-indexing.
The Prompt:
Based on my database schema and the following common query patterns (list them), recommend an indexing strategy. Include B-tree, GIN, and partial indexes where appropriate. Provide the exact CREATE INDEX statements and explain how each index will be used. Also, identify any indexes that are redundant or harmful.
Why It Works: It's a proactive approach to database design. The AI considers the actual workload, not just the schema, and gives you a plan you can implement.
Example Use: I gave the AI a list of the ten most frequent queries. It suggested a partial index on status WHERE status = 'active' that was much smaller than a full index, speeding up our main dashboard query by 10x.
13. The Data Dictionary Maker: Documenting Your Database
The Problem: You need to document your database for new team members, but it's tedious.
The Prompt:
Create a data dictionary for my PostgreSQL database. List every table and its columns, data types, constraints, and a human-readable description of what each column stores. Also, include a section on relationships between tables.
[Paste schema]
Why It Works: It automates a task that nobody wants to do but everyone needs. The output is a ready-to-publish document.
Example Use: I generated a 30-page data dictionary for a client in under a minute. It saved me two days of manual work, and the client was impressed by the professionalism.
14. The Security Auditor: Finding SQL Injection Vulnerabilities
The Problem: You suspect your code might be vulnerable to SQL injection, and you need to find and fix it.
The Prompt:
Here's a snippet of Python code that queries a PostgreSQL database. Identify any SQL injection vulnerabilities and show me how to fix them using parameterized queries. Explain the risks in detail.
[Paste code]
Why It Works: Security is non-negotiable. This prompt gets you both the detection and the remediation, with a clear explanation of the risks.
Example Use: I ran this on a legacy Python script and found a classic f-string SQL query. The AI showed me how to rewrite it with psycopg2.sql parameters, which I then deployed to production.
15. The Backup Strategist: Planning for Disaster Recovery
The Problem: You need a backup strategy, but you're not sure what options PostgreSQL offers.
The Prompt:
I'm running a PostgreSQL 15 database on a Linux server. Outline a comprehensive backup and recovery strategy. Compare `pg_dump`, `pg_basebackup`, and continuous archiving with WAL. Recommend the best approach for a database with 500GB of data and a recovery point objective (RPO) of 15 minutes. Provide example cron scripts.
Why It Works: It covers a critical operational topic, and the requirement for scripts makes it immediately actionable.
Example Use: I followed the AI's advice to set up pg_basebackup with WAL archiving. When we had a disk failure, we restored the database to within 10 minutes of the crash, which was well within our RPO.
16. The Query Optimizer: Rewriting for Speed
The Problem: You have a query that works but is slower than it should be, and you want to rewrite it.
The Prompt:
Here's a query that takes 5 seconds on a table with 1M rows. Rewrite it to run in under 100ms. Use techniques like pre-aggregation, avoiding functions on indexed columns, or using EXISTS instead of IN. Show the original and the rewritten version, and explain the performance gain.
Why It Works: It's a practical exercise in query tuning. The AI gives you a before-and-after comparison, which is great for learning.
Example Use: I had a query with a WHERE EXTRACT(YEAR FROM date) = 2023 clause. The AI rewrote it to WHERE date >= '2023-01-01' AND date < '2024-01-01', which allowed the index to be used. The speedup was dramatic, and I learned a lesson about sargability.
17. The Data Vizard: Creating a View for Reporting
The Problem: You need to create a view that simplifies a complex query for your BI tool.
The Prompt:
Create a PostgreSQL view named 'monthly_sales_summary' that shows the total sales per product per month, along with a running total and a year-over-year comparison. Use a CTE to first aggregate the data, then join to a calendar table. Provide the full SQL and an example of how to query the view.
Why It Works: It's a complete solution from creation to usage, and the CTE approach is a best practice for readability.
Example Use: I created this view for a Tableau dashboard. The AI's use of a calendar table to fill in missing months was brilliant, and the YoY comparison was a hit with the sales team.
18. The Data Extractor: Exporting to CSV with Formatting
The Problem: You need to export data to CSV, but you need specific formatting (like date formats and empty string handling).
The Prompt:
Write a PostgreSQL command to export the result of this query to a CSV file with headers. Use the `COPY` command, but also show how to do it with `\copy` in psql. Handle the case where fields contain commas and quotes, and set the date format to 'YYYY-MM-DD'. Include an example of how to import the CSV back into a table.
Why It Works: It covers the full export/import cycle, which is a common but often tricky task. The explicit handling of special characters is crucial.
Example Use: I used the \copy command to export a large dataset for a client. The AI's suggestion to use CSV HEADER QUOTE '"' ensured that our data with embedded commas was handled correctly.
These prompts have become my Swiss Army knife for PostgreSQL. They don't just give you an answer; they teach you the "why" behind it, making you a better analyst with each use. The next time you're stuck on a complex query or a performance issue, remember that you have a powerful assistant at your fingertips. Copy these prompts, adapt them to your data, and watch your productivity soar. And if you find yourself automating more of your workflow, you might find that an AI-powered learning platform like Asibiont can help you level up your skills even further. Happy querying!
Comments