Re: Document NULL
On Tue, 18 Jun 2024 23:02:14 -0700 "David G. Johnston" wrote: > On Tuesday, June 18, 2024, Tom Lane wrote: > > > Yugo NAGATA writes: > > > On Tue, 18 Jun 2024 20:56:58 -0700 > > > "David G. Johnston" wrote: > > >> But it is neither a keyword nor an identifier. > > > > The lexer would be quite surprised by your claim that NULL isn't > > a keyword. Per src/include/parser/kwlist.h, NULL is a keyword, > > and a fully reserved one at that. > > > > > > > > Can’t it be both a value and a keyword? I figured the not null constraint > and is null predicates are why it’s a keyword but the existence of those > doesn’t cover its usage as a literal value that can be stuck anywhere you > have an expression. I still wonder it whould be unnecessary to mention the case-insensitivity here if we can say NULL is *also* a keyword. Regards, Yugo Nagata > David J. -- Yugo NAGATA
Re: Document NULL
On Tuesday, June 18, 2024, Tom Lane wrote: > Yugo NAGATA writes: > > On Tue, 18 Jun 2024 20:56:58 -0700 > > "David G. Johnston" wrote: > >> But it is neither a keyword nor an identifier. > > The lexer would be quite surprised by your claim that NULL isn't > a keyword. Per src/include/parser/kwlist.h, NULL is a keyword, > and a fully reserved one at that. > > > Can’t it be both a value and a keyword? I figured the not null constraint and is null predicates are why it’s a keyword but the existence of those doesn’t cover its usage as a literal value that can be stuck anywhere you have an expression. David J.
Re: Document NULL
Yugo NAGATA writes: > On Tue, 18 Jun 2024 20:56:58 -0700 > "David G. Johnston" wrote: >> But it is neither a keyword nor an identifier. The lexer would be quite surprised by your claim that NULL isn't a keyword. Per src/include/parser/kwlist.h, NULL is a keyword, and a fully reserved one at that. regards, tom lane
Re: Document NULL
On Tue, 18 Jun 2024 20:56:58 -0700 "David G. Johnston" wrote: > On Tue, Jun 18, 2024 at 8:34 PM Yugo NAGATA 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 Thank you for your explanation. This makes a bit clear for me why the description mentions 'string' syntax there. I just thought NULL is a keyword representing a null constant. > - 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. -- Yugo NAGATA
Re: Document NULL
On Tue, Jun 18, 2024 at 8:34 PM Yugo NAGATA 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.
Re: Document NULL
On Sat, 11 May 2024 08:33:27 -0700 "David G. Johnston" wrote: > On Fri, May 3, 2024 at 9:00 AM David G. Johnston > wrote: > > > On Fri, May 3, 2024 at 8:44 AM Tom Lane wrote: > > > >> Having said that, I reiterate my proposal that we make it a new > >> > > 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)... + + SELECT +NULL, +pg_typeof(null), +pg_typeof(NuLl::text), +cast(null as text); + 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
Re: Document NULL
On Sat, May 11, 2024 at 11:00 AM David G. Johnston < david.g.johns...@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 equal nor unequal 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.
Re: Document NULL
On Saturday, May 11, 2024, Thom Brown wrote: > > Sat, May 11, 2024, 16:34 David G. Johnston > 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 > + equal or unequal > + 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.
Re: Document NULL
On Sat, May 11, 2024, 16:34 David G. Johnston wrote: > On Fri, May 3, 2024 at 9:00 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Fri, May 3, 2024 at 8:44 AM Tom Lane wrote: >> >>> Having said that, I reiterate my proposal that we make it a new >>> >> 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 + equal or unequal + to any other non-null. Again, doesn't this imply it tends to be equal to another null by its omission? Thom >
Re: Document NULL
On Fri, May 3, 2024 at 9:00 AM David G. Johnston wrote: > On Fri, May 3, 2024 at 8:44 AM Tom Lane wrote: > >> Having said that, I reiterate my proposal that we make it a new >> > 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. From bea784bd683f7e022dbfb3d72832d09fc7754913 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Wed, 1 May 2024 07:45:48 -0700 Subject: [PATCH] Document NULL --- doc/src/sgml/ddl.sgml| 2 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/func.sgml | 268 ++--- doc/src/sgml/nullvalues.sgml | 719 +++ 4 files changed, 837 insertions(+), 153 deletions(-) create mode 100644 doc/src/sgml/nullvalues.sgml diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 026bfff70f..68a0fe698d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -168,6 +168,8 @@ DROP TABLE products; + &nullvalues; + Default Values diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 38ec362d8f..882752e88f 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -21,6 +21,7 @@ + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 17c44bc338..98fba7742c 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -23295,7 +23295,8 @@ MERGE INTO products p This section describes the SQL-compliant subquery expressions available in PostgreSQL. All of the expression forms documented in this section return - Boolean (true/false) results. + three-valued typed + results (true, false, or null). @@ -23357,19 +23358,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression + subquery, which must return exactly one column. The result of IN + is false if the subquery returns no rows, otherwise the left-hand expression is evaluated and compared to each row of the subquery result. - The result of IN is true if any equal subquery row is found. - The result is false if no equal row is found (including the - case where the subquery returns no rows). + The result is true if any equal subquery row is found. + The result is false if no equal row is found. - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand row yields - null, the result of the IN construct will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + As explained in , it is not possible to see + a false result in the presence of both rows and null values since the multiple equality + tests are AND'd together. @@ -23386,21 +23385,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); as described in . The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are + expressions in the left-hand row. + The result of IN is false if the subquery returns no rows, + otherwise the left-hand expressions are evaluated and compared row-wise
Re: Document NULL
On Fri, May 3, 2024 at 8:44 AM Tom Lane wrote: > "David G. Johnston" writes: > > On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut > > 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 > 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.
Re: Document NULL
"David G. Johnston" writes: > On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut > 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 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
Re: Document NULL
On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut 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.
Re: Document NULL
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 ), and also to the C-language symbol (tagged with ). We had recently cleaned this up, so I think the rest of the documentation should be pretty consistent about this.
Re: Document NULL
On Fri, May 3, 2024 at 1:14 AM jian he wrote: > On Fri, May 3, 2024 at 2:47 PM Laurenz Albe > 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.
Re: Document NULL
On Fri, May 3, 2024 at 2:47 PM Laurenz Albe 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
Re: Document NULL
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
Re: Document NULL
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 wrote: > On Wed, May 1, 2024 at 9:47 PM Tom Lane wrote: > >> David Rowley 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. > > >
Re: Document NULL
On Wed, May 1, 2024 at 9:47 PM Tom Lane wrote: > David Rowley 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. From 7798121992154edab4768d7eab5a89be04730b2f Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Wed, 1 May 2024 07:45:48 -0700 Subject: [PATCH] Document NULL --- doc/src/sgml/data.sgml | 169 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/postgres.sgml | 1 + 3 files changed, 171 insertions(+) create mode 100644 doc/src/sgml/data.sgml diff --git a/doc/src/sgml/data.sgml b/doc/src/sgml/data.sgml new file mode 100644 index 00..2b09382494 --- /dev/null +++ b/doc/src/sgml/data.sgml @@ -0,0 +1,169 @@ + + Data + + + This chapter provides definitions for, and an overview of, data. + It discusses the basic design of the metadata related to values + and then goes on to describe the special value NULL which typically + represents "unknown" + + + + Data Basics + + All literals, columns, variables, and expression results in PostgreSQL + are typed, which are listed in the next chapter. Literals and columns + must only use one of the concrete types while variables can use + either a concrete type or a pseudo-type. Expression results + are limited to concrete types and the pseudo-type record described below. + + + The pseudo-types prefixed with "any" implement polymorphism + in PostgreSQL. Polymorphism allows a single function specification + to act on multiple concrete types. At runtime, the function body + associates concrete types to all polymorphic types based upon the + conrete argument of its inputs. See ... for more details. + + + The record pseudo-type is also polymorphic in nature but allows + the caller of the function to specify the row-like structure of + output within the containing query. See ... for more details. + The ROW(...) expression (see ...) will also produce a record + result comprised of the named columns. + + + + + Unknown Values (NULL) + +This section first introduces the meaning of NULL and then goes +on to explain how different parts of the system behave when faced +with NULL input. + + + + NULL in Data Models + +Generally NULL is assumed to mean "unknown". However, +in practice meaning comes from context and so a model design may state that +NULL is to be used to represent "not applicable" - i.e., that a value is not +even possible. SQL has only the single value NULL while there are multiple +concepts that people have chosen to apply it to. In any case the behavior +of the system when dealing with NULL is the same regardless of the meaning +the given to it in the surrounding context. + + +NULL also takes on a literal meaning of "not found" when produced as the +result of an outer join. + + + + + NULL Usage + +As NULL is treated as a data value it, like all values, must have +a data type. NULL is a valid value for all data types. + + + +A NULL literal is written as unquoted NULL. Its type is unknown but +can be cast to any concrete data type. The [type 'string'] syntax +however will not work as there is no way to express NULL using single +quotes and unlike. + + + +The presence of NULL in the system results in three-valued logic. +In binary logic every outcome is either true or false. In +three-valued logic unknown, represented using a NULL value, is +also an outcome. Aspects of the system that branch based upon +whether a condition variable is true or false thus must also +decide how to behave when then condition is NULL. The remaining +sub-sections summarize these decisions. + + + + +The Cardinal Rule of NULL + +The cardinal rule, NULL is never equal or unequal to any non-null +value (or itself). [NULL = anything yield
Re: Document NULL
David Rowley 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
Re: Document NULL
On Thu, 2 May 2024 at 03:12, David G. Johnston 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: + Handling Unkowns (NULL) Maybe inject "Values" after Unknown. Let's bash it into shape a bit more before going any further on actual wording. David
Re: Document NULL
On Wed, May 1, 2024 at 8:12 PM David G. Johnston 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 > > David J. > > [1] > https://www.postgresql.org/message-id/1859814.1714532025%40sss.pgh.pa.us > >
Re: Document NULL
On Wed, May 1, 2024, 16:13 David G. Johnston 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] > https://www.postgresql.org/message-id/1859814.1714532025%40sss.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
Document NULL
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] https://www.postgresql.org/message-id/1859814.1714532025%40sss.pgh.pa.us From a068247e92e620455a925a0ae746adc225ae1339 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Wed, 1 May 2024 07:45:48 -0700 Subject: [PATCH] Document NULL --- doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/null.sgml | 79 ++ doc/src/sgml/query.sgml| 2 + 3 files changed, 82 insertions(+) create mode 100644 doc/src/sgml/null.sgml diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 38ec362d8f..ac4fd52978 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -10,6 +10,7 @@ + diff --git a/doc/src/sgml/null.sgml b/doc/src/sgml/null.sgml new file mode 100644 index 00..5f95b2494e --- /dev/null +++ b/doc/src/sgml/null.sgml @@ -0,0 +1,79 @@ + + Handling Unkowns (NULL) + + + NULL + + + + Looking again at our example weather data you will note that we do not know + the amount of precipitation Hayward. We communicated that implicitly by + not including the prcp column in the insert. Explicitly, we can communicate + this fact by writing. [example using null]. When a column is not specified + in an insert the default value for that column is recorded and the default + default value is NULL. + + + + As a value NULL crops up all throughout the database and interacts with many + features. The main portion of this book will detail the interactions specific + features have with NULL but it is helpful to have a reference page where one + can get an overview. + + + + First, like all values, NULLs are typed. But since any value can be unknown + NULL is a valid value for all data types. + + + + Second, when speaking generally NULL is assumed to mean unknown. However, + in practice meaning comes from context and so a model design may state that + NULL is to be used to represent "not applicable" - i.e., that a value is not + even possible. SQL has only the single value NULL while there are multiple + concepts that people have chosen to apply it to. In any case the behavior + of the system when dealing with NULL is the same regardless of the meaning + the given to it in the surrounding context. + + + + The cardinal rule, NULL is never equal or unequal to any non-null + value; and when asked to be combined with a known value in an operation the + result of the operation becomes unknown. e.g., both 1 = NULL and 1 + NULL + result in NULL. Exceptions to this are documented. See [chapter] for + details on how to test for null. Specifically, note that concept of + distinctness is introduced to allow for true/false equality tests. + + + + Extending from the previous point, function calls are truly a mixed bag. + Aggregate functions in particular will usually just ignore NULL inputs + instead of forcing the entire aggregate result to NULL. Function + specifications has a "strictness" attribute that, when set to "strict" + (a.k.a. "null on null input") will tell the executor to return NULL for any + function call having at least one NULL input value, without executing the + function. + + + + A WHERE clause that evaluates to NULL for a given row will exclude that row. + This was demonstrated in the tutorial query where cities with prcp > 0 were + requested and Hayward was not returned due to this and the cardinal rule. + + + + While not yet discussed, it is possible to define validation expressions on + tables that ensure only values passing those expressions are inserted. While + this seems like it would behave as a WHERE clause on a table, the choice here + when an expression evaulates to NULL is al
