From bea784bd683f7e022dbfb3d72832d09fc7754913 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Wed, 1 May 2024 07:45:48 -0700 Subject: [PATCH] Document NULL --- doc/src/sgml/ddl.sgml | 2 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/func.sgml | 268 ++++++------- doc/src/sgml/nullvalues.sgml | 719 +++++++++++++++++++++++++++++++++++ 4 files changed, 837 insertions(+), 153 deletions(-) create mode 100644 doc/src/sgml/nullvalues.sgml diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 026bfff70f..68a0fe698d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -168,6 +168,8 @@ DROP TABLE products; + &nullvalues; + Default Values diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 38ec362d8f..882752e88f 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -21,6 +21,7 @@ + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 17c44bc338..98fba7742c 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -23295,7 +23295,8 @@ MERGE INTO products p This section describes the SQL-compliant subquery expressions available in PostgreSQL. All of the expression forms documented in this section return - Boolean (true/false) results. + three-valued typed + results (true, false, or null). @@ -23357,19 +23358,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression + subquery, which must return exactly one column. The result of IN + is false if the subquery returns no rows, otherwise the left-hand expression is evaluated and compared to each row of the subquery result. - The result of IN is true if any equal subquery row is found. - The result is false if no equal row is found (including the - case where the subquery returns no rows). + The result is true if any equal subquery row is found. + The result is false if no equal row is found. - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand row yields - null, the result of the IN construct will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + As explained in , it is not possible to see + a false result in the presence of both rows and null values since the multiple equality + tests are AND'd together. @@ -23386,21 +23385,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); as described in . The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are + expressions in the left-hand row. + The result of IN is false if the subquery returns no rows, + otherwise the left-hand expressions are evaluated and compared row-wise to each row of the subquery result. - The result of IN is true if any equal subquery row is found. - The result is false if no equal row is found (including the - case where the subquery returns no rows). + The result is true if any equal subquery row is found. + The result is false if no equal row is found. - As usual, null values in the rows are combined per - the normal rules of SQL Boolean expressions. Two rows are considered - equal if all their corresponding members are non-null and equal; the rows - are unequal if any corresponding members are non-null and unequal; - otherwise the result of that row comparison is unknown (null). - If all the per-row results are either unequal or null, with at least one - null, then the result of IN is null. + As explained in , it is not possible to see + a false result in the presence of both rows and null values since the multiple equality + tests are OR'd together. @@ -23412,20 +23408,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); - The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression - is evaluated and compared to each row of the subquery result. - The result of NOT IN is true if only unequal subquery rows - are found (including the case where the subquery returns no rows). + The right-hand side is a parenthesized subquery, which must return exactly one column. + The result of NOT IN is true if the subquery returns no rows, + otherwise the left-hand expression is evaluated and compared to each row of the subquery result. + The result is true if only unequal subquery rows are found. The result is false if any equal row is found. - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand row yields - null, the result of the NOT IN construct will be null, not true. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + As explained in , it is not possible to see + a true result in the presence of both rows and null values since the multiple inequality + tests are OR'd together. @@ -23442,21 +23435,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); as described in . The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are + expressions in the left-hand row. + The result of NOT IN is true if the subquery returns no rows, + otherwise the left-hand expressions are evaluated and compared row-wise to each row of the subquery result. - The result of NOT IN is true if only unequal subquery rows - are found (including the case where the subquery returns no rows). + The result is true if only unequal subquery rows are found. The result is false if any equal row is found. - As usual, null values in the rows are combined per - the normal rules of SQL Boolean expressions. Two rows are considered - equal if all their corresponding members are non-null and equal; the rows - are unequal if any corresponding members are non-null and unequal; - otherwise the result of that row comparison is unknown (null). - If all the per-row results are either unequal or null, with at least one - null, then the result of NOT IN is null. + As explained in , it is not possible to see + a true result in the presence of both rows and null values since the multiple inequality + tests are OR'd together. @@ -23470,13 +23460,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression + subquery, which must return exactly one column. The result of ANY is + false if the subquery returns no rows, otherwise the left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. - The result of ANY is true if any true result is obtained. - The result is false if no true result is found (including the - case where the subquery returns no rows). + The result is true if any true result is obtained. + The result is false if no true result is found. @@ -23485,11 +23475,10 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); - Note that if there are no successes and at least one right-hand row yields - null for the operator's result, the result of the ANY construct - will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + If operator can produce null valued booleans then, as explained in + , it is not possible to see + a false result in the presence of both rows and null values since the multiple equality + tests are AND'd together. Note that IS DISTINCT FROM is not an operator. @@ -23507,16 +23496,19 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); as described in . The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are + expressions in the left-hand row. The result of ANY is + false if the subquery returns no rows, otherwise the left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given operator. - The result of ANY is true if the comparison - returns true for any subquery row. - The result is false if the comparison returns false for every - subquery row (including the case where the subquery returns no - rows). - The result is NULL if no comparison with a subquery row returns true, - and at least one comparison returns NULL. + The result is true if the comparison returns true for any subquery row. + The result is false if the comparison returns false for every subquery row. + + + + If operator can produce null valued booleans then, as explained in + , it is not possible to see + a false result in the presence of both rows and null values since the multiple equality + tests are OR'd together. Note that IS DISTINCT FROM is not an operator. @@ -23534,15 +23526,20 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression + subquery, which must return exactly one column. The result of ALL is + true if the subquery returns no rows, otherwise the left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. - The result of ALL is true if all rows yield true - (including the case where the subquery returns no rows). + The result is true if all rows yield true. The result is false if any false result is found. - The result is NULL if no comparison with a subquery row returns false, - and at least one comparison returns NULL. + + + + If operator can produce null valued booleans then, as explained in + , it is not possible to see + a true result in the presence of both rows and null values since the multiple equality + tests are AND'd together. Note that IS DISTINCT FROM is not an operator. @@ -23563,22 +23560,21 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); as described in . The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are + expressions in the left-hand row. The result of ALL is + true if the subquery returns no rows, otherwise the left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given operator. - The result of ALL is true if the comparison - returns true for all subquery rows (including the - case where the subquery returns no rows). - The result is false if the comparison returns false for any - subquery row. - The result is NULL if no comparison with a subquery row returns false, - and at least one comparison returns NULL. + The result is true if the comparison returns true for all subquery rows. + The result is false if the comparison returns false for any subquery row. - See for details about the meaning - of a row constructor comparison. + If operator can produce null valued booleans then, as explained in + , it is not possible to see + a true result in the presence of both rows and null values since the multiple equality + tests are AND'd together. Note that IS DISTINCT FROM is not an operator. + @@ -23603,6 +23599,14 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); compared row-wise to the single subquery result row. + + If operator can produce null valued booleans then, as explained in + , the result cannot be true in the + presence of null valued fields in either the row constructor or the subquery result row, as + the individual field tests are AND'd together. + Note that IS DISTINCT FROM is not an operator. + + See for details about the meaning of a row constructor comparison. @@ -23670,7 +23674,8 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); PostgreSQL extensions; the rest are SQL-compliant. All of the expression forms documented in this section return - Boolean (true/false) results. + three-valued boolean typed + results (true, false, or null). @@ -23683,24 +23688,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); The right-hand side is a parenthesized list of expressions. The result is true if the left-hand expression's - result is equal to any of the right-hand expressions. This is a shorthand - notation for - - -expression = value1 -OR -expression = value2 -OR -... - + result is equal to any of the right-hand expressions. - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand expression yields - null, the result of the IN construct will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + As explained in , it is not possible to see + a false result in the presence null values since the multiple equality + tests are OR'd together. @@ -23714,35 +23708,15 @@ OR The right-hand side is a parenthesized list of expressions. The result is true if the left-hand expression's - result is unequal to all of the right-hand expressions. This is a shorthand - notation for - - -expression <> value1 -AND -expression <> value2 -AND -... - + result is unequal to all of the right-hand expressions. - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand expression yields - null, the result of the NOT IN construct will be null, not true - as one might naively expect. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + As explained in , it is not possible to see + a true result in the presence of null values since the multiple inequality + tests are OR'd together. - - - x NOT IN y is equivalent to NOT (x IN y) in all - cases. However, null values are much more likely to trip up the novice when - working with NOT IN than when working with IN. - It is best to express your condition positively if possible. - - @@ -23755,30 +23729,26 @@ AND The right-hand side is a parenthesized expression, which must yield an - array value. - The left-hand expression + array value. The result of ANY is + false if the array has zero element, otherwise + the left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. - The result of ANY is true if any true result is obtained. - The result is false if no true result is found (including the - case where the array has zero elements). + The result is true if any true result is obtained. + The result is false if no true result is found. - If the array expression yields a null array, the result of - ANY will be null. If the left-hand expression yields null, - the result of ANY is ordinarily null (though a non-strict - comparison operator could possibly yield a different result). - Also, if the right-hand array contains any null elements and no true - comparison result is obtained, the result of ANY - will be null, not false (again, assuming a strict comparison operator). - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + If operator can produce null valued booleans then, as explained in + , it is not possible to see + a false result in the presence of both elements and null values since the multiple equality + tests are AND'd together. Note that IS DISTINCT FROM is not an operator. SOME is a synonym for ANY. + IN is equivalent to = ANY. @@ -23792,26 +23762,27 @@ AND The right-hand side is a parenthesized expression, which must yield an array value. - The left-hand expression + The result of ALL is + true if the array has zero elements, otherwise + the left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. - The result of ALL is true if all comparisons yield true - (including the case where the array has zero elements). + The result is true if all comparisons yield true. The result is false if any false result is found. - If the array expression yields a null array, the result of - ALL will be null. If the left-hand expression yields null, - the result of ALL is ordinarily null (though a non-strict - comparison operator could possibly yield a different result). - Also, if the right-hand array contains any null elements and no false - comparison result is obtained, the result of ALL - will be null, not true (again, assuming a strict comparison operator). - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + If operator can produce null valued booleans then, as explained in + , it is not possible to see + a true result in the presence of both elements and null values since the multiple equality + tests are AND'd together. Note that IS DISTINCT FROM is not an operator. + + + + NOT IN is equivalent to <> ALL. + @@ -23896,20 +23867,11 @@ AND - The SQL specification requires row-wise comparison to return NULL if the - result depends on comparing two NULL values or a NULL and a non-NULL. - PostgreSQL does this only when comparing the - results of two row constructors (as in - ) or comparing a row constructor - to the output of a subquery (as in ). - In other contexts where two composite-type values are compared, two - NULL field values are considered equal, and a NULL is considered larger - than a non-NULL. This is necessary in order to have consistent sorting - and indexing behavior for composite types. - - - - Each side is evaluated and they are compared row-wise. Composite type + Each side is evaluated and they are compared row-wise. + As discussed in , + null values are treated as being equal to other null values and greater + than all non-null values. + Composite type comparisons are allowed when the operator is =, <>, diff --git a/doc/src/sgml/nullvalues.sgml b/doc/src/sgml/nullvalues.sgml new file mode 100644 index 0000000000..d77235a527 --- /dev/null +++ b/doc/src/sgml/nullvalues.sgml @@ -0,0 +1,719 @@ + + Null Values + + + null value + + + + This section first introduces the concept of null values and then goes + on to explain how different parts of the system behave when provided + one or more null value inputs. Examples throughout this section + can be executed so long as the following table and rows are created first. + + + + CREATE TABLE null_examples ( + id bigint PRIMARY KEY, + value integer NULL + ); + INSERT INTO null_examples + VALUES (1, 1), (2, NULL), (3, 4); + + + + Meaning + + Generally a null value is assumed to mean "unknown", but other interpretations + are common. A data model design may state that a null value + is to be used to represent "not applicable" - i.e., that a value is not + even possible. The null value also takes on a literal meaning of "not found" + when produced as the result of an outer join. + + + + + Usage + + A null value, like all values, must have a data type, and is valid for all data types. + + + A null value literal is written as unquoted, case insensitive, NULL. + Its type is the pseudo-type unknown but can be cast to any concrete data type. + The type 'string' + syntax, however, will not work as there is no way to express + the NULL using single quotes. + + + + SELECT + NULL, + pg_typeof(null), + pg_typeof(NuLl::text), + cast(null as text); + + + ?column? | pg_typeof | pg_typeof | text + ----------+-----------+-----------+------ + | unknown | text | + + + + + SELECT text NULL; + + + ERROR: column "text" does not exist + LINE 1: select text NUll; + + + + The presence of null values in the system results in three-valued logic. + In binary logic every outcome is either true or false. In + three-valued logic unknown, represented using a null value, is + also an outcome. Put a bit more formally, the + Law of the Excluded Middle does not hold: i.e., + P OR NOT(p) != true; for all p. + + + Aspects of the system that branch based upon + whether a condition variable is true or false must therefore + decide how to behave when then input condition is a null value. + The remaining sub-sections summarize these decisions. + + + In there are two broad classes of such comparison tests: first determining + whehow a given value compares to one or more other values, and second, determining + how two multi-element values compare to each other. + + + + + Distinctness - Overcoming the Cardinal Rule of Null Values + + The cardinal rule, a given null value is never + equal or unequal + to any other non-null. + + SELECT + NULL = NULL as "N = N", NULL != NULL as "N != N", + 1 = NULL as "1 = N", 1 != NULL as "1 != N", + 1 = 1 as "1 = 1", 1 != 1 as "1 != 1"; + + + N = N | N != N | 1 = N | 1 != N | 1 = 1 | 1 != 1 + -------+--------+-------+--------+-------+-------- + | | | | t | f + + However, as with many rules, there are exceptions + noted below. Specifically, when + when the two compared values are part of a larger multi-element value. + + select array[1,2]=array[1,null]; + + + ?column? + ---------- + f + (1 row) + + + + Because of this SQL specification mandated rule checking for a null value has an + explicitIS NULL test, + and additionally there are distinctness tests + (e.g., IS DISTINCT, and IS TRUE) + that consider a null value equal to another null value and unequal + to any other value. These and other tests are described in + + + SELECT id, value, + value IS NULL as "IS N", + value IS DISTINCT FROM id as "IS D", + value != id as "IS !=" + FROM null_examples; + + + id | value | IS N | IS D | IS != + ----+-------+------+------+------- + 1 | 1 | f | f | f + 2 | | t | t | + 3 | 4 | f | t | t + + + + There is also a cardinal warning: when dealing with + composite types + expressions; composite IS NULL + and composite IS NOT NUll + are not the opposites of each other in the case where some, + but not all, of the composite's fields are null values. + (The case where all fields are null is indistinguishable + from the composite as a whole being null.) + Write NOT(composite IS NULL) instead. + + SELECT + c, + c IS NULL as "c IS N", + NOT(c IS NULL) as "NOT c IS N", + c IS NOT NULL as "c IS NOT N", + ROW(value, value) IS NULL as "ROW(v,v) IS N", + ROW(value, value) IS NOT NULL as "ROW(v,v) IS NOT N" + FROM null_examples AS c; + + + c | c IS N | NOT c IS N | c IS NOT N | ROW(v,v) IS N | ROW(v,v) IS NOT N + -------+--------+------------+------------+---------------+------------------- + (1,1) | f | t | t | f | t + (2,) | f | t | f | t | f + (3,4) | f | t | t | f | t + + See the multi-element + testing section below for an explanation. + + + + + Null Valued Operands + + As a general expectation, operator invocation expressions where one of inputs + is a null value will result in a null valued output. + + SELECT + 1 + null as "Add", + 'text' || null as "Concatenate"; + + + Add | Concatenate + -----+------------- + | + + Operators that behave otherwise should document their deviation from this norm. + + + A notable example of this is the IN operator, which + uses equality, not distinctness, for testing. + + SELECT + 1 IN (1, null) as "In Present", + 1 IN (2, null) as "In MIssing", + null IN (1, 2) as "N In Non-N", + null IN (null, 2) as "N In N"; + + + In Present | In Missing | N In Non-N | N In N + ------------+------------+------------+-------- + t | | | + + This is just an extension of the multi-element testing behavior described + below. + + + Experience shows that CASE expressions are also prone + to bugs since their format encourages binary logic thinking while a + WHEN test will not consider a null value to be a match. + + SELECT id, value, + CASE WHEN id = value THEN 'Equal' ELSE 'Not Equal' END as "Affirm", + CASE WHEN id != value THEN 'Not Equal' ELSE 'Equal' END as "Negate", + CASE WHEN value IS NULL THEN 'Null' + WHEN id = value THEN 'Equal' + ELSE 'Not Equal' END as "Safe Affirm", + CASE WHEN value IS NULL THEN 'Null' + WHEN id != value THEN 'Not Equal' + ELSE 'Equal' END as "Safe Negate" + FROM null_examples; + + + id | value | Affirm | Negate | Safe Affirm | Safe Negate + ----+-------+-----------+-----------+-------------+------------- + 1 | 1 | Equal | Equal | Equal | Equal + 2 | | Not Equal | Equal | Null | Null + 3 | 4 | Not Equal | Not Equal | Not Equal | Not Equal + + + + The boolean operators AND and OR + will ignore the null value input if the other input is sufficient to + to determine the outcome. + + SELECT + true OR null as "T or N", + false OR null as "F or N", + true AND null as "T and N", + false AND null as "F and N"; + + + T or N | F or N | T and N | F and N + --------+--------+---------+--------- + t | | | f + + + + + + Null Valued Composite Fields + + When a composite type is used, a null value can be assigned to any of its fields. + So long as at least one field is non-null the composite value as whole exists + and an IS NULL test on it will return false. + + + The IS NOT NULL test on a composite performs a test the checks whether + all fields of the composite have non-null values. This is not the same + as a non-null composite value. Specifically, if the composite value has + a null valued field then both this test and the IS NULL test will return false. + + + Please read for a complete treatment + on how PostgreSQL handle row-wise comparison. The + next two multi-element parts of this section discuss those comparisons in the + presence of null valued fields and also in terms of the SQL specification. + + + + + Testing Multi-Element Values with Null Elements + + Arrays and composite types are multi-element types. Here we also consider non-empty + subquery results + and the list of values specified in the + IN test. + When a test is performed on one of these multi-element values + the system will iterate over each element, or pair of elements if the test is + comparing two row constructors to each other, + left-to-right, combining the results using the boolean operations + discussed above. For tests that + require an exhaustive search, (e.g., ALL, NOT IN) + the search effectively ends when a false result is found (AND combiners). + For tests that simply require a true result, (e.g., ANY, + IN) the search effectively ends when a true result is found + (OR combiners). Therefore: + + + IN and ANY + (OR) cannot produce a false result in the presence of null, and + + + NOT IN and ALL + (AND) cannot produce a true result in the presence of null. + + + This is because any exhaustive search will produce at least one null value result + that cannot be ignored. + + + The SQL specification requires that non-exhaustive + (e.g., IN and ANY) subquery tests + return false when there are no rows in the subquery result, and return true + for the exhaustive tests (i.e., ALL). + + + Note that the cardinal warning + noted above is just the application of this behavior to the + IS NULL and IS NOT NULL + tests, which are both exhaustive search tests guaranteed to produce at least one false result + when the composite has a mix of null and non-null values. + + + Note that the rules above are applied to situation with a predicate or a scalar value + are being compared to a multi-element value. The rules when two multi-element values are compared + to each other are discussed next. + The two row constructor comparison case included above is also noted below. + + + + + Multi-Element Comparisons + + The prior section discussed applying + a predicate or a scalar value check element-wise across a multi-element value. + This section discusses + comparing two multi-element values to each other. As both array and composite typed values + can be stored within an index, and comparing two values in that context must not produce + a null valued result, considerations are made to adhere to the SQL specification where + possible while still making indexes, which the specification is silent on, functional. + Specifically, except when comparing two row constructors, null values are considered + equal to other null values and greater than all non-null values. + + + There are five pair-wise comparison situations to consider: + element-wise when the inputs are arrays, and row-wise when the inputs can be either + row constructors or composite typed values. While these four later combinations seem similar, + the fact that row constructors are query literals, while composite typed values can be stored, + brings about important differences in how they are treated. Please read + for a fuller treatment of this topic. Here + we briefly recap the five different sitautions in the presence of null values. + + + Element-wise Comparisons + + First, null values within an array compare as equal to each other and greater than all + non-null values, regardless of whether the comparison involves + array constructors or array typed values. + + select array[1,2]=array[1,null], s, t, s = t, t > s + from + (values (array[1,2])) sv (s), + (values (array[1,null::integer])) st (t); + + + ?column? | s | t | ?column? | ?column? + ----------+-------+----------+----------+---------- + f | {1,2} | {1,NULL} | f | t + + + + + Row-Wise Mutual Row Constructor Comparisons + + In this situation null values produce unknown when compared to all values. + + select (1,2)=(1,null), (1,null::integer)=(1,null); + + + ?column? | ?column? + ----------+---------- + | + (1 row) + + + + + Row-Wise Composite Involved Comparisons + + In these three situations null values are considered equal to each other and greater than + all non-null value. + + + select s, t, s = t, t < (1,2), t = (1,null::integer) + from (values ((1,2))) sv (s), + (values ((1,null::integer))) st (t); + + + s | t | ?column? | ?column? | ?column? + -------+------+----------+----------+---------- + (1,2) | (1,) | f | f | t + + + + SQL Conformance + + The SQL specification requires row-wise comparison to return NULL if the + result depends on comparing two NULL values or a NULL and a non-NULL. + PostgreSQL does this only when comparing the + results of two row constructors (as in + ) or comparing a row constructor + to the output of a subquery (as in ). + + + + + + Null Valued Arguments in Normal Function Calls + + Function specifications + have a "strictness" attribute that, when set to "strict" + (a.k.a. "null on null input") will tell the executor to return a null value for any + function call having at least one null valued input, without executing the + function. + + + Most functions, especially single argument functions, are defined with strict because without + non-null values to act upon they cannot produce a meaningful result. However, for multi-argument + functions, especially variadic functions + like concatenate, null values often are simply ignored. + This can be different than the choice made by a binary operator performing the same function, + like for concatenating text, but not always, like concatenating an element onto an array. + + SELECT + lower(null::text) as "Lower", + left('text', null) as "Left", + 'one' || null as "|| Text Op", + concat('one', null) as "concat Text Func", + array_append(array[1], null) as "append([], null)", + array[1]::integer[] || null::integer as "[] || null", + array[1]::integer[] || null::integer[] as "[] || null[]"; + + + Lower | Left | || Text Op | concat Text Func | append([], null) | [] || null | [] || null[] + -------+------+------------+------------------+------------------+------------+-------------- + | | | one | {1,NULL} | {1,NULL} | {1} + + In short, please read the documentation for the functions you use if they may receive null inputs + to understand how they will behave. Send a documentation comment pointing out any functions + that do not behave strictly but whose actual behavior in the presence of null valued input + is not described or readily inferred. + + + + + Null Valued Arguments in Aggregate and Window Processing + + When executing an aggregate or window function the state tracking + component will remain unchanged even if the underlying processing + function returns a null value, whether from being defined strict + or it simply returns a null value upon execution. The aggregation + routine will usually ignore the null value and continue processing, + as demonstrated in count(value) below. + + SELECT + count(*) as "Count", + count(value) as "Count Value", + count(null_examples) as "Count Composite", + count(row(value, value)) as "Count Row" + FROM null_examples; + + + Count | Count Value | Count Composite | Count Row + -------+-------------+-----------------+----------- + 3 | 2 | 3 | 3 + + Notice the "Count Row" outcome, though. While we noted in the cardinal warning + that a composite whose fields are all null values is indistinguishable from + a null value of composite type, the count aggregate does indeed distinguish them, + recognizing and counting the non-null composite value produced by the + row constructor + count(row(value, value)). + + + + + Null Values in Filters + + A WHERE clause that evaluates to a null value for a given row will exclude that row. + + SELECT id, value FROM null_examples WHERE value = 1; + + + id | value + ----+------- + 1 | 1 + + + + + + Null Values in Constraints + + It is possible to define validation expressions + (check constraints) + on tables that ensure only values passing those expressions are inserted. While + this seems like it would behave the same as a filter, the choice here, + when an expression evaulates to a null value, is to allow the row to be inserted + - the same as a true result. + + BEGIN; + ALTER TABLE null_examples ADD CONSTRAINT value_not_1 CHECK (value != 1); + ROLLBACK; + + + BEGIN + ERROR: check constraint "value_not_1" of relation "null_examples" is violated by some row + ROLLBACK + + + BEGIN; + ALTER TABLE null_examples ADD CONSTRAINT value_not_10 CHECK (value != 10); + ROLLBACK; + + + BEGIN + ALTER TABLE + ROLLBACK + + We are using a transaction + (begin and rollback) and the alter table command to add two + constraints to our null_examples table. The first constraint prohibits rows with a value + of 1, which our row with an id of 1 violates. Prohibiting the value 10 definitely allows + rows with ids 1 and 3 to exist, and since we are not told that some row violates our + constraint the null value in the row with id 2 is being accepted as well. + + + The NOT NULL column constraint + produces the same answer as a column IS NOT NULL check constraint but is + more concise to write. + + + + + Null Values When Grouping + + In the context of both DISTINCT and GROUP BY + it is necessary that all inputs resolve to being either equal to or not equal to all + other values. These features use distinctness + instead of simple equality in order to handle a null value like a definite value equal to + another null vale and unequal to all other values. + + WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL)) + SELECT + value, + count(*) as "Count" + FROM vals + GROUP BY value + ORDER BY value; + + + value | Count + -------+------- + 1 | 2 + 2 | 1 + | 2 + + + WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL)) + SELECT DISTINCT value FROM vals + ORDER BY value NULLS FIRST; + + + value + ------- + + 1 + 2 + + + + + + Null Values When Ordering + + In the context of ORDER BY, distinctness rules also apply, + though this is insufficient since it must be determined whether or not to + present null values before or after all non-null values. To handle + this, the ORDER BY clause will let you specify either + NULLS FIRST or NULLS LAST. + + WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL)) + SELECT value FROM vals + ORDER BY value DESC NULLS FIRST; + + + value + ------- + + + 2 + 1 + 1 + + + + Note that when dealing with multi-element values the comparison behavior + described above applies, + if the comparison determination rests upon comparing a null value to a non-null value + the multi-element value with the null valued component will sort greater than the one + with a non-null component. + + + + + Null Values in Indexes + + The uniqueness and relative ordering rules applied to null values + are defined when creating an index. For the default + NULLS DISTINCT uniqueness, equality rules are applied. + Specifying NULLS NOT DISTINCT will result in + IS DISTINCT FROM rules being applied whereby all null + values are equal to each other. This setting applies to all columns in the index. + + + BEGIN; + CREATE UNIQUE INDEX value_nulls_distinct_implicit ON null_examples (value); + CREATE UNIQUE INDEX value_nulls_distinct_explicit ON null_examples (value) NULLS DISTINCT; + INSERT INTO null_examples VALUES (4, NULL); + ROLLBACK; + + + BEGIN + CREATE INDEX + CREATE INDEX + INSERT 0 1 + ROLLBACK + + + BEGIN; + CREATE UNIQUE INDEX value_nulls_not_distinct_explicit ON null_examples (value) NULLS NOT DISTINCT; + INSERT INTO null_examples VALUES (4, NULL); + ROLLBACK; + + + BEGIN + CREATE INDEX + ERROR: duplicate key value violates unique constraint "value_nulls_not_distinct_explicit" + DETAIL: Key (value)=(null) already exists. + ROLLBACK + + + For ordering, each column in the index gets its own specification of + direction and null value placement similar to that found in the + ORDER BY clause. + + + Note that when dealing with multi-element values the comparison behavior + described above applies, + if the comparison determination rests upon comparing a null value to a non-null value + the multi-element value with the null valued component will sort greater than the one + with a non-null component. + + + + + Null Valued Settings + + There are none. During initializion all settings are assigned a non-null value. + + + This is mostly meaningful for custom settings, + thus this section focuses on SQL interaction. + Unlike settings created by extensions, custom settings can only be textual and the default + value for text here is the empty string. + + SHOW example.string; + BEGIN; + SELECT set_config('example.string', NULL, true); + SELECT current_setting('example.string') IS NULL AS "Setting Is Null"; + ROLLBACK; + SHOW example.string; + RESET example.string; + SHOW example.string; + + + ERROR: unrecognized configuration parameter "example.string" + BEGIN + set_config + ------------ + + (1 row) + + Setting Is Null + ----------------- + f + (1 row) + + ROLLBACK + example.string + ---------------- + + (1 row) + + RESET + example.string + ---------------- + + (1 row) + + Notice two important behaviors: first, even though we passed in a null value to + to the set_config function, the current_setting + function returned a non-null value, specifically the empty string. Second, after ROLLBACK the + setting is still present (i.e., the error seen before creating the setting no longer appears), + and in fact will remain so until the session ends + (i.e., RESET does not restore the non-existence state.) + + + The other ways to specify settings do not have a means to specify null values, + a specific non-null value is required as part of the specification of the setting. + + + + -- 2.34.1