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
On Fri, 2024-06-28 at 13:39 -0700, David G. Johnston 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 read through v4-0001. Thank you for working on this!
I really like the overall feel of the document: outlines the various
interpretations, behaviors, nuances and rationales; practical and not
philosophical.
Comments:
1.
One idea is to have a brief guidance section to help users know how to
use nulls in their data model effectively. For instance, if you allow
nulls for middle_name to mean "no middle name", then you have to be
careful when concatenating it as part of a larger string (otherwise it
will make the entire result null). Using COALESCE() can be a good
strategy here.
2.
It would be helpful to go through a combined example that shows how
these varous behaviors interact. For instance:
SELECT r.a, SUM(s.b)
FROM r LEFT JOIN s ON r.id = s.id
GROUP BY r.a HAVING SUM(s.b) < 123;
Assume that there are no null values in r or s, and there's one record
in r with no match in s. First, a null value comes into existence from
the outer join when there's no match. Then, the GROUP BY creates a
group with a single null value. Then the SUM aggregates it and returns
null. Then the less-than expression evaluates to null (due to 3VL),
then the HAVING clause excludes the record because it's distinct from
true. That's probably not what the user intended -- the sum of no
records is intuitively less than 123.
3. "...more formally, the Law of the Excluded Middle does not hold:
i.e., p OR NOT(p) != true; for all p."
Switching to formal language here is confusing (and wrong, I think). I
suggest rewording and I don't think you need formal language here: what
you are highlighting is that, when p is the null value, the expression
"p OR NOT p" evaluates to null, which is surprising to someone who is
used to thinking in 2VL.
4. COUNT() with no input is a special case that returns zero, and I
think that's worth mentioning somewhere.
Regards,
Jeff Davis
Em qui., 21 de nov. de 2024 às 12:04, David G. Johnston <david.g.johnston@gmail.com> escreveu:
Typo in NuLl, mixed upper and lower case.
SELECT
NULL AS "Literal Null Value",
pg_typeof(null) AS "Type of Null",
pg_typeof(NuLl::text) AS "Type of Cast null",
cast(null as text) AS "Cast null value";
NULL AS "Literal Null Value",
pg_typeof(null) AS "Type of Null",
pg_typeof(NuLl::text) AS "Type of Cast null",
cast(null as text) AS "Cast null value";
should be
pg_typeof(null::text) AS "Type of Cast Null",
On Sat, Nov 23, 2024 at 5:30 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
Em qui., 21 de nov. de 2024 às 12:04, David G. Johnston <david.g.johnston@gmail.com> escreveu:Typo in NuLl, mixed upper and lower case.SELECT
NULL AS "Literal Null Value",
pg_typeof(null) AS "Type of Null",
pg_typeof(NuLl::text) AS "Type of Cast null",
cast(null as text) AS "Cast null value";should bepg_typeof(null::text) AS "Type of Cast Null",
That was not a typo. I'm intentionally showing an example demonstrating that the SQL NULL is fully case-insensitive. But I suppose showing both all-upper and all-lower cases fulfills that goal sufficiently. Changed. Went with NULL so there are two of each. Made all of the column headers Title Case.
Thanks!
David J.
On Mon, Dec 2, 2024 at 12:18 AM jian he <jian.universality@gmail.com> wrote:
>>
>> 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?
>
typo and other whitespace changes that i think make sense.
please check attached.
I fixed the 3 spelling typos. I left the full-stop-double-space alone consistent with elsewhere on the page. The indentation changes I've set aside until later, though they are incomplete as provided. 0002 has the correct indentation for the v4 patch series. My plan is to put forth a v5-0001 patch this week and once a review is willing to indicate ready-to-commit I'll put out a v6 with the indenting applied.
David J.
On Thu, Nov 21, 2024 at 8:03 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
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"
I added this to "Overview"
<para>
Throughout this section the discussion of null values will be limited to
the SQL language unless otherwise noted. The JSON-related data types, and the
non-SQL procedural languages, have their own behaviors documented in their
respective areas.
</para>
Throughout this section the discussion of null values will be limited to
the SQL language unless otherwise noted. The JSON-related data types, and the
non-SQL procedural languages, have their own behaviors documented in their
respective areas.
</para>
And added this to "Distinctness..."
<para>
On the other hand, the SQL specification is largely alone in taking this approach to comparing
values to the null value. Specifically, when working within the JSON data types the use of equals
produces true or false and so the concept of distinctness is neither present nor required. Please
consult the documentation for the non-SQL procedural language of choice to learn about its behavior.
</para>
On the other hand, the SQL specification is largely alone in taking this approach to comparing
values to the null value. Specifically, when working within the JSON data types the use of equals
produces true or false and so the concept of distinctness is neither present nor required. Please
consult the documentation for the non-SQL procedural language of choice to learn about its behavior.
</para>
I'm OK with adding more cross-references (links or just brief comparative verbiage like the above) to non-SQL null value treatment but this document, for this patch, is going to focus solely on SQL NULL.
David J.
Em seg., 9 de dez. de 2024 às 13:31, David G. Johnston <david.g.johnston@gmail.com> escreveu:
I'm OK with adding more cross-references (links or just brief comparative verbiage like the above) to non-SQL null value treatment but this document, for this patch, is going to focus solely on SQL NULL.
If you briefly mention JSON NULLs, it might be interesting to also briefly mention that in other places NULL can be seen differently, including changing its name. A SQL NULL passed as argument to a PL/Python function will be named None or PL/Perl will be undefined.
regards,
Marcos
On Wed, Nov 20, 2024 at 7:24 PM jian he <jian.universality@gmail.com> wrote:
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?
Agreed, fixed in the upcoming v5. The four outputs are actually two equivalent pairs so I framed them up as such and removed two of the results.
5.2.7.3. Single-Column Subquery Rows.
two examples, can be reduced to one.
Disagree, the fact that the two outputs are different is precisely the point of having the two queries. I could see an argument for removing both, given the pair outputs are indeed the same pair in the previous sect3. But since they are separate sect3s I'm inclined to keep this intact.
"does not support...does not include" double negation, can we make it
"positive".
"not null constraints." should be "not-null constraints"?
I've also reworked this for v5
David J.
Thank you for the review. Changes noted below will be part of v5.
On Fri, Nov 22, 2024 at 12:00 PM Jeff Davis <pgsql@j-davis.com> wrote:
One idea is to have a brief guidance section to help users know how to
use nulls in their data model effectively. For instance, if you allow
nulls for middle_name to mean "no middle name", then you have to be
careful when concatenating it as part of a larger string (otherwise it
will make the entire result null).
I haven't explicitly included such an example but have expanded in this direction a bit.
Using COALESCE() can be a good
strategy here.
I have now mentioned coalesce and nullif.
<para>
When dealing with null values it is often useful to explicitly to convert
data to and from a null value given a known non-null representation
(e.g., the empty string, the numbers 0 or 1, or boolean false).
The <link>COALESCE</link> and <link>NULLIF</link> functions are useful
for this purpose.
</para>
When dealing with null values it is often useful to explicitly to convert
data to and from a null value given a known non-null representation
(e.g., the empty string, the numbers 0 or 1, or boolean false).
The <link>COALESCE</link> and <link>NULLIF</link> functions are useful
for this purpose.
</para>
2.
It would be helpful to go through a combined example that shows how
these varous behaviors interact.
I have not done this. This is already a large patch and this kind of example doesn't seem like our norm. I'm not opposed to more content like this but for now would leave considering it as something an interested party can propose once this goes in.
3. "...more formally, the Law of the Excluded Middle does not hold:
i.e., p OR NOT(p) != true; for all p."
Switching to formal language here is confusing (and wrong, I think). I
suggest rewording and I don't think you need formal language here:
Agreed. This isn't the place for that presentation and material.
<para>
The presence of null values in the system results in three-valued logic.
In conventional two-valued (binary) logic every outcome is either true or false.
In three-valued logic the concept of unknown, represented using the null value, is
also an outcome. This results in falsifying the common-sense notion
that "p OR NOT p" is always true.
</para>
4. COUNT() with no input is a special case that returns zero, and I
think that's worth mentioning somewhere.
I added a parenthetical to the following sentence to address this point:
When executing an aggregate or window function the state tracking component
(which may be initialized to a non-null value, e.g., 0 for the count function)
will remain unchanged even if the underlying processing
function returns a null value, whether from being defined strict
or it simply returns a null value upon execution.
(which may be initialized to a non-null value, e.g., 0 for the count function)
will remain unchanged even if the underlying processing
function returns a null value, whether from being defined strict
or it simply returns a null value upon execution.
I'm hesitant to add an example for it though...the implication of the note seems sufficiently clear - if there are zero rows providing non-null inputs to an aggregate its concept of initialized non-null value will be returned. Since count doesn't have an input function to check the only way to see zero such rows is if the underlying thing being counted is empty.
David J.
On Mon, 2024-12-09 at 15:27 -0700, David G. Johnston wrote:
> I have not done this. This is already a large patch and this kind of
> example doesn't seem like our norm. I'm not opposed to more content
> like this but for now would leave considering it as something an
> interested party can propose once this goes in.
Fair enough
Though I think it's a great example and I'd like to find some place to
put it.
>
> <para>
> The presence of null values in the system results in three-valued
> logic.
> In conventional two-valued (binary) logic every outcome is either
> true or false.
> In three-valued logic the concept of unknown, represented using
> the null value, is
> also an outcome. This results in falsifying the common-sense
> notion
> that "p OR NOT p" is always true.
> </para>
Thank you.
I might reword the final sentence as more of an example, like: "Unknown
values can lead to surprising behavior, for instance "NULL OR NOT NULL"
evaluates to the null value."
>
> When executing an aggregate or window function the state tracking
> component
> (which may be initialized to a non-null value, e.g., 0 for the
> count function)
> will remain unchanged even if the underlying processing
> function returns a null value, whether from being defined strict
> or it simply returns a null value upon execution.
Thank you.
> Since count doesn't have an input function to check the only way to
> see zero such rows is if the underlying thing being counted is empty.
While true for COUNT(*), technically that's incorrect for COUNT(x),
which counts the rows for which x is non-null. That doesn't invalidate
your point, though: the initial state is unchanged either way.
Regards,
Jeff Davis
Em ter., 10 de dez. de 2024 às 20:00, David G. Johnston <david.g.johnston@gmail.com> escreveu:
I got rid of the row counts on the examples.
Cool, I would love to get rid all of them, like I proposed on [1].
When I finalize the examples I'm probably going to \pset null <NULL>.
Yes, much better than an empty space in the examples, but you need to show what PSET you did, maybe
<literal>\pset null</literal> meta-command</link> to specify the textual output of null values
it encounters in query results. To get same results as you are seeing on this page, do "\pset null <NULL>"
it encounters in query results. To get same results as you are seeing on this page, do "\pset null <NULL>"
typo in func.sgml
<link linkend="nullvalues">three-valued</link> typed
results (true, false, or null).
results (true, false, or null).
should remove that comma, right ?
results (true, false or null).
Would be good to mention on nullvalues-json section that nulls on JSON values are case sensitive, so NULL or Null won't work
regards
Marcos
Em ter., 10 de dez. de 2024 às 20:00, David G. Johnston <david.g.johnston@gmail.com> escreveu:
Section nullvalues-filtering you are showing filtering with equal and not equal. Wouldn't it be better if you show just one of them and the other using DISTINCT FROM, which would get different results ?
regards
Marcos
On Wed, Dec 11, 2024 at 11:46 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
Em ter., 10 de dez. de 2024 às 20:00, David G. Johnston <david.g.johnston@gmail.com> escreveu:Section nullvalues-filtering you are showing filtering with equal and not equal. Wouldn't it be better if you show just one of them and the other using DISTINCT FROM, which would get different results ?
I'm demonstrating the sentence written there -
A WHERE clause that evaluates to a null value for a given row will exclude that row.
While I can do that with a single example my intent here was to also show that if one writes seemingly mutually exclusive expressions in a where clause it is possible neither expression will find a row, in this case with id=2. "p OR !p" again. I'll give this some more thought though.
In any case I do need to add a few more words framing up the examples. Probably pointing back to the cardinal rule sub-section.
David J.
On Wed, Dec 11, 2024 at 8:09 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
Em ter., 10 de dez. de 2024 às 20:00, David G. Johnston <david.g.johnston@gmail.com> escreveu:When I finalize the examples I'm probably going to \pset null <NULL>.Yes, much better than an empty space in the examples, but you need to show what PSET you did, maybe<literal>\pset null</literal> meta-command</link> to specify the textual output of null values
it encounters in query results. To get same results as you are seeing on this page, do "\pset null <NULL>"
Yes, I will be doing this in some form. My indecision at the moment is that this ideally belongs in the preamble material for the section but wants the reader to have seen the usage output sub-section.
typo in func.sgml<link linkend="nullvalues">three-valued</link> typed
results (true, false, or null).should remove that comma, right ?results (true, false or null).
I include the Oxford commas unless I'm forbidden to. There isn't any forbiddance here that I am aware of. But now I'm questioning whether it should read "true, false, and null" instead of "true, false, or null"...though I'm confident that any of the four options is going to be understood by the reader and this is basically purely stylistic in an area we haven't codified. Any specific arguments or prior-art to consider for choosing one over the others?
Would be good to mention on nullvalues-json section that nulls on JSON values are case sensitive, so NULL or Null won't work
Agreed.
David J.
On Sun, Dec 22, 2024 at 7:27 AM jian he <jian.universality@gmail.com> wrote:
On Wed, Dec 11, 2024 at 7:00 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> v5 Attached, v5-0001 is just v4-0001 rebased; v5-0002 is the rework over v4-0001. There is no formatting-only patch this round.
>
> Wiki tracker: https://wiki.postgresql.org/wiki/Documenting_NULL#ToDo_Note
>
please see attached png file.
As you can see, many of the <screen> </screen> are not fully
left-aligned, and also have an extra empty new line.
I'm aware, which is why I keep mentioning "formatting" in my emails.
Again, I'm choosing to indent those tags because while I edit I find it much easier if I can "fold all" in my editor and have those tags be hidden away under the fold.
Please use your imagination to visualize how thIngs would look without the indentation, or un-indent the elements yourself if you find it so bothersome to look at. I promise you I will unindent them in the final patch that would be committed. Until then it is having no material impact on the substance/content of the patch - which is what needs reviewing. And since you didn't even apply my formatting 0002 patch the first time around I figured producing it was simply unneeded busy-work.
I will try to remember to consider/fix the "extra newline" aspect of this since simply unindenting isn't going to alter vertical space, just horizontal.
+ <para>
+ As a general expectation, operator invocation expressions where
one of inputs
+ is a null value will result in a null-valued output.
I think the following description is more simple and concise.
+Typically, when one of the inputs in an operator invocation
expression is a null value, the output is expected to also be null.
Not sure on the "is expected" qualifier but overall that does seem better.
+ The <link linkend="sql-copy"><command>COPY ... TO</command></link> command,
+ including its psql counter-part meta-command
+ <link linkend="app-psql-meta-commands-copy"><command>\copy</command></link>,
+ has the <literal>NULL</literal> option (and some modifier
options) to specify
+ the string to print to the output for null values it encounters
in the query result.
+ As with input file processing, for the CSV format it will, by default,
+ produce an unquoted empty string for the null value.
+ </para>
I think the following make more sense:
+ The <link linkend="sql-copy"><command>COPY ... TO</command></link>
command and
+ <application>psql</application> meta-command
+ <link linkend="app-psql-meta-commands-copy"><command>\copy</command></link>,
+ has the <literal>NULL</literal> option to specify
+ the string that represents a null value. The default is
+ <literal>\N</literal> (backslash-N) in text format, and an unquoted empty
+ string in <literal>CSV</literal> format.
+ </para>
also psql should decorated as <application>psql</application>
The extra detail for text format is out-of-place in this overview document - csv format is most commonly used so I mention it for the example. I do not feel a need to provide commentary for other formats or provide other examples. I do think at least alluding to the other options, so the reader can go look for them, is a positive. I'll markup psql.
----------------------------------------------------------------------------
+ It is possible to define
+ <link linkend="ddl-constraints-check-constraints">check constraint</link>
+ expressions on tables to ensure only values passing those
expressions are inserted.
+ While this seems like it would behave the same as a where clause,
the choice here,
+ when an expression evaulates to a null value, is to allow the row
to be inserted
+ - the same as a true result.
i think in ddl.sgml,
we already have """
It should be noted that a check constraint is satisfied if the
check expression evaluates to true or the null value.
"""
it is more concise, IMO, we can just try to copy it here.
I may have been overly verbose in a bad way but just putting that sentence here is too concise IMO. Absent a complete suggestion to consider I'm inclined to stick with what I have.
Thanks!
David J.
Em dom., 22 de dez. de 2024 às 12:02, David G. Johnston <david.g.johnston@gmail.com> escreveu:
You have detailed IS NULL and IS NOT NULL predicates but not mention their counterparts ISNULL and NOTNULL, wouldn't it be good to explain that too ? Maybe pointing to its page "functions-comparison"
regards
Marcos
On Sun, Dec 22, 2024 at 9:45 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
Em dom., 22 de dez. de 2024 às 12:02, David G. Johnston <david.g.johnston@gmail.com> escreveu:You have detailed IS NULL and IS NOT NULL predicates but not mention their counterparts ISNULL and NOTNULL, wouldn't it be good to explain that too ? Maybe pointing to its page "functions-comparison"
I'm disinclined to mention alternative non-standard syntax here. I'm happy leaving those indirectly mentioned ("other predicates") by the existing sentence:
These, and other predicates, are described in Table 9.2
And pointing the reader to the table that includes these non-standard syntax forms.
David J.
Em dom., 22 de dez. de 2024 às 15:07, David G. Johnston <david.g.johnston@gmail.com> escreveu:
On section Null-Valued Settings is not easy to understand that "show example.string" will result in an "ERROR: unrecognized...", that BEGIN or ROLLBACK does not return anything. So, what do you think about creating a table with two columns and showing command and result side by side ?
regards
Marcos
On Mon, Dec 23, 2024 at 11:39 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
Em dom., 22 de dez. de 2024 às 15:07, David G. Johnston <david.g.johnston@gmail.com> escreveu:On section Null-Valued Settings is not easy to understand that "show example.string" will result in an "ERROR: unrecognized...", that BEGIN or ROLLBACK does not return anything. So, what do you think about creating a table with two columns and showing command and result side by side ?
I think I'll replace the use of SHOW with current_setting throughout the entire example. That should increase the clarity and lets me return true/false with the column indicating the test - like the existing use of current_setting does.
David J.
p.s.
I really wish someone had gotten around to implementing \pset true 'true' and \pset false 'false' ... however we ended up with boolean outputs being "f" and "t" a bunch of people must have chosen to invest their life savings in eyewear companies.