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
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.
@admin
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.
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
ROW_NUMBER OVER PARTITION
Number rows within a partition — the most common "give me the Nth thing per group" pattern. Pair with a WHERE rn = 1 in an outer query to keep just the top row per group.
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
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
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
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
EXTRACT — Pull Parts Out of a Timestamp
`EXTRACT(field FROM timestamp)` pulls year, month, hour, day-of-week, week, epoch — whatever you need to group, filter, or render. Standard SQL across every database.
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
JSONB Columns (PostgreSQL)
`jsonb` stores binary-optimized JSON. Index sub-fields with GIN for fast containment queries. Most-used operators: `->` (get field), `->>` (get as text), `@>` (contains), `?` (key exists).
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
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
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
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
FULL OUTER JOIN / CROSS JOIN
`FULL OUTER` keeps unmatched rows from BOTH sides. `CROSS JOIN` is the Cartesian product — every row of A paired with every row of B. Rare but useful for date-spine and matrix generation.
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.