The snippet library.
Small, practical examples. Search by task or narrow things down below.
Combine results while retaining duplicates
Postgres: UNION ALL does not spend work removing equal rows.
Postgres✓ Tested
Copy selected rows into another table
Postgres: INSERT SELECT performs a set-based copy with an explicit destination column list.
Postgres✓ Tested
Delete rows selected by a related table
Postgres: A correlated EXISTS limits the delete to explicitly listed records.
Postgres✓ Tested
Find rows in the first result but not the second
Postgres: EXCEPT calculates a distinct result-set difference.
Postgres✓ Tested
Find rows shared by two query results
Postgres: INTERSECT compares complete selected rows and removes duplicates.
Postgres✓ Tested
Increment a stored value in the database
Postgres: An in-database arithmetic update avoids a separate application read-modify-write step.
Postgres✓ Tested
Insert only keys absent from a destination
Postgres: NOT EXISTS supports a set-based missing-row insert in a single-writer example.
Postgres✓ Tested
Remove duplicate values while keeping the smallest key
Postgres: Identify retained keys in a grouped subquery before deleting other copies.
Postgres✓ Tested
Update rows selected by a related table
Postgres: Use EXISTS to limit updates based on an independent eligibility table.
Postgres✓ Tested
Update several classifications in one statement
Postgres: A CASE expression computes the new value for each row covered by the update predicate.
Postgres✓ Tested