SQL Query Recipes & Database Solutions
Everyday database solutions for PostgreSQL, MySQL, and ANSI SQL. Find duplicate rows, build zero-downtime concurrent indexes, master upserts (ON CONFLICT), window function running totals, and optimize slow queries.
How to Find and Delete Duplicate Rows in SQL
Identify rows with duplicate column values using GROUP BY and HAVING count(*) > 1, and safely remove duplicates using ROW_NUMBER() CTEs.
SELECT email, COUNT(*)DELETE vs TRUNCATE vs DROP in SQL: Differences Explained
Compare row-by-row logging (DELETE), fast table emptying with auto-increment reset (TRUNCATE), and complete table removal (DROP).
-- DELETE (Slow, logged, where clause):How to UPSERT in PostgreSQL Using ON CONFLICT DO UPDATE
Atomically insert a new row or update an existing row if a unique constraint or primary key conflict is encountered.
INSERT INTO users (id, name, login_count)How to Calculate a Running Total (Cumulative Sum) in SQL
Compute cumulative running totals over time or partitioned groups using SUM() OVER (ORDER BY ...).
How to Create an Index Concurrently in PostgreSQL (Zero Downtime)
Build a production database index on a table with millions of rows without locking out live INSERT, UPDATE, or DELETE operations.
CREATE INDEX CONCURRENTLY idx_users_emailHow to UPDATE a Table From Another Table in SQL
Update values in one table based on matching columns or aggregations in a secondary table.
-- PostgreSQL / SQLite syntax:How to Reset Auto Increment / Sequence in MySQL & PostgreSQL
Reset the next auto-generated primary key ID back to 1 or synchronize it with the maximum existing ID in the table.
How to Use EXPLAIN ANALYZE to Optimize Slow SQL Queries
Inspect the query execution plan, index scans vs sequential table scans, execution time, and buffer cache hits.
EXPLAIN (ANALYZE, BUFFERS, COSTS)SQL Joins Explained: INNER, LEFT, RIGHT, FULL OUTER & CROSS
A visual and practical guide to combining data from multiple relational tables with real-world examples.
-- INNER JOIN (Only matching rows):How to Backup and Restore a PostgreSQL Database with pg_dump & psql
Export compressed SQL dump archives and restore them cleanly to local or remote database instances.