Engine archive
Every guide here is marked as supported for PostgreSQL, with examples you can follow directly.
Reference values from preceding or following rows within an ordered partition without writing a self-join.
Extend a table's structure by adding a new column with `ALTER TABLE`.
Calculate a future or past date by adding or subtracting an interval from an existing date.
Use `CASE WHEN` inside `SUM` or `COUNT` to produce multiple metrics from a single pass over grouped data.
Collect values from multiple rows into a single JSON array, ordered and grouped by other columns.
Use `EXISTS` when you only need to test whether a related row exists, not return one output row per match.
Construct a JSON object literal from individual column values within a query, returning structured data without a separate serialization step.
Accumulate a column's values row by row using `SUM` as a window function.
Compute the arithmetic mean of numeric values across matching rows.
Use `SUM(...) OVER ()` to compare each row's value to the total without collapsing the result set.
Define several named subqueries in one `WITH` block, each building on the previous, before the final `SELECT`.
Convert string values to all uppercase or all lowercase using `UPPER` and `LOWER`.
Use `IS NULL` and `IS NOT NULL` to filter rows based on whether a column has a value.
`UNION` removes duplicates, while `UNION ALL` keeps them. Picking the wrong one silently changes row counts.
Return all rows from both tables, filling NULLs on either side when there is no matching row.
Merge result sets from two `SELECT` statements into one, removing duplicate rows by default.
Append result sets together without removing duplicates.
Aggregate multiple string values from grouped rows into a single delimited string.
Use `ROWS BETWEEN` in a window function to aggregate only the rows immediately surrounding each row.
Strip the time portion from a timestamp so only the calendar date remains.
Change a value from one SQL type to another using an explicit cast.
Aggregate a result set into a single row that reports how many rows matched.
Count only the distinct values in a column, ignoring duplicates.
Save a `SELECT` statement as a named view so it can be queried like a table.
Define a new table with column names and data types using `CREATE TABLE`.
Permanently remove a table and all its data with `DROP TABLE`.
Remove matching rows with `DELETE`, then query the remaining table contents.
Remove rows from one table when a matching row exists in another table using a subquery.
Return the portion of a string that matches a regular expression pattern, useful for parsing structured text without fixed delimiters.
Return only part of a text value by position and length.
Return the year component from a date or timestamp value.
Return all rows from the right table and matching rows from the left, filling NULL where no match exists.
Return all rows from the left table and matching rows from the right, filling NULL where no match exists.
Use `HAVING` to filter aggregate results after `GROUP BY`, the same way `WHERE` filters individual rows before grouping.
Put right-table filters in the `ON` clause when you want to keep unmatched left-side rows.
Match string columns against a wildcard pattern using `LIKE`.
Use `IN` to match a column against a list of values in a single `WHERE` clause.
Return only rows that match a condition, with explicit ordering for stable output.
Use `BETWEEN` to match rows where a column falls within an inclusive lower and upper bound.
Group rows by the candidate key and keep only values that appear more than once.
Return only the rows that appear in both result sets using `INTERSECT`.
Return rows from the first query that do not appear in the second query using `EXCEPT`.
Use a `LEFT JOIN` and filter for `NULL` on the joined table to find missing relationships.
Use `MIN` and `MAX` to find the lowest and highest values in a column.
Convert a numeric value into a formatted string with a fixed number of decimal places, optionally including thousands separators.
Produce every possible pairing of rows from two tables using `CROSS JOIN`.
Return the current date and time using `CURRENT_TIMESTAMP` or engine-specific functions.
Return the number of characters in a string using `LENGTH` or `LEN`.
Create stable run groups without a primary key by subtracting two `ROW_NUMBER()` calculations over the same ordered events.
Aggregate rows by category with `GROUP BY` and count how many rows fall into each group.
Bucket dates into month starts, then aggregate counts or totals per month.
Use more than one column in `GROUP BY` when each combination defines a separate aggregate bucket.
Use `BEGIN`, `COMMIT`, and `ROLLBACK` to execute multiple statements as an atomic unit.
Add new data to a table, then verify the inserted row with a deterministic query.
Add several rows in one statement using a multi-value `INSERT`.
Atomically insert a row if it does not exist or update it if it does, using the database's native conflict-resolution syntax.
Reference the same table twice using aliases to compare or relate rows within the same dataset.
Combine multiple text values into one string in the query result.
Combine rows from two tables when matching keys exist in both tables.
Return only part of a result set using the row-limiting syntax each engine supports.
Inspect a table definition to see its column names and data types.
Inspect the current database or schema to see which tables exist.
Use `WITH` to define a named temporary result set that can be referenced in the main query, improving readability over inline subqueries.
Embed a `SELECT` inside another query to filter, compute, or supply values that depend on aggregated or derived data.
Assign sequential integers to rows within a partition without collapsing the result set the way `GROUP BY` does.
Skip earlier rows and return the next slice of a sorted result set.
Convert a text string in a known format into a native DATE value using engine-specific parsing functions.
Assign rank numbers to rows within a partition, controlling whether tied ranks leave gaps in the sequence.
Extract a scalar value from a JSON column using the JSON function or operator each engine supports.
Use `LATERAL` (or `CROSS APPLY` on SQL Server) to let a subquery reference columns from the preceding table in the `FROM` clause.
Use `AS` to give result columns clearer labels without changing the underlying table schema.
Return the first non-null value from a list of expressions.
Substitute all occurrences of a substring with a new value using `REPLACE`.
Evaluate conditions row-by-row inside a `SELECT` to produce computed columns based on branching logic.
Use `DISTINCT` to remove duplicate rows from a result set.
Use `NULLIF` to convert a specific value to `NULL`, most commonly to prevent division-by-zero errors.
Truncate a timestamp to the start of a month, day, or hour using engine-specific functions.
Return every row from a table with an explicit sort order for deterministic output.
Use `ROW_NUMBER()` to rank rows within each group and keep only the highest-ranked row.
Use more than one sort key so ties are broken deterministically.
Control the order of result rows explicitly instead of relying on storage order.
Create an index on one or more columns to make lookups and joins faster.
Expand a comma-separated or delimited string into one row per element, turning a single cell into a proper set of rows.
Use a correlated subquery inside `EXISTS` to include rows only when related rows are found in another table.
Add numeric values across matching rows and return one aggregate total.
Use `WITH RECURSIVE` to repeatedly join a query to its own results, enabling traversal of tree or graph structures.
Remove leading and trailing whitespace from string values with `TRIM`.
`COUNT(column)` skips `NULL` values, while `COUNT(*)` counts every row that made it into the result set.
Modify existing rows with `UPDATE`, then verify the changed data with a stable query.
Use a join inside an `UPDATE` statement to copy or derive values from a related table.
A row limit without an explicit sort can return different rows over time or across engines.
Filter `NULL` values out of the right side before using `NOT IN`, or the predicate can exclude everything.
`WHERE` filters individual rows before grouping. `HAVING` filters grouped results after aggregates are computed.