DECISION STATS

SQL string functions are essential for cleaning, transforming, parsing, and searching textual data inside relational databases. Functions such as LENGTH, CHAR_LENGTH, UPPER, LOWER, and INITCAP handle measurement and case transformation, while OCTET_LENGTH measures bytes rather than characters and becomes important with multibyte data. The distinction matters because character length and byte length are not always the same. SQL also provides functions such as REVERSE, REPEAT, ASCII, and CHR for specialised text manipulation. For reliable comparisons, the presentation recommends storing the original value while using a folded or normalised representation for comparison rather than permanently destroying the source text.

Parsing text becomes straightforward when SUBSTRING, LEFT, RIGHT, POSITION, and SPLIT_PART are used according to the structure of the data. SQL string positions are generally 1-based, so forgetting this can introduce silent off-by-one errors. SUBSTRING can extract a fixed region or work together with POSITION to locate delimiters dynamically, while SPLIT_PART is often clearer when processing consistently delimited values such as order codes or email addresses. Cleaning functions then prepare imported data for reliable comparison: TRIM, REPLACE, and TRANSLATE remove unwanted characters, LPAD and RPAD create fixed-width values, and REGEXP_REPLACE handles more complex transformations such as retaining only digits in phone numbers or collapsing repeated whitespace.
Concatenation requires particular care around NULL values. The || operator propagates NULL, meaning that a single missing component can make an entire concatenated result NULL. CONCAT treats NULL values as empty strings, while CONCAT_WS is especially useful for addresses and other multi-part values because it inserts the separator only between non-NULL components. COALESCE provides another explicit way to supply fallback values. For searching, the presentation recommends using the weakest mechanism that satisfies the requirement: equality can use a normal B-tree index, prefix searches such as LIKE 'Adi%' can use an index, while a leading wildcard such as LIKE '%adi%' generally prevents a normal B-tree range scan. For PostgreSQL workloads, trigram indexes can make contains searches index-assisted, while full-text search with to_tsvector and to_tsquery is more appropriate for natural-language document search.
Regular expressions provide more expressive validation, extraction, and replacement than LIKE, but they are generally CPU-bound and should not be used when a simpler indexed predicate is sufficient. The deck also demonstrates how delimited text can be expanded into rows using STRING_TO_ARRAY and UNNEST, after which the resulting values can be trimmed, analysed, joined, or aggregated with STRING_AGG. Although this technique is useful for cleaning denormalised imports, repeatedly splitting a comma-separated column is a sign that the data may be better represented in a normalised child table. Similarly, STRING_AGG can rebuild ordered lists across rows, with ORDER BY placed inside the aggregate and DISTINCT used when repeated values need to be removed.
Performance and correctness depend heavily on keeping text predicates indexable and understanding database-specific behaviour. Wrapping an indexed column in functions such as UPPER() or LOWER() can prevent a plain index from being used unless a corresponding expression index exists; PostgreSQL expression indexes and trigram indexes provide practical solutions. Collation also affects case comparison, sorting, and other text semantics, so assumptions about whether values such as Aditi and aditi are equal should never be made without considering the database and column collation. Common pitfalls include NULL propagation in concatenation, treating character positions as zero-based, leaving wildcard characters unescaped in user input, unexpected CHAR padding, and storing comma-separated lists instead of normalised relationships. A robust text-processing workflow is therefore to normalise first, validate without immediately deleting bad records, deduplicate using a normalised key, and preserve the original data for review.

https://docs.google.com/presentation/d/e/2PACX-1vQrAZ4xNLK3rfXqkTDD0Wo8vT1V8Uosw3Dz6b_V6M0RD9lGjyMTwqM0-AuyfHEhpQ/pub?start=true&loop=true&delayms=10000

Stored procedures, functions, and triggers allow application logic to execute directly inside the database, but each object has a distinct role. A function returns a value or table and can be called from SELECT, WHERE, or joins, making it suitable for reusable computations and parameterised reporting. A procedure is invoked with CALL and is designed for multi-step maintenance or batch operations where transaction control such as COMMIT and ROLLBACK is required. Triggers are different again: they execute automatically in response to database events and are particularly useful for integrity enforcement, audit trails, and controlled row-level transformations. Choosing the correct object prevents server-side code from becoming unnecessarily complex or difficult to maintain.

SQL functions can be implemented as simple SQL expressions or with procedural languages such as PL/pgSQL when variables, branching, loops, or exception handling are required. A scalar function accepts parameters and returns one value, while a table-returning function can behave much like a parameterised view that can be joined and composed with other queries. Function volatility is also important because it communicates assumptions about how results behave: IMMUTABLE indicates that the same inputs always produce the same result without table access, STABLE allows results to remain consistent within a statement while reading database state, and VOLATILE permits results to change between calls. Declaring volatility accurately gives the query planner more information and can allow immutable functions to participate in expression indexes.
PL/pgSQL adds procedural control flow through variables, IF statements, loops, records, and exception blocks. However, the presentation strongly emphasises a set-based-first approach: a loop that performs an operation row by row is usually far slower than a single SQL statement that performs the same transformation across the entire dataset. Exception handling should likewise be deliberate. RAISE EXCEPTION can abort an operation with a clear message, while named conditions such as unique_violation can be caught when recovery is genuinely required. Broadly swallowing errors with a blanket WHEN OTHERS THEN NULL is dangerous because it can hide failures and leave data in an unexpected state.
Procedures become particularly useful for long-running maintenance tasks because they can commit work between batches. The deck demonstrates chunked archival using batches of 10,000 rows, FOR UPDATE SKIP LOCKED, and GET DIAGNOSTICS to monitor affected rows. Committing between chunks prevents a maintenance job from holding one enormous transaction and helps keep locks and write-ahead logging growth manageable. Triggers provide another form of server-side automation: BEFORE row triggers can modify or validate NEW before a row is stored, while AFTER row triggers can record what actually happened, making them well suited to audit trails. The presentation’s audit example captures inserts, updates, and deletes using TG_OP, CURRENT_USER, and JSONB snapshots of the old and new rows.
The most important lesson is not simply how to write server-side SQL, but knowing when it belongs in the database. Logic that must never be bypassed, such as integrity rules and audit trails, is a strong database-side candidate, as are set-based transformations and bulk maintenance close to the data. Frequently changing business rules, workflows involving external services, retry queues, and complex application behaviour are generally better kept in the application layer. Performance and observability also matter: row-level triggers execute once per affected row, while set-based statements can process large datasets far more efficiently. Production database code should therefore be version-controlled, tested with assertion queries, deployed through repeatable migrations, instrumented with tools such as EXPLAIN ANALYZE, and kept as set-based as possible.

https://docs.google.com/presentation/d/e/2PACX-1vSrIlAlmIMXkYz_NYzQKrXIX_nx_zlz3XBl2G-Q7FL7Yy1h-yyYbcsraOCg6a9CBQ/pub?start=true&loop=true&delayms=10000

SQL set operations provide a way to combine the results of multiple queries vertically, while conditional expressions allow SQL to express branching logic directly inside a query. UNION combines result sets and removes duplicates, whereas UNION ALL simply appends the rows and is generally the better default when duplicate elimination is unnecessary. INTERSECT returns rows present in both result sets, while EXCEPT returns rows present in the first result but absent from the second; Oracle uses MINUS for the latter operation. Set operations require the same number of columns with compatible types, and columns are matched by position rather than name. The first SELECT determines the output column names, while a final ORDER BY applies to the combined result.
A common source of SQL errors is confusing set operations with joins. Set operations stack rows with the same structure, whereas joins combine related tables horizontally by adding attributes and can change row counts through fan-out. CASE addresses a different problem: it returns a value based on conditions and can therefore be used in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, and aggregate expressions. SQL supports both searched CASE, which evaluates arbitrary Boolean conditions from top to bottom, and simple CASE, which compares one expression against multiple values. The first matching branch wins, so condition ordering matters. If ELSE is omitted, unmatched rows produce NULL, and all branches must return compatible types.
SQL’s handling of NULL is based on three-valued logic: a condition can evaluate to TRUE, FALSE, or UNKNOWN. Comparisons involving NULL normally produce UNKNOWN, which explains why NULL = NULL is not true and why = NULL should be replaced with IS NULL. This behaviour becomes especially important with NOT IN, because a NULL in the comparison list can make the predicate evaluate to UNKNOWN and prevent rows from qualifying. SQL provides several tools for controlling this behaviour. COALESCE returns the first non-NULL argument, NULLIF converts a specified value into NULL, and IS DISTINCT FROM provides NULL-safe equality semantics. Together, these functions support fallback values, data cleaning, safe division, outer-join reporting, and comparisons involving nullable columns.
These features also enable several practical SQL patterns without requiring procedural code. Conditional aggregation with SUM(CASE...) can create portable static pivots, transforming categories such as quarters into separate columns. CASE can implement custom business-priority sorting, create age or revenue buckets, perform conditional updates, and construct optional filters. For data reconciliation, running EXCEPT in both directions reveals rows missing from either system; the two difference sets can then be combined and paired with a FULL OUTER JOIN to produce a labelled report showing missing, extra, or differing records. The presentation also highlights an important performance consideration: UNION and EXCEPT require duplicate elimination, typically through sorting or hashing, while UNION ALL performs a straightforward append. For large datasets, an indexed NOT EXISTS anti-join can sometimes be a more efficient alternative to EXCEPT.
The key to reliable SQL set and conditional logic is understanding exactly how rows, values, and NULL states behave. Use UNION ALL when duplicate removal is not required, explicitly parenthesise mixed set-operation chains because INTERSECT has higher precedence than UNION and EXCEPT, order CASE conditions from narrow to broad, and provide an ELSE when an unmatched result should not become NULL. For safe ratios, the presentation recommends the idiom COALESCE(a / NULLIF(b, 0), 0), which prevents division by zero while supplying a fallback value. Finally, remember that COUNT(column) ignores NULL values whereas COUNT(*) counts every row. These principles make set operations predictable, conditional logic expressive, and NULL-heavy SQL substantially easier to reason about.

https://docs.google.com/presentation/d/e/2PACX-1vSYt01Y1aizH5L8l-0glWFI2y-7rZZ1n-g4-gKlBRhF7Ys-shiXvSs4IphdhHj3Gw/pub?start=true&loop=true&delayms=10000

Recursive queries extend SQL beyond ordinary set-based operations by allowing a query to repeatedly process the results produced by its previous iteration. The core mechanism is the recursive CTE, which consists of an anchor query that runs once to seed the working table and a recursive term that joins the previous iteration back to the base table. The process continues until an iteration produces no new rows. This makes recursive CTEs particularly useful for hierarchical structures such as employee reporting lines, category trees, organizational charts, and dependency relationships. A depth limit should be treated as a practical safety mechanism because a cycle in the underlying data can otherwise cause runaway recursion and excessive resource consumption.

Tree traversal is one of the most common applications of recursive SQL. To find descendants, the recursive join moves from a parent node to its children; to find ancestors, the direction is reversed. During traversal, the query can carry additional state such as the current depth and an accumulated path. A path array is particularly useful because it can provide a deterministic depth-first sort order while also acting as a cycle guard by preventing a node from being revisited. Recursive queries can also identify leaf nodes by checking for the absence of child rows. The same recursive pattern applies to bills of material, where each level multiplies the quantity required and the final results are aggregated by component to determine total material requirements across multiple branches.
Recursive CTEs can also operate on graph-shaped data rather than strict trees. When relationships are stored as an edge table, recursion can accumulate a visited path and a running cost or hop count to explore reachability and candidate routes. Cycle protection is essential because graphs can contain arbitrary loops, while a hop limit prevents the search space from growing without bound. The presentation recommends three main defences: explicitly tracking visited nodes, imposing a maximum depth or hop count, and using the SQL CYCLE clause where supported. These techniques allow SQL to handle modest graph problems such as reachability and path listing, although very large graph workloads may be better suited to specialized graph engines.
The way a hierarchy is stored has a major impact on query and write performance. An adjacency list stores a parent_id and is simple to maintain, with recursive CTEs performing traversal when needed. Materialised path stores the ancestry as a string or array, enabling index-friendly prefix or containment queries but making subtree moves more expensive. A closure table precomputes every ancestor-descendant relationship, turning many hierarchy reads into a straightforward indexed join at the cost of additional rows and more maintenance during updates. Nested sets provide very fast range-based reads but make inserts and structural changes expensive. For many applications, an adjacency list is the sensible starting point; a closure table or materialised path becomes attractive when hierarchy reads dominate the workload.
Performance depends heavily on the recursive join and the amount of data carried through each iteration. An index on the recursion column, such as parent_id in a hierarchy table, is essential because the recursive join executes repeatedly for each level. Filtering should be pushed into the anchor where possible, unnecessary columns should not be carried through the working table, and recursion should have an appropriate depth or hop limit. If the same hierarchy is queried extremely frequently, precomputing relationships in a closure table can remove recursion from the hot path. The broader principle is to match the storage model to the read/write workload while treating path tracking, cycle protection, depth limits, and appropriate indexing as core parts of production recursive SQL rather than optional additions.

https://docs.google.com/presentation/d/e/2PACX-1vRw94aHXskbtSD6klffQHulD2ZQP1B-kIMvcK2vbnyMDPH4uOLlVJaU-HPD3wEsNg/pub?start=true&loop=true&delayms=10000

SQL numeric functions are fundamental to producing accurate calculations, especially when working with money, percentages, measurements, and analytical data. The first decision is choosing the correct numeric type: exact integers such as SMALLINT, INT, and BIGINT are appropriate for counts, identifiers, and quantities; NUMERIC or DECIMAL should be used when decimal values must be stored exactly, particularly for financial data; and REAL, FLOAT, or DOUBLE are approximate binary types better suited to measurements, statistics, and machine-learning features. Decimal fractions such as 0.1 generally cannot be represented exactly in binary floating point, which can produce drift in calculations and unreliable equality comparisons. For financial applications, the presentation recommends exact NUMERIC values or integer minor units such as paise or cents rather than floating-point storage.
Rounding operations must also be chosen deliberately because ROUND, CEIL, FLOOR, and TRUNC perform different operations. ROUND returns the nearest value at a specified decimal scale, while CEIL and FLOOR move toward positive and negative infinity respectively. TRUNC, in contrast, removes digits toward zero, making it different from FLOOR for negative values. The deck also highlights that rounding behaviour can depend on both the database engine and numeric type, including differences between half-up and half-even behaviour. Integer division is another common source of silent errors: dividing two integers can discard the fractional component, so percentage calculations should explicitly force numeric division and protect the denominator with NULLIF. Functions such as MOD, POWER, SQRT, ABS, SIGN, GREATEST, LEAST, and WIDTH_BUCKET extend SQL’s arithmetic capabilities for reporting, bucketing, growth calculations, and statistical analysis.
Explicit conversion with CAST is preferable to relying on implicit type conversion because implicit casts can introduce both correctness and performance problems. The presentation shows that CAST(x AS type) is the ANSI form, while PostgreSQL also supports the x::type shorthand. Dirty text data should be validated before conversion, or TRY_CAST/TRY_CONVERT can be used where supported to turn failed conversions into NULL rather than aborting the query. A particularly important performance issue occurs when a cast is applied to an indexed column: a predicate such as amount::int = 100 may prevent a normal index from being used because the database must evaluate an expression on the column. Casting the parameter to the column’s native type is generally preferable. Similarly, text columns containing numeric identifiers should be compared with appropriately typed text literals rather than forcing the database to convert the entire column.
Financial calculations require particular attention to precision and the order of operations. The deck recommends keeping intermediate calculations exact and rounding at the business-defined reporting point rather than repeatedly rounding intermediate values. For example, line-level tax and totals can be calculated using exact numerics and rounded once according to the invoice’s reporting rules. Importantly, the sum of individually rounded lines can differ slightly from the rounded sum of exact values, so reconciliation logic may be necessary. Integer minor units provide another robust approach for monetary storage because they eliminate decimal representation issues while retaining exact arithmetic. Weighted averages should use the sum of products divided by the sum of weights rather than a simple average when observations carry different quantities, while functions such as PERCENTILE_CONT can provide median and percentile measures.
The broader lesson is that numeric correctness and query performance are closely connected to data types and conversion choices. Integers are efficient for keys and counts, NUMERIC provides exact decimal arithmetic for values requiring precision, and approximate floating-point types should be reserved for domains where approximation is acceptable. Common mistakes include storing money as FLOAT, accidentally performing integer division, allowing division by zero, rounding too early, casting indexed columns inside predicates, and assuming a particular rounding mode without testing the database and data type involved. Keeping predicates cast-free, choosing the narrowest exact type that fits the domain, using NULLIF for safe ratios, and documenting the business rounding rule produces SQL that is both more reliable and easier for the database optimizer to execute efficiently.

https://docs.google.com/presentation/d/e/2PACX-1vQ8gQMBGU8nrFwgsfaEpJ6WICMhwSzcevAhSnm9eLwp3IXavKma02MQ1kiV1ePDtg/pub?start=true&loop=true&delayms=10000

Modern relational databases can handle semi-structured data without abandoning the relational model, making JSON particularly useful when part of a schema is genuinely variable. JSON works well for sparse category-specific attributes, external API or webhook payloads, and audit snapshots that need to preserve an original document. However, core entities that are regularly joined, constrained, filtered, sorted, or aggregated are generally better represented by typed relational columns. Putting an entire application schema into a single JSON document sacrifices constraints, data types, and useful planner statistics, while repeated extraction and casting can add unnecessary processing overhead. The practical approach is therefore to use JSON for the variable portion of a model while keeping frequently queried business data relational.

For PostgreSQL workloads, jsonb is generally the preferred type when JSON needs to be queried. Unlike plain json, which preserves the original textual representation, jsonb stores parsed binary data and supports indexing and containment operations. The deck’s extraction model is built around an important distinction: -> returns a JSON/JSONB value that can be navigated further, while ->> extracts text for comparison or casting. Path operators such as #> and #>> provide another way to reach nested values. JSON arrays can also be expanded into relational rows with functions such as JSONB_ARRAY_ELEMENTS, allowing nested items to be grouped, aggregated, and analysed like ordinary table data. PostgreSQL’s documentation confirms these extraction operators and JSONB-specific querying capabilities.
JSONB becomes especially powerful when filtering and indexing are designed around the actual access pattern. Containment with @> and key-existence operators such as ?, ?|, and ?& can be accelerated with GIN indexes, while expression indexes are useful when one extracted field is queried repeatedly. PostgreSQL provides both the default jsonb_ops GIN operator class and the more specialized jsonb_path_ops; the latter supports fewer operators but can provide better performance for supported containment and JSON-path workloads. The deck also highlights partial indexes for frequently queried subsets of data. The important principle is that indexing JSON is not simply about adding a GIN index everywhere: the index type should match the predicates the application actually executes.
JSON can also be generated directly from relational data, allowing databases to construct nested API responses using functions such as JSONB_BUILD_OBJECT and JSONB_AGG. At the modelling layer, the deck recommends promoting a JSON attribute to a typed column when it becomes a regular filtering, joining, constraint, sorting, or aggregation target. Generated columns can provide a practical bridge by extracting a frequently queried JSON value into a typed, indexable field while retaining the original document. This approach also addresses an important performance issue: repeated JSON extraction and casting consumes CPU, large documents may require additional storage and decompression work, and JSON predicates can provide weaker planner statistics than ordinary typed columns.
The central lesson is to treat JSON as a deliberate modelling tool rather than a replacement for relational design. Avoid confusing -> with ->>, compare numeric JSON values only after appropriate casting, index frequently queried keys, avoid putting frequently updated counters inside JSONB when whole-row rewrites would become expensive, and remember that JSONB_AGG returns NULL for an empty input unless it is wrapped with COALESCE. A robust production pattern is to retain raw webhook or external payloads, promote frequently accessed fields into generated or typed columns, expand nested arrays only when analytical queries require them, and aggregate the resulting rows at the appropriate grain. In this hybrid model, relational columns provide structure, constraints, statistics, and efficient access, while JSONB provides the flexibility needed for genuinely variable data.

https://docs.google.com/presentation/d/e/2PACX-1vSKTdTyPxNfLWz6ehjy4aYK_VaXrZde1mWMimU3LWI8l-69AgxISUqyndqT2aP1yA/pub?start=true&loop=true&delayms=10000

A particularly important aspect of SQL Joins is filtering correctly, especially when working with outer joins. The presentation demonstrates the difference between conditions placed in ON and WHERE: with an outer join, a condition on the optional table in WHERE can eliminate the NULL-padded rows and effectively turn a LEFT JOIN into an INNER JOIN. It also examines USING, NATURAL JOIN, compound keys, multi-table join chains, and joining to pre-aggregated subqueries. USING can provide concise syntax when column names match, whereas NATURAL JOIN is considered fragile because a newly added same-named column can unexpectedly change the join condition.

The presentation also goes beyond conventional joins to cover semi-joins, anti-joins, non-equi joins, range joins, and LATERAL joins. EXISTS can answer whether a matching record exists without duplicating the rows from the left table, while NOT EXISTS provides a NULL-safe way to find records with no match. Non-equi and range joins are useful when relationships depend on conditions such as ranges, validity periods, or tiers rather than simple equality. LATERAL allows a subquery to reference the current row from the left side, making it particularly useful for patterns such as retrieving the top three most recent orders for every customer.

Join performance depends heavily on the database engine’s execution strategy and on maintaining the correct grain of the result. The presentation explains three major physical join algorithms—Nested Loop, Hash Join, and Merge Join—and highlights the importance of reading EXPLAIN ANALYZE rather than guessing about performance. It also addresses the fan-out problem, where joining a one-to-many table before aggregation can multiply rows and artificially inflate sums or counts. Practical recommendations include indexing both sides of join keys, keeping key data types consistent, filtering before joining when appropriate, selecting only required columns, pre-aggregating one-to-many branches, and checking estimated versus actual row counts. These principles help produce SQL queries that are both accurate and efficient.

https://docs.google.com/presentation/d/e/2PACX-1vQUq-PqRJgAGmifrj8Uef22nuj3CZEPHSr6IiVEPmatp5W8QE7muYclRBfL2X4UuQ/pub?start=true&loop=true&delayms=10000