Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XSFW0-0000PV-HR for pgsql-docs@arkaria.postgresql.org; Fri, 12 Sep 2014 01:19:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XSFW0-0000Dl-1V for pgsql-docs@arkaria.postgresql.org; Fri, 12 Sep 2014 01:19:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XSFVz-0000Df-74 for pgsql-docs@postgresql.org; Fri, 12 Sep 2014 01:19:07 +0000 Received: from blu004-omc3s7.hotmail.com ([65.55.116.82]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XSFVt-0000w6-KE for pgsql-docs@postgresql.org; Fri, 12 Sep 2014 01:19:06 +0000 Received: from BLU436-SMTP48 ([65.55.116.72]) by BLU004-OMC3S7.hotmail.com over TLS secured channel with Microsoft SMTPSVC(7.5.7601.22724); Thu, 11 Sep 2014 18:18:58 -0700 X-TMN: [7Z9fdKgZY2SfOYGzwurlTNc4MtboN6YX] X-Originating-Email: [johnlumby@hotmail.com] Message-ID: Received: from threlglg.localdomain ([69.165.169.141]) by BLU436-SMTP48.smtp.hotmail.com over TLS secured channel with Microsoft SMTPSVC(8.0.9200.16384); Thu, 11 Sep 2014 18:18:55 -0700 Date: Thu, 11 Sep 2014 21:24:26 -0400 From: johnlumby User-Agent: Mozilla/5.0 (X11; Linux i686; rv:17.0) Gecko/20130625 Thunderbird/17.0.7 MIME-Version: 1.0 To: David Johnston , "pgsql-docs@postgresql.org" Subject: Re: NULL as a (pseudo-)value not described? References: <1410398400332-5818543.post@n5.nabble.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------090809050002030302050308" X-OriginalArrivalTime: 12 Sep 2014 01:18:55.0795 (UTC) FILETIME=[85A03830:01CFCE27] X-Pg-Spam-Score: -3.4 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --------------090809050002030302050308 Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: quoted-printable On 09/11/14 10:03=2C David Johnston wrote: > John=2C please respond to the list next time. Sorry=2C I was not sure if I understood what you were saying. Now I do ... > > > On Thu=2C Sep 11=2C 2014 at 9:24 AM=2C John Lumby > wrote: > > Thanks David ... but ... (below) > > ---------------------------------------- > > Date: Wed=2C 10 Sep 2014 18:20:00 -0700 > > From: david.g.johnston@gmail.com > > To: pgsql-docs@postgresql.org > > Subject: Re: [DOCS] NULL as a (pseudo-)value not described? > > > > johnlumby wrote > >> As far as I can tell=2C > >> the un-value NULL is not described anywhere in the doc > >> as being something that can be assigned to a column. > >> > >> E.g. : > >> . not listed as a constant (well that's reasonable as it > isn't) > >> . not listed as an expression (is NULL an expression?) > >> . not explicitly listed under the INSERT and UPDATE commands > >> as a valid syntactic unit as an alternative to an > expression > >> (If it is not an expression) > >> > >> I do see it listed as a valid keyword but there is no > description there. > >> > >> To put it another way=2C the following syntax is apparently not > permitted > >> : > >> UPDATE my_nullable_table SET nullable_col =3D NULL=3B > >> > >> (I am not referring to other null-related constructs such as IS > NULL=2C > >> IFNULL etc) > >> > >> I have to assume I'm missing something? > >> Or is it that NULL is so=2C well=2C null=2C that it cannot be > described?=20 > > > > > http://www.postgresql.org/docs/9.4/static/ddl-default.html > > > > To your point: null is a constant/literal as defined in > > > > > http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX= -EXPRESS-EVAL > > Well I have searched that page from top to bottom > and nowhere do I see such a statement. There are many mentions > of the word "NULL" and "null" but as far as I can tell none of these > refer to to the use of the word as a substitute for a value > such as occurs in a column assignment in an INSERT or UPDATE command. > > Can you please cut'n'paste the relevant text that you see that > states this? > > > =E2=80=8BAs I mentioned before the fact that null is a literal/constant i= s=20 > assumed. My point here is that given that assumption the referenced=20 > section explains that "SET col =3D null" is valid. SET col =3D=20 > =2C are =2C null is a valid=20 > =2C ergo SET col =3D null is valid. Ah=2C ok. Well=2C I would say that NULL is not a constant=2C since a constant has a well-defined value and can be used to represent that value in any expression=2C neither of which are true of NULL. However see my last para. > > The documentation does not describe all possible valid constants -=20 > though I admit given the special nature of NULL it probably should do=20 > so in this instance. The trick is avoiding adding comments pertaining=20 > to NULL all over the documentation (see below) and confusing the=20 > underlying normal non-null usage. Thus=2C right now=2C only when NULL=20 > behavior is important does it get addressed. SET col =3D NULL is no=20 > different then SET col =3D 'a string' so why make it seem like a=20 > special case by pointing out the "obvious"? > > Even if people are not positive simply trying what you wrote is easy=20 > and in the absence of any error it would become obvious that NULL is=20 > valid in UPDATE/SET. > > The larger problem is people thinking "NULL =3D NULL" returns true or=20 > "NULL =3D 'some other literal'" returns FALSE: i.e.=2C that NULL is never= =20 > special but is just another literal. Actually I have seen mailings where someone is asking how to set a=20 column value to NULL so I'd say it is not obvious. A reference manual should preferably=20 avoid assuming anything about what it is documenting. > > > > > > and so can appear in the defined expression area and so your > example is > > explicitly allowed. > > > > > http://www.postgresql.org/docs/9.4/static/bookindex.html#AEN167062 > (under > > "N") > > Likewise -- 8 instances of the nullness concept=2C > none of which refer to to the use of the word as a substitute for > a value > > such as occurs in a column assignment in an INSERT or UPDATE command. > > > =E2=80=8BFair enough...my response was somewhat unorganized.=E2=80=8B Ag= ain=2C this=20 > supports the theory that it is assumed people know what null is and=20 > simply need to see how PostgreSQL specifically treats it. > > > > > Pseudo-type has a specific meaning is PostgreSQL which null does > not conform > > to. > > > > Having brought this to attention why did you go looking for it > and where=2C in > > order=2C did you look? > > > =E2=80=8B I've already agreed the documentation could be improved - but s= ince=20 > you are the one who brought this up it would be helpful to get your=20 > thoughts on how best to do that. Most everyone on this list=20 > intimately understands how NULL works so while we can write accurate=20 > documentation it is more difficult to write/organize the documentation=20 > for the purposes of learning. IOW=2C It would be nice to get to know=20 > the target audience. Well=2C I would assume that one or other of the SQL ANSI standards=20 documents NULL and (provided postgresql confirms to that standard) we can simply copy=20 or rephrase that text. However=2C I personally am not a standards expert=2C and afte= r=20 a short and futile search on on the web I can't find any accessible html/pdf=20 format of any SQL ANSI standard. I assume someone more knowledgeable on standards could find it. But if I had to take a shot at it=2C I would add a description of NULL at= =20 the bottom of section 4.2 following the text : "In addition to this list=2C there are a number of constructs that can= =20 be classified as an expression but do not follow any general syntax rules. These generally have the semantics of a function or operator and=20 are explained in the appropriate location in Chapter 9. An example is the IS=20 NULL clause." something along these lines : Another example is the NULL token. This is not a value but can=20 be included in certain commands where a value can be assigned to a column=2C such as INSERT (the=20 VALUES clause) and UPDATE (the SET clause)=2C in the same position as a value=20 would be placed=2C provided the column is not defined as NOT NULL. Its meaning is=20 "no value". It is not equal to any other value=2C and not unequal to any other= =20 value=2C and not equal to another NULL=2C and not unequal to another NULL. NULL may not be stated as an explicit default value for a column=2C but if no default value is defined for a column which is not=20 defined as NOT NULL=2C then its default value is NULL. In this case=2C NULL has the=20 sense of "not set". It cannot be used in an expression other than when it occurs=20 alone without any associated operator or function. > > David J. > > > =E2=80=8B --------------090809050002030302050308 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On 09/11/14 10:03=2C David Johnston wrote:
John=2C plea= se respond to the list next time.

Sorry=2C=C2=A0 I was not sure if I understood what you were saying.
Now I do ...



On Thu=2C Sep 11=2C 2014 at 9:24 AM=2C= John Lumby <=3Bjoh= nlumby@hotmail.com>=3B wrote:
Thanks David=C2=A0 ...=C2=A0 but=C2=A0 ... (below)

----------------------------------------
>=3B Date: Wed=2C 10 Sep 2014 18:20:00 -0700
>=3B From: david.g.johnston= @gmail.com
>=3B To: pgsql-docs@postgr= esql.org
>=3B Subject: Re: [DOCS] NULL as a (pseudo-)value not described?
>=3B
>=3B johnlumby wrote
>=3B>=3B As far as I can tell=2C
>=3B>=3B the un-value NULL is not described anywhere in the doc
>=3B>=3B as being something that can be assigned to a column.
>=3B>=3B
>=3B>=3B E.g. :
>=3B>=3B=C2=A0 =C2=A0 .=C2=A0 not listed as a constant=C2= =A0 (well that's reasonable as it isn't)
>=3B>=3B=C2=A0 =C2=A0 =C2=A0.=C2=A0 not listed as an expr= ession=C2=A0 (is NULL an expression?)
>=3B>=3B=C2=A0 =C2=A0 =C2=A0.=C2=A0 not explicitly listed= under the INSERT and UPDATE commands
>=3B>=3B=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0as a valid synt= actic unit as an alternative to an expression
>=3B>=3B=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(If it is not a= n expression)
>=3B>=3B
>=3B>=3B I do see it listed as a valid keyword but there is no description there.
>=3B>=3B
>=3B>=3B To put it another way=2C=C2=A0 =C2=A0the followi= ng syntax is apparently not permitted
>=3B>=3B :
>=3B>=3B=C2=A0 =C2=A0 =C2=A0 =C2=A0UPDATE my_nullable_tab= le SET nullable_col =3D NULL=3B
>=3B>=3B
>=3B>=3B (I am not referring to other null-related constructs such as IS NULL=2C
>=3B>=3B IFNULL etc)
>=3B>=3B
>=3B>=3B I have to assume I'm missing something?
>=3B>=3B Or is it that NULL is so=2C=C2=A0 well=2C=C2=A0 = null=2C=C2=A0 =C2=A0that it cannot be described?=C2=A0
>=3B
>=3B http://www.postgresql.org/docs/9.4/static= /ddl-default.html
>=3B
>=3B To your point: null is a constant/literal as defined in
>=3B
>=3B http://www.postgresql.org/docs/9.4/static= /sql-expressions.html#SYNTAX-EXPRESS-EVAL

Well I have searched that page from top to bottom
and nowhere do I see such a statement.=C2=A0=C2=A0=C2=A0 Ther= e are many mentions
of the word "NULL" and "null" but as far as I can tell none of these
refer to to the use of the word as a substitute for a value
such as occurs in a column assignment in an INSERT or UPDATE command.

Can=C2=A0 you please cut'n'paste the relevant text that you s= ee that states this?

=E2=80= =8BAs I mentioned before the fact that null is a literal/constant is assumed. My point here is that given that assumption the referenced section explains that "SET col =3D null" is valid. =C2=A0SET col =3D <=3Bexpression>=3B=2C <=3Bconstants>=3B are <=3Bexpressions>=3B=2C null is a valid <=3Bconstant&g= t=3B=2C ergo SET col =3D null is valid.

Ah=2C=C2=A0 ok.=C2=A0=C2=A0=C2=A0 Well=2C=C2=A0=C2=A0 I would say that = NULL is not a constant=2C
since a constant has a well-defined value and can be used
to represent that value in any expression=2C=C2=A0 neither of which
are true of NULL.=C2=A0=C2=A0 However see my last para.


The documentation does not describe all possible valid constants - though I admit given the special nature of NULL it probably should do so in this instance. =C2=A0The trick is avoiding adding comments pertaining to NULL all over the documentation (see below) and confusing the underlying normal non-null usage. =C2=A0Thus=2C right now= =2C only when NULL behavior is important does it get addressed. =C2=A0SET col =3D NULL is no different then SET col =C2=A0= =3D 'a string' so why make it seem like a special case by pointing out the "obvious"?

Even i= f people are not positive simply trying what you wrote is easy and in the absence of any error it would become obvious that NULL is valid in UPDATE/SET.

The larger problem is people thinking "NULL =3D NULL" returns true or "NULL =3D 'some other literal'" returns FALSE: i.e.=2C that NULL is never special but is just another literal.

Actually I have seen mailings where someone is asking how to set a column value to NULL
so I'd say it is not obvious.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 A reference= manual should preferably avoid
assuming anything about what it is documenting.

=C2=A0

>=3B
>=3B and so can appear in the defined expression area and so your example is
>=3B explicitly allowed.
>=3B
>=3B http://www.postgresql.org/docs/9.4/static= /bookindex.html#AEN167062 (under
>=3B "N")

Likewise --=C2=A0=C2=A0=C2=A0 8 instances of the nullness con= cept=2C
none of which refer to to the use of the word as a substitute for a value

such as occurs in a column assignment in an INSERT or UPDATE command.


=E2=80= =8BFair enough...my response was somewhat unorganized.=E2=80=8B =C2= =A0Again=2C this supports the theory that it is assumed people know what null is and simply need to see how PostgreSQL specifically treats it.

=C2=A0
>=3B
>=3B Pseudo-type has a specific meaning is PostgreSQL which null does not conform
>=3B to.
>=3B
>=3B Having brought this to attention why did you go looking for it and where=2C in
>=3B order=2C did you look?

=E2=80=8B I've already agreed the documentation could be improved - but since you are the one who brought this up it would be helpful to get your thoughts on how best to do that. =C2=A0Most everyone on this list intimately understands how NULL works so while we can write accurate documentation it is more difficult to write/organize the documentation for the purposes of learning. =C2=A0IOW=2C It would be nice= to get to know the target audience.

Well=2C=C2=A0=C2=A0 I would assume that one or other of the SQL ANSI st= andards documents NULL
and (provided postgresql confirms to that standard) we can simply copy or rephrase
that text.=C2=A0=C2=A0 However=2C=C2=A0 I personally am not a standards= expert=2C and after a short
and futile search on on the web I can't find any accessible html/pdf format of any SQL ANSI standard.
I assume someone more knowledgeable on standards could find it.
But if I had to take a shot at it=2C=C2=A0 I would add a description of NULL at the bottom of section 4.2
following the text :
=C2=A0=C2=A0 "In addition to this list=2C there are a number of constru= cts that can be classified
=C2=A0=C2=A0=C2=A0=C2=A0 as an expression but do not follow any general= syntax rules.
=C2=A0=C2=A0=C2=A0=C2=A0 These generally have the semantics of a functi= on or operator and are explained
=C2=A0=C2=A0=C2=A0=C2=A0 in the appropriate location in Chapter 9. An e= xample is the IS NULL clause."

something along these lines :

<=3Bstart=C2=A0 addition>=3B
=C2=A0=C2=A0=C2=A0=C2=A0 Another example is the NULL token.=C2=A0=C2=A0= =C2=A0=C2=A0 This is not a value but can be included in certain commands
=C2=A0=C2=A0=C2=A0=C2=A0 where a value can be assigned to a column=2C= =C2=A0 such as INSERT (the VALUES clause)
=C2=A0=C2=A0=C2=A0 and UPDATE (the SET clause)=2C=C2=A0 =C2=A0 =C2=A0 i= n the same position as a value would be placed=2C
=C2=A0=C2=A0=C2=A0 provided the column is not defined as NOT NULL.=C2= =A0=C2=A0=C2=A0 Its meaning is "no value".
=C2=A0=C2=A0=C2=A0=C2=A0 It is not equal to any other value=2C=C2=A0=C2= =A0 and not unequal to any other value=2C
=C2=A0 =C2=A0 and not equal to another NULL=2C=C2=A0 and not unequal to= another NULL.
=C2=A0=C2=A0=C2=A0=C2=A0 NULL may not be stated as an explicit default = value for a column=2C
=C2=A0 =C2=A0 =C2=A0 but if no default value is defined for a column wh= ich is not defined as NOT NULL=2C
=C2=A0 =C2=A0 =C2=A0 then its default value is NULL.=C2=A0=C2=A0=C2=A0 = In this case=2C=C2=A0 NULL has the sense of "not set".
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <=3Bis the next true?=C2=A0 I am not s= omewhere to verify ...?>=3B
=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 It cannot be used in an expression othe= r than when it occurs alone without any associated operator or function.
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <=3B/is the above true?=C2=A0 I am not= somewhere to verify ...?>=3B
=C2=A0 <=3B/end addition>=3B


David J.


=E2=80=8B
=C2=A0

--------------090809050002030302050308--