On Tue, Mar 11, 2025 at 8:51 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> Version 7
> https://wiki.postgresql.org/wiki/Documenting_NULL
> https://commitfest.postgresql.org/patch/5086/
>
> The only item that came up that I'm unable to address myself is discussion
> comparing a NOT NULL column constraint to an equivalent check constraint.
> I've left things documented as semantically equivalent. A future patch can
> clear those things up. At this point I'm considering this patch content
> complete.
>
> I figure to make any last tweaks against this version 7, combine the two
> patches into one and submit v8 as ready to commit should a reviewer agree.
>
>
Version 8.
Marking this Ready to Commit in CF 2025-09 (CF PG19-1)
David J.
From e8f398d80966c9ea3211b336ba91e225d8e18746 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Wed, 18 Jun 2025 10:38:24 -0700
Subject: [PATCH] doc: Add an overview of NULL treatment in PostgreSQL
The handling of NULL within PostgreSQL is broad, varied, and
unintuitive. This new section provides a place for the reader
to go and skim over the various behaviors and stick them in
the back of their mind.
Some existing documentation is tweaked to be more in line with
the overall flow of the material presented in this overview.
The individual features do still constitute an authoritative
location for describing the behaviors - but now have a more
convenient place to put examples.
---
doc/src/sgml/datatype.sgml | 2 +-
doc/src/sgml/ddl.sgml | 2 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/func.sgml | 273 +++----
doc/src/sgml/json.sgml | 7 +-
doc/src/sgml/nullvalues.sgml | 1126 +++++++++++++++++++++++++++
doc/src/sgml/ref/create_domain.sgml | 7 +-
doc/src/sgml/syntax.sgml | 23 +-
8 files changed, 1281 insertions(+), 160 deletions(-)
create mode 100644 doc/src/sgml/nullvalues.sgml
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 09309ba0390..53fd7fe3fa1 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -5389,7 +5389,7 @@ WHERE ...
<row>
<entry><type>unknown</type></entry>
<entry>Identifies a not-yet-resolved type, e.g., of an undecorated
- string literal.</entry>
+ string literal. Also, the <link linkend="nullvalues-usage">null value.</link></entry>
</row>
</tbody>
</tgroup>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 96936bcd3ae..0f2ce1bfb88 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -168,6 +168,8 @@ DROP TABLE products;
</para>
</sect1>
+ &nullvalues;
+
<sect1 id="ddl-default">
<title>Default Values</title>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index fef9584f908..53b70f31e9b 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -21,6 +21,7 @@
<!ENTITY indices SYSTEM "indices.sgml">
<!ENTITY json SYSTEM "json.sgml">
<!ENTITY mvcc SYSTEM "mvcc.sgml">
+<!ENTITY nullvalues SYSTEM "nullvalues.sgml">
<!ENTITY parallel SYSTEM "parallel.sgml">
<!ENTITY perform SYSTEM "perform.sgml">
<!ENTITY queries SYSTEM "queries.sgml">
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..324d6ca9fff 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23790,7 +23790,8 @@ MERGE INTO products p
This section describes the <acronym>SQL</acronym>-compliant subquery
expressions available in <productname>PostgreSQL</productname>.
All of the expression forms documented in this section return
- Boolean (true/false) results.
+ <link linkend="nullvalues">three-valued</link> typed
+ results (true, false, or null).
</para>
<sect2 id="functions-subquery-exists">
@@ -23852,19 +23853,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
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 <token>IN</token>
+ is <quote>false</quote> 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 <token>IN</token> is <quote>true</quote> if any equal subquery row is found.
- The result is <quote>false</quote> if no equal row is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any equal subquery row is found.
+ The result is <quote>false</quote> if no equal row is found.
</para>
<para>
- 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 <token>IN</token> construct will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together.
</para>
<para>
@@ -23881,21 +23880,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
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 <token>IN</token> is <quote>false</quote> 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 <token>IN</token> is <quote>true</quote> if any equal subquery row is found.
- The result is <quote>false</quote> if no equal row is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any equal subquery row is found.
+ The result is <quote>false</quote> if no equal row is found.
</para>
<para>
- 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 <token>IN</token> is null.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are OR'd together.
</para>
</sect2>
@@ -23907,20 +23903,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</synopsis>
<para>
- 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 <token>NOT IN</token> is <quote>true</quote> 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 <token>NOT IN</token> is <quote>true</quote> 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 <quote>true</quote> if only unequal subquery rows are found.
The result is <quote>false</quote> if any equal row is found.
</para>
<para>
- 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 <token>NOT IN</token> construct will be null, not true.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple inequality
+ tests are OR'd together.
</para>
<para>
@@ -23937,21 +23930,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
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 <token>NOT IN</token> is <quote>true</quote> 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 <token>NOT IN</token> is <quote>true</quote> if only unequal subquery rows
- are found (including the case where the subquery returns no rows).
+ The result is <quote>true</quote> if only unequal subquery rows are found.
The result is <quote>false</quote> if any equal row is found.
</para>
<para>
- 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 <token>NOT IN</token> is null.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple inequality
+ tests are OR'd together.
</para>
</sect2>
@@ -23965,13 +23955,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
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 <token>ANY</token> is
+ <quote>false</quote> 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 <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ANY</token> is <quote>true</quote> if any true result is obtained.
- The result is <quote>false</quote> if no true result is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any true result is obtained.
+ The result is <quote>false</quote> if no true result is found.
</para>
<para>
@@ -23980,11 +23970,10 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</para>
<para>
- 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 <token>ANY</token> construct
- will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -24002,16 +23991,19 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
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 <token>ANY</token> is
+ <quote>false</quote> 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 <replaceable>operator</replaceable>.
- The result of <token>ANY</token> is <quote>true</quote> if the comparison
- returns true for any subquery row.
- The result is <quote>false</quote> 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 <quote>true</quote> if the comparison returns true for any subquery row.
+ The result is <quote>false</quote> if the comparison returns false for every subquery row.
+ </para>
+
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are OR'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -24029,15 +24021,20 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
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 <token>ALL</token> is
+ <quote>true</quote> 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 <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ALL</token> is <quote>true</quote> if all rows yield true
- (including the case where the subquery returns no rows).
+ The result is <quote>true</quote> if all rows yield true.
The result is <quote>false</quote> 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.
+ </para>
+
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -24058,22 +24055,21 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
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 <token>ALL</token> is
+ <quote>true</quote> 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 <replaceable>operator</replaceable>.
- The result of <token>ALL</token> is <quote>true</quote> if the comparison
- returns true for all subquery rows (including the
- case where the subquery returns no rows).
- The result is <quote>false</quote> 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 <quote>true</quote> if the comparison returns true for all subquery rows.
+ The result is <quote>false</quote> if the comparison returns false for any subquery row.
</para>
<para>
- See <xref linkend="row-wise-comparison"/> for details about the meaning
- of a row constructor comparison.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
+
</sect2>
<sect2 id="functions-subquery-single-row-comp">
@@ -24098,6 +24094,14 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
compared row-wise to the single subquery result row.
</para>
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, the result cannot be <quote>true</quote> 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 <literal>IS DISTINCT FROM</literal> is not an operator.
+ </para>
+
<para>
See <xref linkend="row-wise-comparison"/> for details about the meaning
of a row constructor comparison.
@@ -24165,7 +24169,8 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<productname>PostgreSQL</productname> extensions; the rest are
<acronym>SQL</acronym>-compliant.
All of the expression forms documented in this section return
- Boolean (true/false) results.
+ <link linkend="nullvalues">three-valued</link> boolean typed
+ results (true, false, or null).
</para>
<sect2 id="functions-comparisons-in-scalar">
@@ -24178,24 +24183,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized list
of expressions. The result is <quote>true</quote> if the left-hand expression's
- result is equal to any of the right-hand expressions. This is a shorthand
- notation for
-
-<synopsis>
-<replaceable>expression</replaceable> = <replaceable>value1</replaceable>
-OR
-<replaceable>expression</replaceable> = <replaceable>value2</replaceable>
-OR
-...
-</synopsis>
+ result is equal to any of the right-hand expressions.
</para>
<para>
- 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 <token>IN</token> construct will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence null values since the multiple equality
+ tests are OR'd together.
</para>
</sect2>
@@ -24209,35 +24203,15 @@ OR
<para>
The right-hand side is a parenthesized list
of expressions. The result is <quote>true</quote> if the left-hand expression's
- result is unequal to all of the right-hand expressions. This is a shorthand
- notation for
-
-<synopsis>
-<replaceable>expression</replaceable> <> <replaceable>value1</replaceable>
-AND
-<replaceable>expression</replaceable> <> <replaceable>value2</replaceable>
-AND
-...
-</synopsis>
+ result is unequal to all of the right-hand expressions.
</para>
<para>
- 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 <token>NOT IN</token> 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 <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of null values since the multiple inequality
+ tests are OR'd together.
</para>
- <tip>
- <para>
- <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
- cases. However, null values are much more likely to trip up the novice when
- working with <token>NOT IN</token> than when working with <token>IN</token>.
- It is best to express your condition positively if possible.
- </para>
- </tip>
</sect2>
<sect2 id="functions-comparisons-any-some">
@@ -24250,30 +24224,26 @@ AND
<para>
The right-hand side is a parenthesized expression, which must yield an
- array value.
- The left-hand expression
+ array value. The result of <token>ANY</token> is
+ <quote>false</quote> if the array has zero element, otherwise
+ the left-hand expression
is evaluated and compared to each element of the array using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ANY</token> is <quote>true</quote> if any true result is obtained.
- The result is <quote>false</quote> if no true result is found (including the
- case where the array has zero elements).
+ The result is <quote>true</quote> if any true result is obtained.
+ The result is <quote>false</quote> if no true result is found.
</para>
<para>
- If the array expression yields a null array, the result of
- <token>ANY</token> will be null. If the left-hand expression yields null,
- the result of <token>ANY</token> 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 <token>ANY</token>
- 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 <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both elements and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
<token>SOME</token> is a synonym for <token>ANY</token>.
+ <token>IN</token> is equivalent to <literal>= ANY</literal>.
</para>
</sect2>
@@ -24287,26 +24257,27 @@ AND
<para>
The right-hand side is a parenthesized expression, which must yield an
array value.
- The left-hand expression
+ The result of <token>ALL</token> is
+ <quote>true</quote> if the array has zero elements, otherwise
+ the left-hand expression
is evaluated and compared to each element of the array using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ALL</token> is <quote>true</quote> if all comparisons yield true
- (including the case where the array has zero elements).
+ The result is <quote>true</quote> if all comparisons yield true.
The result is <quote>false</quote> if any false result is found.
</para>
<para>
- If the array expression yields a null array, the result of
- <token>ALL</token> will be null. If the left-hand expression yields null,
- the result of <token>ALL</token> 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 <token>ALL</token>
- 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 <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both elements and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
+
+ <para>
+ <token>NOT IN</token> is equivalent to <literal><> ALL</literal>.
+ </para>
+
</sect2>
<sect2 id="row-wise-comparison">
@@ -24357,6 +24328,11 @@ AND
considered.
</para>
+ <para>
+ See <xref linkend="nullvalues-multielementcomparison-rowconstructor"/>
+ and surrounding content for additional details and examples.
+ </para>
+
<synopsis>
<replaceable>row_constructor</replaceable> IS DISTINCT FROM <replaceable>row_constructor</replaceable>
</synopsis>
@@ -24391,20 +24367,11 @@ AND
</synopsis>
<para>
- 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.
- <productname>PostgreSQL</productname> does this only when comparing the
- results of two row constructors (as in
- <xref linkend="row-wise-comparison"/>) or comparing a row constructor
- to the output of a subquery (as in <xref linkend="functions-subquery"/>).
- 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.
- </para>
-
- <para>
- Each side is evaluated and they are compared row-wise. Composite type
+ Each side is evaluated and they are compared row-wise.
+ As discussed and shown in <xref linkend="nullvalues-multielementcomparison-composite"/>,
+ 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 <replaceable>operator</replaceable> is
<literal>=</literal>,
<literal><></literal>,
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 206eadb8f7b..0f39d4bf21a 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -129,6 +129,11 @@
the corresponding <productname>PostgreSQL</productname> types.
</para>
+ <indexterm>
+ <primary>null value</primary>
+ <secondary sortas="json">within JSON</secondary>
+ </indexterm>
+
<table id="json-type-mapping-table">
<title>JSON Primitive Types and Corresponding <productname>PostgreSQL</productname> Types</title>
<tgroup cols="3">
@@ -162,7 +167,7 @@
<row>
<entry><type>null</type></entry>
<entry>(none)</entry>
- <entry>SQL <literal>NULL</literal> is a different concept</entry>
+ <entry>An SQL null value is similar, but see <xref linkend="nullvalues-json"/> for differences.</entry>
</row>
</tbody>
</tgroup>
diff --git a/doc/src/sgml/nullvalues.sgml b/doc/src/sgml/nullvalues.sgml
new file mode 100644
index 00000000000..53063f598ee
--- /dev/null
+++ b/doc/src/sgml/nullvalues.sgml
@@ -0,0 +1,1126 @@
+<!-- doc/src/sgml/nullvalues.sgml -->
+
+<sect1 id="nullvalues">
+ <title>Null Values Overview</title>
+
+ <indexterm>
+ <primary>null value</primary>
+ </indexterm>
+
+ <para>
+ This section first introduces the concept of null values and then
+ explains 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.
+ </para>
+
+ <para>
+ Throughout this section, the discussion of null values will be limited to
+ the SQL language unless otherwise noted. The JSON-related data types, and the
+ non-SQL procedural languages, have their own behaviors documented in their
+ respective areas.
+ </para>
+
+ <para>
+ The following <literal>CREATE TABLE</literal> and <literal>INSERT</literal>
+ SQL commands can be executed in any SQL client to create and populate
+ the persistent table used in the examples below. The <literal>\pset</literal>
+ commands require the use of <application>psql</application> as the client program;
+ they make the resulting output a bit easier to read and do not impact any behaviors
+ described herein. Note, the examples below have been manually edited to show
+ <literal>true</literal> and <literal>false</literal> instead of
+ <literal>t</literal> and <literal>f</literal>. They also omit any transactional
+ command output when transactions are used. Instead, each transaction gets its own
+ display block.
+ </para>
+
+<programlisting>
+CREATE TABLE null_examples (
+ id bigint PRIMARY KEY,
+ value integer NULL
+);
+INSERT INTO null_examples
+VALUES (1, 1), (2, NULL), (3, 4);
+
+-- This makes null values print as \N in the output instead of the empty string.
+\pset null '\\N'
+-- Removes the row count footer that prints by default.
+\pset footer off
+</programlisting>
+
+ <sect2 id="nullvalues-model">
+ <title>Meaning</title>
+ <para>
+ 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.
+ </para>
+ <para>
+ In different programming languages, some of which are accessible in the server
+ as procedural languages, the null value is represented in other ways.
+ Of those included in <productname>PostgreSQL</productname>,
+ these are:
+ <literal>None</literal> in <productname>Python</productname>,
+ <literal>undefined</literal> in <productname>Perl</productname>,
+ and the empty string in <productname>TCL</productname>.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-usage">
+ <title>Usage</title>
+ <para>
+ A null value, like all values, must have a data type, and is valid for all data types.
+ It must also be printed as text. This section discusses null values at the boundaries
+ of the system, as well as how they can come into existence due to the design of a query.
+ </para>
+ <sect3 id="nullvalues-usage-input">
+ <title>Null Value Input</title>
+ <para>
+ A null value can be used as input to any function, operator, or expression.
+ The system will then decide how to behave based on the rules described in the
+ rest of this section.
+ </para>
+ <para>
+ As noted in <xref linkend="sql-syntax-constants-nullvalue"/>,
+ a null value literal is written using the <literal>NULL</literal> keyword.
+ Its type is the <link linkend="datatype-pseudo">pseudo-type unknown</link>
+ but can be cast to any concrete data type.
+ </para>
+ <para>
+<programlisting>
+SELECT
+ NULL AS "Literal Null Value",
+ pg_typeof(null) AS "Type of Null",
+ pg_typeof(NULL::text) AS "Type of Cast Null",
+ cast(null as text) AS "Cast Null Value";
+</programlisting>
+<screen>
+ Literal Null Value | Type of Null | Type of Cast Null | Cast Null Value
+--------------------+--------------+-------------------+-----------------
+ \N | unknown | text | \N
+</screen>
+ </para>
+ <para>
+<programlisting>
+SELECT text NULL;
+</programlisting>
+<screen>
+ERROR: column "text" does not exist
+LINE 1: select text NUll;
+</screen>
+ </para>
+ <para>
+ The <link linkend="sql-copy"><command>COPY ... FROM</command></link> command,
+ including its psql counter-part meta-command
+ <link linkend="app-psql-meta-commands-copy"><command>\copy</command></link>,
+ must deal with input files containing textual representations of the null value.
+ The lack of consistency in real-world data requires having a few options to the
+ command related to null handling. See the documentation in
+ <xref linkend="sql-copy"/> for more information.
+ But, in short, for CSV input it expects text to be quoted and interprets an unquoted
+ empty string as the null value.
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-usage-tables">
+ <title>Null Values in Tables</title>
+ <para>
+ From a semantics perspective a table treats a null value like any other value.
+ However, the SQL Language recognizes its uniqueness by defining a column-scoped
+ <link linkend="nullvalues-table-constraints"><literal>NOT NULL</literal> constraint</link>
+ as syntactic sugar. At present one would expect that a column having a
+ domain data type with a <literal>NOT NULL</literal> constraint would likewise be
+ incapable of having a null value stored. This is not the case. See the commentary
+ below in <xref linkend="nullvalues-domains"/> for more information.
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-usage-derived">
+ <title>Derived Null Values</title>
+ <para>
+ Even if all data stored in tables are known to be non-null, null values can still
+ be produced while executing a query. The most common way this happens is by
+ introducing a (left) outer join to the query and having left side data without
+ corresponding data on the right side of the join.
+<programlisting>
+SELECT
+ countries.country,
+ flagships.flagship
+FROM (
+ VALUES ('Spain'), ('Switzerland')
+) as countries (country)
+LEFT JOIN (
+ VALUES ('Spain', 'Ship')
+) as flagships (country, flagship)
+ON countries.country = flagships.country;
+</programlisting>
+<screen>
+ country | flagship
+-------------+----------
+ Spain | Ship
+ Switzerland | \N
+</screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-usage-output">
+ <title>Null Value Output</title>
+ <para>
+ As evidenced above, the "absence of value" aspect of the null value results
+ in its secondary textual representation being an empty string
+ (its primary representation is just NULL).
+ This can be problematic if the empty string is expected to be a valid value.
+ Therefore, places that deal with possible null values as input and text as
+ output need some means to give the user a way to specify how to print
+ the null value.
+ </para>
+ <para>
+ Generally, the primary representation is used when the value is part of a multi-element value.
+ If the value is being displayed by itself the secondary (blank) representation is used. The settings
+ discussed herein typically control the secondary representation. The null value representation when it
+ is within a container type (composite, array, etc...) is controlled by the input and output rules of the
+ container type. It is when the container value itself is the null value that these generalities then apply.
+ </para>
+ <para>
+ No matter how the null value got into the result when presenting results to the user it is
+ necessary to present null values using text. This is the responsibility of the client application.
+ The <command>psql</command> client program has the <link linkend="app-psql-meta-command-pset-null">
+ <literal>\pset null</literal> meta-command</link> to specify the textual output of null values
+ it encounters in query results.
+ </para>
+ <para>
+ When the final output of the result is a text file instead of a user additional
+ considerations come into play. While the option to take the user presentation
+ and send it to a text file always exists <productname>PostgreSQL</productname> also
+ provides a facility to output a structured text file.
+ The <link linkend="sql-copy"><command>COPY ... TO</command></link> command,
+ including its psql counter-part meta-command
+ <link linkend="app-psql-meta-commands-copy"><command>\copy</command></link>,
+ has the <literal>NULL</literal> option (and some modifier options) to specify
+ the string to print to the output for null values it encounters in the query result.
+ As with input file processing, for the CSV format it will, by default,
+ produce an unquoted empty string for the null value.
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-usage-handling">
+ <title>Null Value Handling</title>
+ <para>
+ The presence of null values in the system results in three-valued logic.
+ In conventional two-valued (binary) logic every outcome is either true or false.
+ In three-valued logic the concept of unknown, represented using the null value, is
+ also an outcome. This results in falsifying the common-sense notion
+ that "p OR NOT p" is always true.
+<programlisting>
+SELECT
+ NULL OR NOT NULL AS "N OR !N";
+</programlisting>
+<screen>
+ N OR !N
+---------
+ \N
+</screen>
+ (See <xref linkend="nullvalues-operands"/> for more explanation.)
+ </para>
+ <para>
+ When dealing with null values it is often useful to explicitly to convert
+ data to and from a null value given a known non-null representation
+ (e.g., the empty string, the numbers 0 or 1, or boolean false).
+ The <link linkend="functions-coalesce-nvl-ifnull">COALESCE</link> and
+ <link linkend="functions-nullif">NULLIF</link> functions are useful
+ for this purpose.
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="nullvalues-cardinalrule">
+ <title>Distinctness - Overcoming the Cardinal Rule of Null Values</title>
+ <para>
+ The cardinal rule, a null value is
+ <link linkend="functions-comparison-op-table">
+ neither equal nor unequal
+ </link>
+ to any value, including other null values.
+<programlisting>
+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";
+</programlisting>
+<screen>
+ N = N | N != N | 1 = N | 1 != N | 1 = 1 | 1 != 1
+-------+--------+-------+--------+-------+--------
+ \N | \N | \N | \N | true | false
+</screen>
+ However, as with many rules, there are exceptions, as noted in
+ <xref linkend="nullvalues-multielementcomparison"/>.
+ Particularly, when the two compared values are part of a larger multi-element value.
+<programlisting>
+SELECT
+ array[1,2]=array[1,null] AS "Array Equals";
+</programlisting>
+<screen>
+ Array Equals
+--------------
+ false
+</screen>
+ </para>
+ <para>
+ Because of this SQL standard rule, checking for a null value has an
+ explicit <literal>IS NULL</literal> predicate. Additionally, there are comparison
+ predicates that consider a null value equal to other null values but unequal
+ to any other value (e.g., <literal>IS DISTINCT</literal>, and <literal>IS TRUE</literal>.)
+ These, and other predicates, are described in
+ <xref linkend="functions-comparison-pred-table"/>
+<programlisting>
+SELECT id, value,
+ value IS NULL AS "IS NULL",
+ value IS DISTINCT FROM id AS "IS DIST",
+ value != id AS "IS !="
+FROM null_examples;
+</programlisting>
+<screen>
+ id | value | IS NULL | IS DIST | IS !=
+----+-------+---------+---------+-------
+ 1 | 1 | false | false | false
+ 2 | \N | true | true | \N
+ 3 | 4 | false | true | true
+</screen>
+ </para>
+ <para>
+ On the other hand, the SQL standard is largely alone in taking this approach to comparing
+ values to the null value. For example, when working within the JSON data types the use of equals
+ produces true or false and so the concept of distinctness is neither present nor required.
+ Additional details and links are provided in <xref linkend="nullvalues-json"/>.
+ For the non-SQL procedural languages, please consult the appropriate documentation.
+ </para>
+ <para>
+ There is also a cardinal warning: when dealing with
+ <link linkend="rowtypes">composite types</link> in
+ expressions, <literal>composite IS NULL</literal>
+ and <literal>composite IS NOT NULL</literal>
+ 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 <literal>NOT(composite IS NULL)</literal> instead.
+<programlisting>
+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;
+</programlisting>
+<screen>
+ 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) | false | true | true | false | true
+ (2,) | false | true | false | true | false
+ (3,4) | false | true | true | false | true
+</screen>
+ See <xref linkend="nullvalues-multielement"/> below for an explanation.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-operands">
+ <title>Null-Valued Operands</title>
+ <para>
+ As a general expectation, operator invocation expressions where one of the inputs
+ is a null value will result in a null-valued output.
+<programlisting>
+SELECT
+ 1 + null AS "Add",
+ 'text' || null AS "Concatenate";
+</programlisting>
+<screen>
+ Add | Concatenate
+-----+-------------
+ \N | \N
+</screen>
+ Operators that behave otherwise should document their deviation from this norm.
+ </para>
+ <para>
+ A notable example is the <literal>IN</literal> operator, which
+ uses equality, not distinctness, for testing.
+<programlisting>
+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";
+</programlisting>
+<screen>
+ In Present | In Missing | N In Non-N | N In N
+------------+------------+------------+--------
+ true | \N | \N | \N
+</screen>
+ This is just an extension of the multi-element testing behavior described in
+ <xref linkend="nullvalues-multielement"/>.
+ </para>
+ <para>
+ Experience shows that <literal>CASE</literal> expressions are also prone
+ to bugs since their format encourages binary logic thinking while a
+ <literal>WHEN</literal> test will not consider a null value to be a match.
+<programlisting>
+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;
+</programlisting>
+<screen>
+ id | value | Affirm | Negate | Safe Affirm | Safe Negate
+----+-------+-----------+-----------+-------------+-------------
+ 1 | 1 | Equal | Equal | Equal | Equal
+ 2 | \N | Not Equal | Equal | Null | Null
+ 3 | 4 | Not Equal | Not Equal | Not Equal | Not Equal
+</screen>
+ </para>
+ <para>
+ The boolean operators <literal>AND</literal> and <literal>OR</literal>
+ will ignore the null value input if the other input is sufficient to
+ determine the outcome.
+<programlisting>
+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";
+</programlisting>
+<screen>
+ T or N | F or N | T and N | F and N
+--------+--------+---------+---------
+ true | \N | \N | false
+</screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-domains">
+ <title>Null Values in Domains</title>
+ <para>
+ A domain is a user-defined data type that can have a <literal>NOT NULL</literal>
+ constraint. However, some usages of domains can still cause a column to be of the
+ domain type but some value may be null. The common way this happens is by including
+ the domain column's table on the right side of a left join.
+<programlisting>
+BEGIN;
+CREATE DOMAIN domain_example AS integer NOT NULL;
+CREATE TABLE domain_examples (de_id bigint PRIMARY KEY, de_value domain_example);
+INSERT INTO domain_examples VALUES (1, 1), (2, 2);
+SELECT *, pg_typeof(de_value)
+FROM null_examples AS ne
+LEFT JOIN domain_examples AS de ON ne.id = de.de_id;
+ROLLBACK;
+</programlisting>
+<screen>
+BEGIN
+CREATE DOMAIN
+CREATE TABLE
+INSERT 0 2
+ id | value | de_id | de_value | pg_typeof
+----+-------+-------+----------+----------------
+ 1 | 1 | 1 | 1 | domain_example
+ 2 | \N | 2 | 2 | domain_example
+ 3 | 4 | \N | \N | domain_example
+
+ROLLBACK
+</screen>
+ Please see the details in <xref linkend="sql-createdomain-notes"/>
+ for another example, as well as commentary on why this non-standard behavior exists.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-multielement">
+ <title>Testing Multi-Element Values with Null-Valued Elements</title>
+ <para>
+ Arrays and composite types are multi-element types. Here we also consider non-empty
+ <link linkend="functions-subquery">subquery results</link>
+ and the list of values (i.e., the multiset) specified in the
+ <link linkend="functions-comparisons-in-scalar">IN test</link>.
+ </para>
+ <para>
+ 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
+ <link linkend="row-wise-comparison">comparing two row constructors</link> to each other),
+ left-to-right, combining the results using the boolean operations described in
+ <xref linkend="nullvalues-operands"/>. For tests that
+ require an exhaustive search, (e.g., <literal>ALL</literal>, <literal>NOT IN</literal>)
+ the search effectively ends when a false result is found (<literal>AND</literal> combiners).
+ For tests that require a true result, (e.g., <literal>ANY</literal>,
+ <literal>IN</literal>) the search effectively ends when a true result is found
+ (<literal>OR</literal> combiners). Therefore:
+ <simplelist>
+ <member>
+ <literal>IN</literal> and <literal>ANY</literal>
+ (<literal>OR</literal>) cannot produce a false result in the presence of null, and
+ </member>
+ <member>
+ <literal>NOT IN</literal> and <literal>ALL</literal>
+ (<literal>AND</literal>) cannot produce a true result in the presence of null.
+ </member>
+ </simplelist>
+ This is because any exhaustive search will produce at least one null value result
+ that cannot be ignored.
+ </para>
+ <para>
+ The SQL standard requires that non-exhaustive
+ (i.e., <literal>IN</literal> and <literal>ANY</literal>) subquery tests
+ return false when there are no rows in the subquery result, and return true
+ for the exhaustive tests (i.e., <literal>NOT IN</literal> and <literal>ALL</literal>).
+ </para>
+ <para>
+ Note that the cardinal warning
+ noted in <xref linkend="nullvalues-cardinalrule"/> above is just the application of this behavior to the
+ <literal>IS NULL</literal> and <literal>IS NOT NULL</literal>
+ 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.
+ </para>
+ <para>
+ In the next section, the rules above are discussed.
+ <xref linkend="nullvalues-multielementpredicates"/>
+ discusses situations where a predicate or a scalar value
+ are being compared to a multi-element value.
+ In <xref linkend="nullvalues-multielementcomparison"/>
+ the rules when two multi-element values are compared
+ to each other are discussed
+ (including the two row constructor comparison case.)
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-multielementpredicates">
+ <title>Multi-Element Predicates and Scalars</title>
+ <sect3 id="nullvalues-multielementpredicates-composites">
+ <title>Composite Fields</title>
+ <para>
+ When a composite typed value is created, a null value can be assigned to any
+ of its fields (see <xref linkend="rowtypes-constructing"/> for how to do this).
+ So long as at least one field is non-null the composite value
+ as a whole exists and an <literal>IS NULL</literal> predicate will return false.
+ </para>
+ <para>
+ Applying the <literal>IS NOT NULL</literal> predicate to a composite value performs
+ checks on 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 the <literal>IS NOT NULL</literal> predicate and the
+ <literal>IS NULL</literal> predicate will return false.
+<programlisting>
+SELECT
+ ROW(1,2) IS NULL AS "Row Is Null",
+ ROW(1,2) IS NOT NULL AS "Row Is Not Null",
+ ROW(1,NULL) IS NULL AS "Row Is Null",
+ ROW(1,NULL) IS NOT NULL AS "Row Is Not Null";
+</programlisting>
+<screen>
+ Row Is Null | Row Is Not Null | Row Is Null | Row Is Not Null
+-------------+-----------------+-------------+-----------------
+ false | true | false | false
+</screen>
+ </para>
+ <para>
+ Please read <xref linkend="composite-type-comparison"/> for a complete treatment
+ on how <productname>PostgreSQL</productname> handles row-wise comparison. The
+ next two multi-element related items in this section discuss those comparisons in the
+ presence of null-valued fields, and also in terms of the SQL standard.
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementpredicates-arrays">
+ <title>Array Elements and IN Multiset Members</title>
+ <para>
+ Examples of applying the behavior discussed in <xref linkend="nullvalues-multielement"/>
+ to arrays, and <literal>IN</literal> and <literal>NOT IN</literal> multisets, using the
+ operators defined in <xref linkend="functions-comparisons"/>. The following examples produce
+ the same results when swapping <literal>IN</literal>/<literal>ANY</literal>
+ and also <literal>NOT IN</literal>/<literal>ALL</literal>, plus transforming the multiset/array format.
+ I.e., the exhaustive and non-exhaustive pairs noted in <xref linkend="nullvalues-multielement"/>.
+ </para>
+ <para>
+<programlisting>
+SELECT
+ 1 = ANY(array[1, 1, NULL]) AS "Any-Null-Match",
+ 1 = ANY(array[1, 1]) AS "Any-NoNull-Match",
+ 1 = ALL(array[1, 1, NULL]) AS "ALL-Null-Match",
+ 1 = ALL(array[1, 1]) AS "All-NoNull-Match";
+SELECT
+ 2 IN (1, 1, NULL) AS "IN-Null-Negative",
+ 2 IN (1, 1) AS "IN-NoNull-Negative",
+ 2 NOT IN (2, 2, NULL) AS "NotIN-Null-Negative",
+ 2 NOT IN (2, 2) AS "NotIN-NoNull-Negative";
+</programlisting>
+<screen>
+ Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
+----------------+------------------+----------------+------------------
+ true | true | \N | true
+
+ IN-Null-Negative | IN-NoNull-Negative | NotIN-Null-Negative | NotIN-NoNull-Negative
+------------------+--------------------+---------------------+-----------------------
+ \N | false | false | false
+</screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementpredicates-subqueries">
+ <title>Single-Column Subquery Rows</title>
+ <para>
+ The following examples demonstrate the behavior discussed in
+ <xref linkend="nullvalues-multielement"/>
+ applied to subqueries using the operators defined in <xref linkend="functions-subquery"/>.
+ Here we cover the case where the multiple elements being checked are rows, each having one column.
+ If the column itself is multi-element then the thing being searched for must be a compatible
+ multi-element value, and the corresponding comparison behavior described in
+ <xref linkend="nullvalues-multielementcomparison"/> will also be applied.
+ </para>
+ <para>
+<programlisting>
+SELECT
+ 1 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-Match",
+ 1 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-Match",
+ 1 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-Match",
+ 1 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-Match";
+SELECT
+ 2 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-NoMatch",
+ 2 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-NoMatch",
+ 2 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-NoMatch",
+ 2 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-NoMatch";
+</programlisting>
+<screen>
+ Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
+----------------+------------------+----------------+------------------
+ true | true | \N | true
+
+ Any-Null-NoMatch | Any-NoNull-NoMatch | ALL-Null-NoMatch | All-NoNull-NoMatch
+------------------+--------------------+------------------+--------------------
+ \N | false | false | false
+</screen>
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="nullvalues-multielementcomparison">
+ <title>Multi-Element Comparisons</title>
+ <para>
+ The previous section, <xref linkend="nullvalues-multielementpredicates"/>, discussed applying
+ a predicate or a scalar value check element-wise across a multi-element value.
+ This section moves the discussion over to 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 standard 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.
+ </para>
+ <para>
+ 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
+ <xref linkend="composite-type-comparison"/> for a fuller treatment of this topic. Here
+ we briefly recap the five situations in the presence of null values.
+ </para>
+ <sect3 id="nullvalues-multielementcomparison-array">
+ <title>Element-wise Comparisons</title>
+ <para>
+ In this first situation, null values within an array compare as equal to each other and greater
+ than all non-null values, regardless of whether the comparison involves
+ <link linkend="sql-syntax-array-constructors">array constructors</link> or array-typed values.
+<programlisting>
+SELECT
+ array[1,2]=array[1,null] AS "Constructors",
+ s, t,
+ s = t AS "Stored Equality",
+ t > s AS "Stored Ordering"
+FROM
+(values (array[1,2])) AS sv (s),
+(values (array[1,null::integer])) AS st (t);
+</programlisting>
+<screen>
+ Constructors | s | t | Stored Equality | Stored Ordering
+--------------+-------+----------+-----------------+-----------------
+ false | {1,2} | {1,NULL} | false | true
+</screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-rowconstructor">
+ <title>Row-wise Mutual Row Constructor Comparisons</title>
+ <para>
+ In this situation, null values produce unknown when compared to all values.
+<programlisting>
+SELECT
+ (1,2)=(1,null) AS "NonNull=Null",
+ (1,null::integer)=(1,null) AS "Null=Null";
+</programlisting>
+<screen>
+ NonNull=Null | Null=Null
+--------------+-----------
+ \N | \N
+</screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-composite">
+ <title>Row-wise Composite Involved Comparisons</title>
+ <para>
+ In these three situations, null values are considered equal to each other and greater than
+ all non-null valueS.
+ </para>
+<programlisting>
+SELECT s, t,
+ s = t AS "Stored Equals Stored",
+ t < (1,2) AS "Stored LT Constructor",
+ t = (1,null::integer) AS "Stored Equals Constructor"
+FROM
+ (values (1,2)) AS s,
+ (values (1,null::integer)) AS t;
+</programlisting>
+<screen>
+ s | t | Stored Equals Stored | Stored LT Constructor | Stored Equals Constructor
+-------+------+----------------------+-----------------------+---------------------------
+ (1,2) | (1,) | false | false | true
+</screen>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-sqlconformance">
+ <title>SQL Standard Conformance</title>
+ <para>
+ The SQL standard requires row-wise comparison to return NULL if the
+ result depends on comparing two NULL values or a NULL and a non-NULL.
+ <productname>PostgreSQL</productname> does this only when comparing the
+ results of two row constructors (as in
+ <xref linkend="row-wise-comparison"/>) or comparing a row constructor
+ to the output of a subquery (as in <xref linkend="functions-subquery"/>).
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="nullvalues-functions">
+ <title>Null-Valued Arguments in Normal Function Calls</title>
+ <para>
+ <link linkend="sql-createfunction">Function specifications</link>
+ have a "strictness" attribute (<literal>pg_proc.proisstrict</literal>) that,
+ when set to "strict" (true) will tell the executor to return a null value for any
+ function call having at least one null-valued input, without executing the
+ function.
+ </para>
+ <para>
+ 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 <link linkend="xfunc-sql-variadic-functions">variadic functions</link>
+ like concatenate, null values often are 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.
+<programlisting>
+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[]";
+</programlisting>
+<screen>
+ Lower | Left | || Text Op | concat Text Func | append([], null) | [] || null | [] || null[]
+-------+------+------------+------------------+------------------+------------+--------------
+ \N | \N | \N | one | {1,NULL} | {1,NULL} | {1}
+</screen>
+ 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.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-aggregates">
+ <title>Null-Valued Arguments in Aggregate and Window Functions</title>
+ <para>
+ When executing an aggregate or window function the state tracking component
+ (which may be initialized to a non-null value, e.g., 0 for the count function)
+ will remain unchanged even if the underlying processing
+ function returns a null value, whether from being defined strict
+ or it returns a null value upon execution. The aggregation
+ routine will usually ignore the null value and continue processing,
+ as demonstrated in <literal>count(value)</literal> below.
+<programlisting>
+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;
+</programlisting>
+<screen>
+ Count | Count Value | Count Composite | Count Row
+-------+-------------+-----------------+-----------
+ 3 | 2 | 3 | 3
+</screen>
+ 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
+ <link linkend="sql-syntax-row-constructors">row constructor</link>
+ <literal>row(null, null)</literal>.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-filtering">
+ <title>Null Values When Filtering</title>
+ <para>
+ A <literal>WHERE</literal> clause that evaluates to a null value for a given row will exclude that row.
+ Note below that, due to tri-valued logic described in <xref linkend="nullvalues-cardinalrule"/>,
+ the row with an id of 2 is not included in either of the first two results. The third result, using
+ <literal>IS NULL</literal>, finds that row.
+<programlisting>
+SELECT id, value AS "Equals 1"
+FROM null_examples
+WHERE value = 1;
+
+SELECT id, value AS "Not Equal to 1"
+FROM null_examples
+WHERE value != 1;
+
+SELECT id, value AS "IS NULL"
+FROM null_examples
+WHERE value IS NULL;
+</programlisting>
+<screen>
+ id | Equals 1
+----+----------
+ 1 | 1
+
+ id | Not Equal to 1
+----+----------------
+ 3 | 4
+
+ id | IS NULL
+----+---------
+ 2 | \N
+</screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-table-constraints">
+ <title>Null Values in Table Constraints</title>
+ <para>
+ It is possible to define
+ <link linkend="ddl-constraints-check-constraints">check constraint</link>
+ expressions on tables to ensure only values passing those expressions are inserted.
+ While this seems like it would behave the same as a where clause, the choice here,
+ when an expression evaluates to a null value, is to allow the row to be inserted
+ - the same as a true result.
+<programlisting>
+BEGIN;
+ALTER TABLE null_examples ADD CONSTRAINT value_not_1 CHECK (value != 1);
+ROLLBACK;
+</programlisting>
+<screen>
+ERROR: check constraint "value_not_1" of relation "null_examples" is violated by some row
+</screen>
+<programlisting>
+BEGIN;
+ALTER TABLE null_examples ADD CONSTRAINT value_not_10 CHECK (value != 10);
+ROLLBACK;
+</programlisting>
+<screen>
+ALTER TABLE
+</screen>
+ We are using a transaction (<command>BEGIN</command> and <command>ROLLBACK</command>) and
+ the <command>ALTER TABLE</command> 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.
+ </para>
+ <para>
+ The <link linkend="ddl-constraints-not-null"><literal>NOT NULL</literal> column constraint</link>
+ produces the same answer as a <literal>column IS NOT NULL</literal> check constraint but is
+ more concise to write.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-grouping">
+ <title>Null Values When Grouping</title>
+ <para>
+ In the context of both <literal>DISTINCT</literal> and <literal>GROUP BY</literal>
+ it is necessary that all inputs resolve to being either equal to or not equal to all
+ other values. These features use <link linkend="nullvalues-cardinalrule">distinctness</link>
+ instead of simple equality in order to handle a null value like a definite value equal to
+ another null value and unequal to all other values.
+<programlisting>
+WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+SELECT
+ value,
+ count(*) AS "Count"
+FROM vals
+GROUP BY value
+ORDER BY value;
+</programlisting>
+<screen>
+ value | Count
+-------+-------
+ 1 | 2
+ 2 | 1
+ \N | 2
+</screen>
+<programlisting>
+WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+SELECT DISTINCT value
+FROM vals
+ORDER BY value NULLS FIRST;
+</programlisting>
+<screen>
+ value
+-------
+ \N
+ 1
+ 2
+</screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-ordering">
+ <title>Null Values When Ordering</title>
+ <para>
+ In the context of <literal>ORDER BY</literal>, 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 <literal>ORDER BY</literal> clause will let you specify either
+ <literal>NULLS FIRST</literal> or <literal>NULLS LAST</literal>.
+<programlisting>
+WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+SELECT value FROM vals
+ORDER BY value DESC NULLS FIRST;
+</programlisting>
+<screen>
+ value
+-------
+ \N
+ \N
+ 2
+ 1
+ 1
+</screen>
+ </para>
+ <para>
+ Note that when dealing with multi-element values the comparison behavior described in
+ <xref linkend="nullvalues-multielementcomparison"/> 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.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-indexed">
+ <title>Null Values in Indexes</title>
+ <para>
+ The uniqueness and relative ordering rules applied to null values
+ are defined when creating an index. For the default
+ <literal>NULLS DISTINCT</literal> uniqueness, equality rules are applied.
+ Specifying <literal>NULLS NOT DISTINCT</literal> will result in
+ <literal>IS DISTINCT FROM</literal> rules being applied whereby all null
+ values are equal to each other. This setting applies to all columns in the index.
+ </para>
+ <para>
+<programlisting>
+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;
+</programlisting>
+<screen>
+CREATE INDEX
+CREATE INDEX
+INSERT 0 1
+</screen>
+<programlisting>
+BEGIN;
+CREATE UNIQUE INDEX value_nulls_not_distinct_explicit ON null_examples (value) NULLS NOT DISTINCT;
+INSERT INTO null_examples VALUES (4, NULL);
+ROLLBACK;
+</programlisting>
+<screen>
+CREATE INDEX
+ERROR: duplicate key value violates unique constraint "value_nulls_not_distinct_explicit"
+DETAIL: Key (value)=(null) already exists.
+</screen>
+ </para>
+ <para>
+ For ordering, each column in the index gets its own specification of
+ direction and null value placement similar to that found in the
+ <literal>ORDER BY</literal> clause.
+ </para>
+ <para>
+ Note that when dealing with multi-element values the comparison behavior described in
+ <xref linkend="nullvalues-multielementcomparison"/> 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.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-partitionkeys">
+ <title>Null Values in Partition Keys</title>
+ <para>
+ Presently, <productname>PostgreSQL</productname> requires that all the columns of a
+ partition key be included in the primary key. Furthermore, all columns used in a primary
+ key must have a not-null column constraint applied to them. Therefore, any partitioned table
+ with a primary key will only have non-null values in the partition key columns.
+ </para>
+ <para>
+ However, should you set up a situation where a partition key column can both: have a null value
+ and, null values in that key go to a specific partition, list-based routing will work as expected.
+ There is presently no way to direct rows having null values in partition keys away from the
+ default partition for range and hash partitioning.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-settings">
+ <title>Null-Valued Settings</title>
+ <para>
+ The value of a setting known to the system will never be null. There is a bit of confusion
+ because the <function>current_setting</function> function has an operating mode where instead
+ of provoking an error when retrieving the value of a setting not known to the system it will
+ instead return a null value. This null value should not be considered the value of the setting
+ but an error indicator.
+<programlisting>
+SELECT current_setting('example.string', false);
+SELECT current_setting('example.string', true);
+</programlisting>
+<screen>
+unrecognized configuration parameter "example.string"
+ current_setting
+-----------------
+ \N
+</screen>
+ The next paragraph discusses the corner case behavior when this
+ suggestion is not heeded.
+ </para>
+ <para>
+ The corner case mentioned above is only meaningful for
+ <link linkend="runtime-config-custom">custom settings</link>,
+ thus this section focuses on <link linkend="config-setting-sql">SQL interaction</link>.
+ Unlike settings created by extensions, custom settings can only be textual and the default
+ value for text here is the empty string.
+<programlisting>
+-- The transaction markers are left here to emphasize the rollback behavior.
+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;
+</programlisting>
+<screen>
+ERROR: unrecognized configuration parameter "example.string"
+BEGIN
+ set_config
+------------
+
+
+ Setting Is Null
+-----------------
+ false
+
+ROLLBACK
+ example.string
+----------------
+
+
+RESET
+ example.string
+----------------
+
+</screen>
+ Notice two important behaviors: first, even though we passed in a null value to
+ the <literal>set_config</literal> function, the <literal>current_setting</literal>
+ 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.)
+ </para>
+ <para>
+ The other ways to specify settings do allow for null values;
+ a specific non-null value is required as part of the setting specification.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-json">
+ <title>Null Values in JSON</title>
+ <para>
+ As noted in <xref linkend="json-type-mapping-table"/>, the JSON specification's
+ null value is assigned its own type having a single constant value which can be
+ compared to all other JSON types with the expected non-null boolean result.
+ A consequence of this definition is that an SQL json or jsonb type containing
+ a JSON null value is seen as non-null in SQL.
+ (Note, while in SQL the capitalization of NULL is unimportant -
+ all-caps is just convention - JSON requires lowercase.)
+<programlisting>
+SELECT 'null'::json IS NULL AS "JSON null is NULL";
+</programlisting>
+<screen>
+ JSON null is NULL
+-------------------
+ false
+</screen>
+ Additionally, the SQL operators and functions involving JSON key or array element selection,
+ or construction from literals, require that a valid number or text value be supplied as an operand
+ and so an SQL null value cannot be targeted by those operators and functions.
+<programlisting>
+ SELECT to_json(null::text);
+</programlisting>
+<screen>
+ to_json
+---------
+ \N
+</screen>
+ That all said, the system will convert an SQL null value to a JSON null value when in a
+ composite type context.
+<programlisting>
+SELECT json_build_object('value', value)
+FROM null_examples;
+</programlisting>
+<screen>
+ json_build_object
+-------------------
+ {"value" : 1}
+ {"value" : null}
+ {"value" : 4}
+</screen>
+ And vice versa.
+<programlisting>
+SELECT *
+FROM jsonb_to_recordset('[{"value":1},{"value":null},{"value":4}]'::jsonb) AS jtr (value integer);
+</programlisting>
+<screen>
+ value
+-------
+ 1
+ \N
+ 4
+</screen>
+ Or when a simple scalar JSON null is cast to an SQL type.
+<programlisting>
+SELECT 'null'::jsonb::numeric IS NULL AS "Cast jsonb NULL to SQL NULL";
+</programlisting>
+<screen>
+ Cast jsonb NULL to SQL NULL
+-----------------------------
+ true
+</screen>
+ </para>
+ <para>
+ Aspects of null value handling within the internals of the JSON-related types are discussed
+ in <xref linkend="datatype-json"/>,
+ particularly in <xref linkend="datatype-jsonpath"/>.
+ This section is focused on how SQL null values are related to JSON null values.
+ </para>
+ </sect2>
+</sect1>
diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml
index c111285a69c..0240f75f3cf 100644
--- a/doc/src/sgml/ref/create_domain.sgml
+++ b/doc/src/sgml/ref/create_domain.sgml
@@ -197,9 +197,10 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea
Domain constraints, particularly <literal>NOT NULL</literal>, are checked when
converting a value to the domain type. It is possible for a column that
is nominally of the domain type to read as null despite there being such
- a constraint. For example, this can happen in an outer-join query, if
- the domain column is on the nullable side of the outer join. A more
- subtle example is
+ a constraint. For example, this can happen in
+ <link linkend="nullvalues-domains">an outer-join query</link>, if
+ the domain column is on the nullable side of the outer join.
+ A more subtle example is
<programlisting>
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
</programlisting>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 916189a7d68..c41687b2244 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -281,9 +281,9 @@ U&"d!0061t!+000061" UESCAPE '!'
</indexterm>
<para>
- There are three kinds of <firstterm>implicitly-typed
+ There are four kinds of <firstterm>implicitly-typed
constants</firstterm> in <productname>PostgreSQL</productname>:
- strings, bit strings, and numbers.
+ strings, bit strings, numbers, and the null value.
Constants can also be specified with explicit types, which can
enable more accurate representation and more efficient handling by
the system. These alternatives are discussed in the following
@@ -834,6 +834,25 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
usage, as is the function-call syntax.
</para>
</sect3>
+
+ <sect3 id="sql-syntax-constants-nullvalue">
+ <title>The Null Value Constant</title>
+ <indexterm>
+ <primary>null value</primary>
+ <secondary>constant</secondary>
+ </indexterm>
+ <para>
+ The null value represents an unknown value and its constant, the keyword <literal>NULL</literal>,
+ when evaluated in an expression, likewise yields a value of <literal>unknown</literal> type.
+ See <xref linkend="nullvalues"/> for an overview of how the system behaves in the presence
+ of a null value in various contexts.
+ </para>
+ <para>
+ Due to the typing of a null value as <literal>unknown</literal> it is often necessary to use
+ a cast, as described in the previous section, to convert it to the specific type needed.
+ However, implicit casting is performed when contextual information is available.
+ </para>
+ </sect3>
</sect2>
<sect2 id="sql-syntax-operators">
--
2.34.1
