Tags
#php
#kotlin
#bash
#go
#sql
#rust
#typescript
#html
#java
#python
#files
#utils
#strings
#http
#concurrency
#async
#json
#arrays
#security
#types
#crypto
#database
#dates
#format
SQL
Generated / Computed Columns
A column whose value is derived from other columns and maintained automatically. PostgreSQL has STORED (persisted) and MySQL adds VIRTUAL (computed on read). Saves you from triggers for derived data.
@admin
SQL
UPDATE with JOIN
Update one table using values from another. Syntax varies by database — PostgreSQL uses `FROM`, MySQL puts the join in the `UPDATE` clause.
SQL
Window Function vs N+1 Query
Don't fetch a list, then loop in code to count each parent's children. Use a window function or a single GROUP BY — turn 1,001 queries into 1.
SQL
Indexes — B-tree, Partial, Composite
Indexes are the difference between a 50ms query and a 50s one. Composite indexes match queries that filter / sort on the columns in order; partial indexes skip irrelevant rows for big space savings.
SQL
Array Columns (PostgreSQL)
Native array columns avoid an extra join for "small list of things per row" patterns — tags, categories, allowed_roles. Combine with GIN index for fast `@>` / `&&` operators.
SQL
EXPLAIN / EXPLAIN ANALYZE
`EXPLAIN` shows the query plan; `EXPLAIN ANALYZE` actually runs it and shows real timings. The first tool when a query is slow — look for sequential scans on big tables.
SQL
DELETE with JOIN / USING
Delete rows from one table conditional on a related table. PostgreSQL uses `USING`; MySQL uses join syntax in the DELETE. Run as a SELECT first to verify what you're about to remove.
SQL
date_trunc — Bucket Times into Hours/Days/Weeks
`date_trunc('day', ts)` rounds a timestamp down to the start of the day. Use for time-series GROUP BYs — daily/weekly/hourly buckets without messy arithmetic.
SQL
Transactions and Isolation Levels
Wrap related changes in a transaction so they commit or roll back together. Isolation levels trade consistency for concurrency — pick the weakest one that meets your correctness needs.
SQL
Soft Delete with Partial Index
Set a `deleted_at` timestamp instead of physically removing rows — preserves history and lets you "undelete". Pair with a partial index for fast queries that skip the soft-deleted rows.
SQL
TRUNCATE vs DELETE
`DELETE` is logged per-row and respects triggers; `TRUNCATE` deallocates whole pages and resets the table to empty. `TRUNCATE` is much faster on large tables but has caveats (no WHERE, can't be rolled back in some DBs).
SQL
Conditional Aggregation (FILTER / CASE)
Count or sum subsets of rows in a single GROUP BY pass. PostgreSQL has the cleaner `FILTER` clause; everyone else uses `SUM(CASE WHEN ...)`.
SQL
Pattern Matching — LIKE / ILIKE / SIMILAR
`LIKE` uses `%` (any string) and `_` (single char). PostgreSQL `ILIKE` is case-insensitive. For real regex, reach for `SIMILAR TO` (POSIX) or `~` (PostgreSQL).
SQL
CASE WHEN — Conditional Values
Inline if/else inside a SELECT, ORDER BY, or aggregation. The Swiss-army knife for converting raw column values into labels or buckets.
SQL
MERGE — One Statement for INSERT + UPDATE + DELETE
`MERGE` (standard SQL, PostgreSQL 15+, SQL Server, Oracle) is a single statement that combines INSERT/UPDATE/DELETE driven by joining a source against a target. Heavy artillery for reconciliation jobs.
SQL
GROUP BY — Aggregation Basics
`GROUP BY` collapses rows into buckets; the SELECT list must be aggregates OR grouped columns. The most common reporting pattern in SQL.
SQL
RETURNING — Get Back What You Wrote
PostgreSQL `RETURNING *` returns the rows affected by an INSERT/UPDATE/DELETE — no separate SELECT round-trip needed. SQL Server has `OUTPUT`; MySQL added `RETURNING` in MariaDB and 8.0.31+.