Document NULL

On Wed, May 1, 2024, 16:13 David G. Johnston <david.g.johnston@gmail.com>
wrote:

Hi,

Over in [1] it was rediscovered that our documentation assumes the reader
is familiar with NULL. It seems worthwhile to provide both an introduction
to the topic and an overview of how this special value gets handled
throughout the system.

Attached is a very rough draft attempting this, based on my own thoughts
and those expressed by Tom in [1], which largely align with mine.

I'll flesh this out some more once I get support for the goal, content,
and placement. On that point, NULL is a fundamental part of the SQL
language and so having it be a section in a Chapter titled "SQL Language"
seems to fit well, even if that falls into our tutorial. Framing this up
as tutorial content won't be that hard, though I've skipped on examples and
such pending feedback. It really doesn't fit as a top-level chapter under
part II nor really under any of the other chapters there. The main issue
with the tutorial is the forward references to concepts not yet discussed
but problem points there can be addressed.

I do plan to remove the entity reference and place the content into
query.sgml directly in the final version. It is just much easier to write
an entire new section in its own file.

David J.

[1]
/messages/by-id/1859814.1714532025@sss.pgh.pa.us

"The cardinal rule, NULL is never equal or unequal to any non-null value."

This implies that a NULL is generally equal or unequal to another NULL.
While this can be true (e.g. in aggregates), in general it is not. Perhaps
immediately follow it with something along the lines of "In most cases NULL
is also not considered equal or unequal to any other NULL (i.e. NULL = NULL
will return NULL), but there are occasional exceptions, which will be
explained further on."

Regards

Thom

On Wed, May 1, 2024 at 8:12 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

Hi,

Over in [1] it was rediscovered that our documentation assumes the reader
is familiar with NULL. It seems worthwhile to provide both an introduction
to the topic and an overview of how this special value gets handled
throughout the system.

Attached is a very rough draft attempting this, based on my own thoughts
and those expressed by Tom in [1], which largely align with mine.

I'll flesh this out some more once I get support for the goal, content,
and placement. On that point, NULL is a fundamental part of the SQL
language and so having it be a section in a Chapter titled "SQL Language"
seems to fit well, even if that falls into our tutorial. Framing this up
as tutorial content won't be that hard, though I've skipped on examples and
such pending feedback. It really doesn't fit as a top-level chapter under
part II nor really under any of the other chapters there. The main issue
with the tutorial is the forward references to concepts not yet discussed
but problem points there can be addressed.

I do plan to remove the entity reference and place the content into
query.sgml directly in the final version. It is just much easier to write
an entire new section in its own file.

Reviewed the documentation update and it's quite extensive, but I think
it's better to include some examples as well.

Regards
Kashif Zeeshan

On Thu, 2 May 2024 at 03:12, David G. Johnston
<david.g.johnston@gmail.com> wrote:

Attached is a very rough draft attempting this, based on my own thoughts and those expressed by Tom in [1], which largely align with mine.

Thanks for picking this up. I agree that we should have something to
improve this.

It would be good to see some subtitles in this e.g "Three-valued
boolean logic" and document about NULL being unknown, therefore false.
Giving a few examples would be good to, which I think is useful as it
at least demonstrates a simple way of testing these things using a
simple FROMless SELECT, e.g. "SELECT NULL = NULL;". You could link to
this section from where we document WHERE clauses.

Maybe another subtitle would be "GROUP BY / DISTINCT clauses with NULL
values", and then explain that including some other examples using
"SELECT 1 IS NOT DISTINCT FROM NULL;" to allow the reader to
experiment and learn by running queries.

You likely skipped them due to draft status, but if not, references
back to other sections likely could do with links back to that
section, e.g "amount of precipitation Hayward" is not on that page.
Without that you're assuming the reader is reading the documents
linearly.

Another section might briefly explain about disallowing NULLs in
columns with NOT NULL constraints, then link to wherever we properly
document those.

typo:

+ <title>Handling Unkowns (NULL)</title>

Maybe inject "Values" after Unknown.

Let's bash it into shape a bit more before going any further on actual wording.

David

David Rowley <dgrowleyml@gmail.com> writes:

Let's bash it into shape a bit more before going any further on actual wording.

FWIW, I want to push back on the idea of making it a tutorial section.
I too considered that, but in the end I think it's a better idea to
put it into the "main" docs, for two reasons:

1. I want this to be a fairly official/formal statement about how we
treat nulls; not that it has to be written in dry academic style or
whatever, but it has to be citable as The Reasons Why We Act Like That,
so the tutorial seems like the wrong place.

2. I think we'll soon be cross-referencing it from other places in the
docs, even if we don't actually move existing bits of text into it.
So again, cross-ref'ing the tutorial doesn't feel quite right.

Those arguments don't directly say where it should go, but after
surveying things a bit I think it could become section 5.2 in
ddl.sgml, between "Table Basics" and "Default Values". Another
angle could be to put it after "Default Values" --- except that
that section already assumes you know what a null is.

I've not read any of David's text in detail yet, but that's my
two cents on where to place it.

regards, tom lane

On Wed, May 1, 2024 at 9:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <dgrowleyml@gmail.com> writes:

Let's bash it into shape a bit more before going any further on actual

wording.

FWIW, I want to push back on the idea of making it a tutorial section.
I too considered that, but in the end I think it's a better idea to
put it into the "main" docs, for two reasons:

Version 2 attached. Still a draft, focused on topic picking and overall
structure. Examples and links planned plus the usual semantic markup stuff.

I chose to add a new sect1 in the user guide (The SQL Language) chapter,
"Data". Don't tell Robert.

The "Data Basics" sub-section lets us readily slide this Chapter into the
main flow and here the NULL discussion feels like a natural fit. In
hindsight, the lack of a Data chapter in a Database manual seems like an
oversight. One easily made because we assume if you are here you "know"
what data is, but there is still stuff to be discussed, if nothing else to
establish a common understanding between us and our users.

David J.

Hi David

I reviewed the documentation and it's very detailed.

Thanks
Kashif Zeeshan
Bitnine Global

On Thu, May 2, 2024 at 8:24 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Thu, 2024-05-02 at 08:23 -0700, David G. Johnston wrote:

Version 2 attached.  Still a draft, focused on topic picking and overall structure.

I'm fine with most of the material (ignoring ellipses and typos), except this:

+    The NOT NULL column constraint is largely syntax sugar for the corresponding
+    column IS NOT NULL check constraint, though there are metadata differences
+    described in create table.

I see a substantial difference there:

SELECT conname, contype,
pg_get_expr(conbin, 'not_null'::regclass)
FROM pg_constraint
WHERE conrelid = 'not_null'::regclass;

conname │ contype │ pg_get_expr
══════════════════════╪═════════╪══════════════════
check_null │ c │ (id IS NOT NULL)
not_null_id_not_null │ n │ ∅
(2 rows)

There is also the "attnotnull" column in "pg_attribute".

I didn't try it, but I guess that the performance difference will be measurable.
So I wouldn't call it "syntactic sugar".

Perhaps: The behavior of the NOT NULL constraint is like that of a check
constraint with IS NOT NULL.

Yours,
Laurenz Albe

On Fri, May 3, 2024 at 2:47 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2024-05-02 at 08:23 -0700, David G. Johnston wrote:

Version 2 attached. Still a draft, focused on topic picking and overall structure.

I'm fine with most of the material (ignoring ellipses and typos), except this:

+    The NOT NULL column constraint is largely syntax sugar for the corresponding
+    column IS NOT NULL check constraint, though there are metadata differences
+    described in create table.

the system does not translate (check constraint column IS NOT NULL)
to NOT NULL constraint,
at least in domain.

for example:
create domain connotnull integer;
alter domain connotnull add not null;
\dD connotnull

drop domain connotnull cascade;
create domain connotnull integer;
alter domain connotnull add check (value is not null);
\dD

On Fri, May 3, 2024 at 1:14 AM jian he <jian.universality@gmail.com> wrote:

On Fri, May 3, 2024 at 2:47 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Thu, 2024-05-02 at 08:23 -0700, David G. Johnston wrote:

Version 2 attached. Still a draft, focused on topic picking and

overall structure.

I'm fine with most of the material (ignoring ellipses and typos), except

this:

+ The NOT NULL column constraint is largely syntax sugar for the

corresponding

+ column IS NOT NULL check constraint, though there are metadata

differences

+ described in create table.

the system does not translate (check constraint column IS NOT NULL)
to NOT NULL constraint,
at least in domain.

I'll change this but I was focusing on the fact you get identical
user-visible behavior with not null and a check(col is not null). Chain of
thought being we discuss the is not null operator (indirectly) already and
so not null, which is syntax as opposed to an operation/expression, can
leverage that explanation as opposed to getting its own special case. I'll
consider this some more and maybe mention the catalog dynamics a bit as
well, or at least point to them.

drop domain connotnull cascade;
create domain connotnull integer;
alter domain connotnull add check (value is not null);
\dD

This reminds me, I forgot to add commentary regarding defining a not null
constraint on a domain but the domain type surviving a left join but having
a null value.

David J.

On 02.05.24 17:23, David G. Johnston wrote:

Version 2 attached.  Still a draft, focused on topic picking and overall
structure.  Examples and links planned plus the usual semantic markup stuff.

I chose to add a new sect1 in the user guide (The SQL Language) chapter,
"Data".

Please, let's not.

A stylistic note: "null" is an adjective. You can talk about a "null
value" or a value "is null". These are lower-cased (or maybe
title-cased). You can use upper-case when referring to SQL syntax
elements (in which case also tag it with something like <literal>), and
also to the C-language symbol (tagged with <symbol>). We had recently
cleaned this up, so I think the rest of the documentation should be
pretty consistent about this.

On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut <peter@eisentraut.org>
wrote:

On 02.05.24 17:23, David G. Johnston wrote:

Version 2 attached. Still a draft, focused on topic picking and overall
structure. Examples and links planned plus the usual semantic markup

stuff.

I chose to add a new sect1 in the user guide (The SQL Language) chapter,
"Data".

Please, let's not.

If a committer wants to state the single place in the documentation to put
this I'm content to put it there while leaving my reasoning of choices in
place for future bike-shedding. My next options to decide between are the
appendix or the lead chapter in Data Types. It really doesn't fit inside
DDL IMO which is the only other suggestion I've seen (and an uncertain, or
at least unsubstantiated, one) and a new chapter meets both criteria Tom
laid out, so long as this is framed as more than just having to document
null values.

A stylistic note: "null" is an adjective. You can talk about a "null
value" or a value "is null".

Will do.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut <peter@eisentraut.org>
wrote:

On 02.05.24 17:23, David G. Johnston wrote:

I chose to add a new sect1 in the user guide (The SQL Language) chapter,
"Data".

Please, let's not.

If a committer wants to state the single place in the documentation to put
this I'm content to put it there while leaving my reasoning of choices in
place for future bike-shedding. My next options to decide between are the
appendix or the lead chapter in Data Types. It really doesn't fit inside
DDL IMO which is the only other suggestion I've seen (and an uncertain, or
at least unsubstantiated, one) and a new chapter meets both criteria Tom
laid out, so long as this is framed as more than just having to document
null values.

I could see going that route if we actually had a chapter's worth of
material to put into "Data". But we don't, there's really only one
not-very-long section. Robert has justifiably complained about that
sort of thing elsewhere in the docs, and I don't want to argue with
him about why it'd be OK here.

Having said that, I reiterate my proposal that we make it a new
<sect1> under DDL, before 5.2 Default Values which is the first
place in ddl.sgml that assumes you have heard of nulls. Sure,
it's not totally ideal, but noplace is going to be entirely
perfect. I can see some attraction in dropping it under Data Types,
but (a) null is a data-type-independent concept, and (b) the
chapters before that are just full of places that assume you have
heard of nulls. Putting it in an appendix is similarly throwing
to the wind any idea that you can read the documentation in order.

Really, even the syntax chapter has some mentions of nulls.
If we did have a "Data" chapter there would be a case for
putting it as the *first* chapter of Part II.

I suppose we could address the nonlinearity gripe with a bunch
of cross-reference links, in which case maybe something under
Data Types is the least bad approach.

regards, tom lane

On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut <peter@eisentraut.org>
wrote:

On 02.05.24 17:23, David G. Johnston wrote:

I chose to add a new sect1 in the user guide (The SQL Language)

chapter,

"Data".

Please, let's not.

If a committer wants to state the single place in the documentation to

put

this I'm content to put it there while leaving my reasoning of choices in
place for future bike-shedding. My next options to decide between are

the

appendix or the lead chapter in Data Types. It really doesn't fit inside
DDL IMO which is the only other suggestion I've seen (and an uncertain,

or

at least unsubstantiated, one) and a new chapter meets both criteria Tom
laid out, so long as this is framed as more than just having to document
null values.

I could see going that route if we actually had a chapter's worth of
material to put into "Data". But we don't, there's really only one
not-very-long section. Robert has justifiably complained about that
sort of thing elsewhere in the docs, and I don't want to argue with
him about why it'd be OK here.

OK. I was hopeful that once the Chapter existed the annoyance of it being
short would be solved by making it longer. If we ever do that, moving this
section under there at that point would be an option.

Having said that, I reiterate my proposal that we make it a new
<sect1> under DDL, before 5.2 Default Values which is the first
place in ddl.sgml that assumes you have heard of nulls.

I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.

Putting it in an appendix is similarly throwing

to the wind any idea that you can read the documentation in order.

I think we can keep the entire camel out of the tent while letting it get a
whiff of what is inside. It would be a summary reference linked to from
the various places that mention null values.
https://en.wikipedia.org/wiki/Camel%27s_nose

I suppose we could address the nonlinearity gripe with a bunch
of cross-reference links, in which case maybe something under
Data Types is the least bad approach.

Yeah, there is circularity here that is probably impossible to
completely resolve.

David J.

On Fri, May 3, 2024 at 9:00 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Having said that, I reiterate my proposal that we make it a new

<sect1> under DDL, before 5.2 Default Values which is the first

place in ddl.sgml that assumes you have heard of nulls.

I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.

v3 Attached.

Probably at the 90% complete mark. Minimal index entries, not as thorough
a look-about of the existing documentation as I'd like. Probably some
wording and style choices to tweak. Figured better to get feedback now
before I go into polish mode. In particular, tweaking and re-running the
examples.

Yes, I am aware of my improper indentation for programlisting and screen. I
wanted to be able to use the code folding features of my editor. Those can
be readily un-indented in the final version.

The changes to func.sgml is basically one change repeated something like 20
times with tweaks for true/false. Plus moving the discussion regarding the
SQL specification into the new null handling section.

It took me doing this to really understand the difference between row
constructors and composite typed values, especially since array
constructors produce array typed values and the constructor is just an
unimportant implementation option while row constructors introduce
meaningfully different behaviors when used.

My plan is to have a v4 out next week, without or without a review of this
draft, but then the subsequent few weeks will probably be a bit quiet.

David J.

On Sat, May 11, 2024, 16:34 David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Fri, May 3, 2024 at 9:00 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Having said that, I reiterate my proposal that we make it a new

<sect1> under DDL, before 5.2 Default Values which is the first

place in ddl.sgml that assumes you have heard of nulls.

I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.

v3 Attached.

Probably at the 90% complete mark. Minimal index entries, not as thorough
a look-about of the existing documentation as I'd like. Probably some
wording and style choices to tweak. Figured better to get feedback now
before I go into polish mode. In particular, tweaking and re-running the
examples.

Yes, I am aware of my improper indentation for programlisting and screen.
I wanted to be able to use the code folding features of my editor. Those
can be readily un-indented in the final version.

The changes to func.sgml is basically one change repeated something like
20 times with tweaks for true/false. Plus moving the discussion regarding
the SQL specification into the new null handling section.

It took me doing this to really understand the difference between row
constructors and composite typed values, especially since array
constructors produce array typed values and the constructor is just an
unimportant implementation option while row constructors introduce
meaningfully different behaviors when used.

My plan is to have a v4 out next week, without or without a review of this
draft, but then the subsequent few weeks will probably be a bit quiet.

+   The cardinal rule, a given null value is never
+   <link linkend="functions-comparison-op-table">equal or unequal</link>
+   to any other non-null.

Again, doesn't this imply it tends to be equal to another null by its
omission?

Thom

On Saturday, May 11, 2024, Thom Brown <thom@linux.com> wrote:

Sat, May 11, 2024, 16:34 David G. Johnston <david.g.johnston@gmail.com>
wrote:

My plan is to have a v4 out next week, without or without a review of this

draft, but then the subsequent few weeks will probably be a bit quiet.

+   The cardinal rule, a given null value is never
+   <link linkend="functions-comparison-op-table">equal or unequal</link>
+   to any other non-null.

Again, doesn't this imply it tends to be equal to another null by its
omission?

I still agree, it’s just a typo now…

…is never equal or unequal to any value.

Though I haven’t settled on a phrasing I really like. But I’m trying to
avoid a parenthetical.

David J.

On Sat, May 11, 2024 at 11:00 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

Though I haven’t settled on a phrasing I really like. But I’m trying to
avoid a parenthetical.

Settled on this:

The cardinal rule, a null value is neither
<link linkend="functions-comparison-op-table">equal nor unequal</link>
to any value, including other null values.

I've been tempted to just say, "to any value.", but cannot quite bring
myself to do it...

David J.

On Sat, 11 May 2024 08:33:27 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:

On Fri, May 3, 2024 at 9:00 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Having said that, I reiterate my proposal that we make it a new

<sect1> under DDL, before 5.2 Default Values which is the first

place in ddl.sgml that assumes you have heard of nulls.

I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.

v3 Attached.

Probably at the 90% complete mark. Minimal index entries, not as thorough
a look-about of the existing documentation as I'd like. Probably some
wording and style choices to tweak. Figured better to get feedback now
before I go into polish mode. In particular, tweaking and re-running the
examples.

Yes, I am aware of my improper indentation for programlisting and screen. I
wanted to be able to use the code folding features of my editor. Those can
be readily un-indented in the final version.

The changes to func.sgml is basically one change repeated something like 20
times with tweaks for true/false. Plus moving the discussion regarding the
SQL specification into the new null handling section.

It took me doing this to really understand the difference between row
constructors and composite typed values, especially since array
constructors produce array typed values and the constructor is just an
unimportant implementation option while row constructors introduce
meaningfully different behaviors when used.

My plan is to have a v4 out next week, without or without a review of this
draft, but then the subsequent few weeks will probably be a bit quiet.

+   A null value literal is written as unquoted, case insensitive, NULL.
...(snip)...
+  <programlisting>
+  SELECT
+    NULL,
+    pg_typeof(null),
+    pg_typeof(NuLl::text),
+    cast(null as text);
+  </programlisting>

It may be a trivial thing but I am not sure we need to mention case insensitivity
here, because all keywords and unquoted identifiers are case-insensitive in
PostgreSQL and it is not specific to NULL.

Also, I found the other parts of the documentation use "case-insensitive" in which
words are joined with hyphen, so I wonder it is better to use the same form if we
leave the description.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata@sraoss.co.jp>

On Tue, Jun 18, 2024 at 8:34 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:

It may be a trivial thing but I am not sure we need to mention case
insensitivity
here, because all keywords and unquoted identifiers are case-insensitive in
PostgreSQL and it is not specific to NULL.

But it is neither a keyword nor an identifier. It behaves more like:
SELECT 1 as one; A constant, which have no implied rules - mainly because
numbers don't have case. Which suggests adding some specific mention there
- and also probably need to bring up it and its "untyped" nature in the
syntax chapter, probably here:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC

Also, I found the other parts of the documentation use "case-insensitive"
in which
words are joined with hyphen, so I wonder it is better to use the same
form if we
leave the description.

Typo on my part, fixed.

I'm not totally against just letting this content be assumed to be learned
from elsewhere in the documentation but it also seems reasonable to
include. I'm going to leave it for now.

David J.