#sql Clear
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.
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.
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 ...)`.