50 AI Prompts for PostgreSQL and SQL Query Development

How to Use These PostgreSQL Prompts

Schema Design and Basic Queries (Prompts 1-14)

• Prompt 4: Write a PostgreSQL query that finds the top 10 users by total order value in the last 30 days. Join users, orders, and order_items tables. Filter orders to the last 30 days. Group by user, sum the order_items quantity * unit_price, and order by total descending. Include users with no recent orders as zero.
• Prompt 5: Create a recursive CTE query that fetches an organisation hierarchy. Given a departments table with id, name, and parent_id, write a recursive WITH query that starts from a root department and returns all descendants with their depth level and path from root to leaf.
• Prompt 6: Write a PostgreSQL query using window functions to calculate a 7-day rolling average of daily revenue. The source is an orders table with created_at and total_amount. Return each date, the daily total, and the 7-day rolling average using AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
• Prompt 7: Create a PostgreSQL full-text search query for a blog posts table. Add a tsvector column generated from the title and body fields (title weighted A, body weighted B), create a GIN index on the tsvector column, and write a search query using to_tsquery that ranks results by ts_rank and highlights matching terms with ts_headline.
• Prompt 8: Write a PostgreSQL query that efficiently paginates using keyset (cursor) pagination. Given a posts table ordered by created_at descending then id descending, write a query that accepts a cursor of (last_seen_created_at, last_seen_id) and returns the next page of 20 results without an OFFSET.
• Prompt 9: Create a PostgreSQL upsert using INSERT ... ON CONFLICT. Insert a new product or update the price and stock if a product with the same sku already exists. Return the full row whether it was inserted or updated using the RETURNING clause.
• Prompt 10: Write a PostgreSQL query that pivots rows into columns. Given a daily_metrics table with metric_name and metric_value columns, produce a result where each metric name becomes a column and each row is a date.
• Prompt 11: Create a PostgreSQL trigger that automatically updates the updated_at column on every UPDATE. Write the trigger function in PL/pgSQL and the trigger definition that fires BEFORE UPDATE on each row.
• Prompt 12: Write a PostgreSQL query using JSONB operators. Given a products table with a metadata JSONB column, query products where metadata contains a specific key, filter by a nested value using the ->> operator, and index the JSONB column with a GIN index for fast containment queries.
• Prompt 13: Create a PostgreSQL lateral join query. For each user, find their most recent 3 orders using LATERAL (SELECT ... FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3). Show why LATERAL is needed here instead of a regular subquery.
• Prompt 14: Write a PostgreSQL query using DISTINCT ON to find the latest order per customer. Show how DISTINCT ON (customer_id) with ORDER BY customer_id, created_at DESC returns exactly one row per customer — the most recent — more efficiently than a subquery with MAX.

Performance, Indexing, Partitioning, and Operations (Prompts 15-50)

• Prompt 18: Create a PostgreSQL Row Level Security (RLS) policy for a multi-tenant application. Enable RLS on the projects table, create a policy that allows SELECT only when organisation_id equals the value stored in the app.current_organisation session variable, and show the Node.js code that sets the session variable before running queries.
• Prompt 19: Write a PostgreSQL logical replication setup for zero-downtime migration. Show how to set up a publication on the source database, create a subscription on the target, let replication catch up, then switch the application connection string with zero data loss.
• Prompt 20: Create a PostgreSQL backup strategy using pg_dump and pg_basebackup. Show a daily pg_dump command for logical backup, a pg_basebackup command for a full cluster backup, the pg_restore command to restore a specific table from a dump, and a cron job that rotates backups and deletes those older than 30 days.
• Prompt 21: Write a PostgreSQL connection pooling setup using PgBouncer. Configure PgBouncer in transaction pooling mode, set pool_size to 20 connections, show the pgbouncer.ini configuration for a Node.js application, and explain why connection pooling is necessary for serverless or high-concurrency applications.
• Prompt 22: Create a PostgreSQL extension setup for UUID primary keys using gen_random_uuid(). Add the pgcrypto or uuid-ossp extension, define a table that uses UUID as the primary key with a default value, and show the trade-offs of UUID versus serial integer primary keys for distributed inserts and index fragmentation.
• Prompt 23: Write a PostgreSQL advisory lock pattern for a distributed application. Use pg_try_advisory_lock with a hash of a job ID to ensure only one application instance processes a specific job at a time, release the lock when the job completes, and handle the case where the lock is already held by returning without processing.
• Prompt 24: Create a Node.js PostgreSQL connection setup using node-postgres (pg). Configure a connection pool with max 10 connections, set a connection timeout and query timeout, add an error handler for idle client errors, use the pool.query method for simple queries, and use pool.connect for transactions that need to hold a client for multiple queries.
• Prompt 25: Write a Python PostgreSQL connection setup using asyncpg for async access. Create a connection pool on application startup, write an async query function that fetches rows as dict-like records, handle connection errors with retry logic, and close the pool on application shutdown.