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
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
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
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+.
SQL
LATERAL Join — Per-Row Subquery
PostgreSQL `LATERAL` lets a join's right-hand side reference the left-hand row — like a correlated subquery, but supplying multiple columns or rows. Perfect for "top-N per group".
SQL
UPSERT — PostgreSQL ON CONFLICT
`INSERT ... ON CONFLICT DO UPDATE` inserts a row, or updates the existing one if a unique-constraint collision happens. The atomic alternative to "SELECT, then INSERT or UPDATE".
SQL
ARRAY_AGG and JSON_AGG (PostgreSQL)
Roll up grouped rows into a PostgreSQL array or JSON array — often a faster replacement for the application doing the same "group children under parent" join.
SQL
Views and Materialized Views
A view is a named query — no data stored. A materialized view caches the result; refresh it on demand. Great for expensive joins or aggregations that don't need to be real-time.