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
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.
@admin
Go
sql.NullString — Nullable DB Columns
Plain `string` can't represent SQL NULL. Use `sql.NullString` (and its siblings `NullInt64`, `NullBool`, etc.) for columns where NULL is a real value distinct from empty.
SQL
BETWEEN — Range Filtering
`BETWEEN a AND b` is inclusive on both ends. Cleaner than `col >= a AND col <= b`. For dates, watch for end-of-day vs midnight boundaries.
Go
database/sql — Transaction Helper
Wrap your transaction in a function that commits on success and rolls back on error or panic. Drop-in: pass any `func(*sql.Tx) error` and forget about manual cleanup.
SQL
SELECT DISTINCT and Counting Uniques
`DISTINCT` removes duplicate rows. Combine with `COUNT(DISTINCT col)` to count uniques in aggregations — different from `COUNT(*)`.
SQL
Recursive CTE — Date Spine Generator
Generate a row per day (or month, year, hour) without a calendar table. Useful for filling gaps when a date has no events but you still want it in your output.
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
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
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
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
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.