Thread: 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.
Attachment
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.
"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 largelyalign 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.
Attachment
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 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.
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.
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.
Attachment
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.
<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:
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.
On Tue, 18 Jun 2024 20:56:58 -0700 "David G. Johnston" <david.g.johnston@gmail.com> wrote: > 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 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 <nagata@sraoss.co.jp>
Yugo NAGATA <nagata@sraoss.co.jp> writes:
> On Tue, 18 Jun 2024 20:56:58 -0700
> "David G. Johnston" <david.g.johnston@gmail.com> 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
On Tuesday, June 18, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yugo NAGATA <nagata@sraoss.co.jp> writes:
> On Tue, 18 Jun 2024 20:56:58 -0700
> "David G. Johnston" <david.g.johnston@gmail.com> 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.
On Tue, 18 Jun 2024 23:02:14 -0700 "David G. Johnston" <david.g.johnston@gmail.com> wrote: > On Tuesday, June 18, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > Yugo NAGATA <nagata@sraoss.co.jp> writes: > > > On Tue, 18 Jun 2024 20:56:58 -0700 > > > "David G. Johnston" <david.g.johnston@gmail.com> 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 <nagata@sraoss.co.jp>
On Wed, Jun 26, 2024 at 8:14 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
I still wonder it whould be unnecessary to mention the case-insensitivity here
if we can say NULL is *also* a keyword.
I went with wording that includes mentioning its keyword status.
The attached are complete and ready for review. I did some file structure reformatting at the end and left that as the second patch. The first contains all of the content.
I'm adding this to the commitfest.
Thanks!
David J.
Attachment
Thank you for taking the time to look this over.
On Wed, Nov 20, 2024 at 3:19 AM jian he <jian.universality@gmail.com> wrote:
On Sat, Jun 29, 2024 at 4:40 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> The attached are complete and ready for review. I did some file structure reformatting at the end and left that as the second patch. The first contains all of the content.
>
> I'm adding this to the commitfest.
>
> Thanks!
>
> David J.
in doc/src/sgml/nullvalues.sgml
can we mention
\pset null NULL
command, then NULL means this value is NULL.
you can also see doc/src/sgml/func.sgml
(The above example can be copied-and-pasted
into <application>psql</application> to set things up for the following
examples.
Good idea. I'll see how it plays out.
-------------------------------------------------------------
in doc/src/sgml/nullvalues.sgml
see the attached for one example output
in doc/src/sgml/nullvalues.sgml we have
one_whitespace<programlisting>
two_whitespace<programlisting>
three_whitespace<programlisting>
four_whitespace<programlisting>
i think you need zero whitespace for tag <programlisting>. like
<programlisting>
</programlisting>
https://tdg.docbook.org/tdg/4.5/programlisting
says whitespaces are significant.
Did you not apply patch 0002? The indentation in 0001 exists because it was much easier to deal with collapse-all related viewing in my editor. I removed it in 0002 since the final commit would indeed not be so indented. The tag itself doesn't actually care but its content does indeed get undesirably indented if the markup is nested in the typical manner.
<<>>
As noted in <xref linkend="json-type-mapping-table"/>, JSON has a null value
that does not get exposed at the SQL level.
<<>>
i feel like this sentence is weird. since these two are different things.
Yeah, the linked page and this summary/pointer need a bit of work. I don't like the unexplained "different concept" but probably "not exposed" isn't much better. As this gets closer to being committable I'll see about getting this topic cleaned up. Suggestions welcomed.
I think some of the query and query output can be combined into one
<programlisting>.
no need one <programlisting> for the query, one <screen> for the output.
Trivial to change but having both seems more semantically correct and easier to read IMO. We don't have a policy document covering this that I'm aware of, and IIRC both variations presently exist in the documentation.
David J.
About JSONB_PATH, you said that "JSON null value is considered equal to other JSON null values", but didn't say anything about IS DISTINCT FROM at jsonb_path level. Wouldn't be good to mention something about it ?
select '{1,2}'::integer[] is distinct from null::integer[]
select jsonb_path_exists('[null]', '$[*] ? (@ == null)')
regards
Marcos
On Thu, Nov 21, 2024 at 6:50 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
About JSONB_PATH, you said that "JSON null value is considered equal to other JSON null values", but didn't say anything about IS DISTINCT FROM at jsonb_path level. Wouldn't be good to mention something about it ?select '{1,2}'::integer[] is distinct from null::integer[]select jsonb_path_exists('[null]', '$[*] ? (@ == null)')
I'm not following your train of thought here. Since null == null in json-land there isn't a need for or concept of "is distinct from". We tend to not expend space on pointing out things that don't exist, and while I'm actually one to want to violate that principle more often than not this doesn't seem like a place for an exception. Especially without being motivated by end-user questions.
I'm glad they did it for semantics but the need for the path operator "@ is unknown" is redundant with just saying (@ == null). Pointing that out seems a bit superfluous though. The nulls equals each other is the key point to remember and then everything else works just as one would expect under that condition.
I may end up calling out this dynamic though (not related to json_path though possibly has an equivalent there, will need to look or be pointed to the relevant section).
UPDATE statements may use subscripting in the SET clause to modify jsonb values. Subscript paths must be traversable for all affected values insofar as they exist. For instance, the path val['a']['b']['c'] can be traversed all the way to c if every val, val['a'], and val['a']['b'] is an object. If any val['a'] or val['a']['b'] is not defined, it will be created as an empty object and filled as necessary. However, if any val itself or one of the intermediary values is defined as a non-object such as a string, number, or jsonb null, traversal cannot proceed so an error is raised and the transaction aborted.
David J.
Em qui., 21 de nov. de 2024 às 11:42, David G. Johnston <david.g.johnston@gmail.com> escreveu:
I'm not following your train of thought here. Since null == null in json-land there isn't a need for or concept of "is distinct from". We tend to not expend space on pointing out things that don't exist.
But you said previously in this document about IS DISTINCT, so it's related to NULL. I thought it would be better to mention that here, for JSON PATH, that way doesn't exist.
"JSON null value is considered equal to other JSON null values, so here we don't have the IS DISTINCT operator"
regards
Marcos
On Wed, Nov 20, 2024 at 7:24 PM jian he <jian.universality@gmail.com> wrote:
On Wed, Nov 20, 2024 at 11:57 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
>> -------------------------------------------------------------
>> in doc/src/sgml/nullvalues.sgml
>> see the attached for one example output
>>
>> in doc/src/sgml/nullvalues.sgml we have
>> one_whitespace<programlisting>
>> two_whitespace<programlisting>
>> three_whitespace<programlisting>
>> four_whitespace<programlisting>
>>
>> i think you need zero whitespace for tag <programlisting>. like
>> <programlisting>
>> </programlisting>
>>
>> https://tdg.docbook.org/tdg/4.5/programlisting
>> says whitespaces are significant.
>
>
> Did you not apply patch 0002? The indentation in 0001 exists because it was much easier to deal with collapse-all related viewing in my editor. I removed it in 0002 since the final commit would indeed not be so indented. The tag itself doesn't actually care but its content does indeed get undesirably indented if the markup is nested in the typical manner.
>
i didn't apply patch 0002, 0001 is already too much.
attached image.png for
5.2.7.2. Array Elements and IN Bag Members
the example is too overwhelming, one or two should be enough?
5.2.7.3. Single-Column Subquery Rows.
two examples, can be reduced to one.
Yeah, examples will be there own pass for cleanup as the patch gets closer to acceptance.
typo:
There are none. During initializion all settings are assigned a non-null value.
5.2.16. Null Values in Partiton Keys
As noted in the synatx chapter, a null value literal is written using
the NULL keyword. Its type is the pseudo-type unknown but can be cast
to any concrete data type.
Sorry, not seeing the typo. Can you point it out or supply the fix?
""
At present this is typically a non-issue as PostgreSQL does not
support a primary key that does not include partition key columns, and
all columns in a primary key are forced to be have not null
constraints.
""
"does not support...does not include" double negation, can we make it
"positive".
"not null constraints." should be "not-null constraints"?
Yeah, I will clean that up.
David J.
On Fri, Jun 28, 2024 at 1:39 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
The attached are complete and ready for review. I did some file structure reformatting at the end and left that as the second patch. The first contains all of the content.
To help manage this fairly large patch I've created a wiki page listing the sect2 titles (might need to add the handful of sect3s too).
My goal is to use it as a WIP tracker for changes as well as a convenient place for getting confirmation that specific sections have been looked at and deemed ready-to-commit. No one person needs to look at everything but so long as each has one or more someones who has reviewed it the whole patch can then be marked ready-to-commit.
David J.
Well, all comparisons with JSONs runs differently with null values, so maybe an example would help
select f1 = f2 "JS Object Equal - Right",
f1->'a' = f2->'a' "JS Value Equal - Right",
f1->>'a' = f2->>'a' "Text Equal - Wrong",
f1->>'a' IS NOT DISTINCT FROM f2->>'a' "Text Distinct - Right"
from (Values ('{"a": 5}'::jsonb, '{"a": null}'::jsonb)) x(f1,f2)
f1->'a' = f2->'a' "JS Value Equal - Right",
f1->>'a' = f2->>'a' "Text Equal - Wrong",
f1->>'a' IS NOT DISTINCT FROM f2->>'a' "Text Distinct - Right"
from (Values ('{"a": 5}'::jsonb, '{"a": null}'::jsonb)) x(f1,f2)
"JSON values, independently if null or not, are compared using Equal and not Equal operators, so here we don't have the IS DISTINCT operator"
regards
Marcos