SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
How scalar functions transform one value at a time
Predict how many rows a query returns when it uses per-row functions, nest them correctly, and tell scalar functions apart from aggregates.
What you will learn
- A scalar function returns one value per row, so it never changes the row count.
- Use the same call in SELECT, WHERE, and ORDER BY; it is evaluated once per row.
- Reach for GREATEST/LEAST across columns, MAX/MIN down rows.
- Stop NULL propagation with COALESCE instead of expecting 0 or an empty string.
Understanding How scalar functions transform one value at a time
A scalar function is a per-row calculation: it receives values from one row (or plain literals) and hands back exactly one value. The engine calls it once for every row that reaches that point in the query, so a four-row table produces four results, never three and never one. That fixed one-in, one-out contract is why a scalar call can sit in the select list beside a raw column with no GROUP BY: it adds a derived column without changing the shape of the result.
Because the call is an expression rather than a statement, it is legal anywhere the grammar expects a value: the select list, WHERE, ORDER BY, GROUP BY, a CHECK constraint, a generated column. The value it returns has a data type, and that type is what makes nesting work, since ABS(celsius) yields a numeric that COALESCE can then consume. Evaluation runs inside out, the innermost call first, which is also why MAX(ABS(celsius)) computes each row's magnitude before the aggregate collapses the column.
A scalar function only ever sees the current row and has no way to look at its neighbours, so it cannot average a column, fill a gap from the previous row, or number rows; those jobs belong to aggregates and window functions. The same narrow view explains NULL handling: NULL means the value is unknown, and a transformation of an unknown input is still unknown, so most scalar functions return NULL rather than 0 or an empty string. When that NULL reaches a comparison in WHERE, the comparison evaluates to unknown and the row is dropped, which is how filtering on a function quietly loses rows you expected to keep.
Nothing a scalar function returns is stored. The derived column exists only in the result set you are looking at, so the bytes on disk still hold the original value until an UPDATE writes the function's output back.
CREATE TABLE reading (sensor text, celsius numeric);
INSERT INTO reading VALUES ('a-1', -3.5), ('a-2', 21.0), ('b-7', NULL), ('b-9', 8.25);
SELECT sensor,
celsius,
ABS(celsius) AS magnitude,
COALESCE(ABS(celsius), 0) AS filled
FROM reading;
SELECT COUNT(*) AS rows_scanned, MAX(ABS(celsius)) AS biggest
FROM reading;A scalar function is a value-to-value transformation applied independently to each row, so it changes the columns of a result but never the number of rows.
Worked examples
One function, three clauses
The same per-row call drives the output column, the filter, and the sort order of one query over the reading table.
SELECT sensor, ABS(celsius) AS magnitude
FROM reading
WHERE ABS(celsius) >= 8
ORDER BY ABS(celsius) DESC;Example explained
Line 1ABS(celsius) in the select list produces the derived column; the copy in WHERE is evaluated again for each candidate row to decide inclusion.
Line 2Row b-7 holds NULL, so ABS(celsius) is NULL and NULL >= 8 is unknown rather than true, and the row is filtered out with no error.
Line 3ORDER BY ABS(celsius) DESC sorts on the computed magnitude, so sign is ignored; a value of -30 would sort ahead of 21.0.
Line 4Because the filter compares a function of celsius instead of celsius itself, a plain index on celsius cannot be used; an index on ABS(celsius) can.
GREATEST across a row, MAX down a column
Two functions with almost the same meaning operate on different axes and therefore return different numbers of rows.
CREATE TABLE shift_temp (station text, morning numeric, evening numeric);
INSERT INTO shift_temp VALUES ('north', 12.0, 9.5), ('south', 18.0, 22.5);
SELECT station,
GREATEST(morning, evening) AS warmer_half,
LEAST(morning, evening) AS cooler_half
FROM shift_temp;
SELECT MAX(morning) AS hottest_morning FROM shift_temp;Example explained
Line 1GREATEST(morning, evening) compares two values inside one row, so it emits one value per station and the result still has 2 rows.
Line 2LEAST is the mirror image and is evaluated on the same row during the same pass, not in a second scan of the table.
Line 3MAX(morning) walks the whole column and collapses it to one row, which is why it cannot sit beside station without GROUP BY.
Line 4Writing MAX(morning, evening) fails outright because an aggregate takes one argument per call, so this confusion shows up as an error rather than wrong numbers.
Derived value versus stored value
A scalar function in SELECT leaves the table untouched; only an UPDATE persists what it returns.
SELECT sensor, ABS(celsius) AS magnitude FROM reading WHERE sensor = 'a-1';
SELECT sensor, celsius FROM reading WHERE sensor = 'a-1';
UPDATE reading SET celsius = ABS(celsius) WHERE sensor = 'a-1';
SELECT sensor, celsius FROM reading WHERE sensor = 'a-1';Example explained
Line 1The first query reports 3.5 while the stored value is still -3.5, because SELECT only builds a result set.
Line 2Re-reading celsius immediately afterwards proves the magnitude column existed nowhere but in the previous output.
Line 3UPDATE ... SET celsius = ABS(celsius) runs the same per-row call, but the returned value is written back, and psql reports UPDATE 1 for the single affected row.
Line 4The function reads the value present in the row at that moment, so repeating this statement is harmless for ABS but would keep compounding for something like celsius * 2.
Important notes
Values shown here are formatted by PostgreSQL's psql client; SQLite and MySQL return the same values with different column alignment.
GREATEST and LEAST disagree on NULL between engines: PostgreSQL and SQLite ignore NULL arguments, while MySQL and Oracle return NULL if any argument is NULL.
Common mistakes
Running SELECT ABS(celsius) and expecting the table to change: the function only builds a derived column, so the next query still reads -3.5 until an UPDATE writes the result back.
Calling MAX(morning, evening) to compare two columns in one row: aggregates accept a single argument, so the engine raises an error such as function max(numeric, numeric) does not exist, and GREATEST is the per-row form you wanted.
Assuming a function converts NULL to 0 or an empty string: ABS(NULL) is NULL, so WHERE ABS(celsius) >= 8 silently discards that row and the reported totals come out short.
Try it yourself
Change, predict, then run
Create a table with three numeric readings, one of them NULL, then write a single query returning the raw column, ABS of it, and COALESCE(ABS(...), 0) side by side. Confirm the result has as many rows as the table and that only the COALESCE column has a value on the NULL row.
Open the SQL workspaceCheck your understanding
shift_temp has 2 rows. How does SELECT GREATEST(morning, evening) FROM shift_temp; differ from SELECT MAX(morning) FROM shift_temp;?
- GREATEST returns 2 rows because it compares values within a single row; MAX returns 1 row because it collapses values down a column.
- Both return 1 row, since GREATEST is just MAX written with two arguments.
- Both return 2 rows, since every function in a select list is evaluated once per row.
- GREATEST needs GROUP BY station to produce one row per station, exactly as MAX does.
Show answer
GREATEST is scalar: it sees only the current row, so 2 input rows give 2 output rows. MAX is an aggregate and reduces the whole column to a single value, which is why it appears alone without GROUP BY. Option 2 is tempting because both names suggest a maximum, but they take their maximum along different axes, and that difference is exactly what determines the row count.