Re: NULL as a (pseudo-)value not described? - Mailing list pgsql-docs
| From | David Johnston |
|---|---|
| Subject | Re: NULL as a (pseudo-)value not described? |
| Date | |
| Msg-id | CAKFQuwZVE3xkn3Wjic7L49XFkJ14HghuE01zPkCCPRbSX3KkNw@mail.gmail.com Whole thread Raw |
| In response to | NULL as a (pseudo-)value not described? (John Lumby <johnlumby@hotmail.com>) |
| Responses |
Re: NULL as a (pseudo-)value not described?
|
| List | pgsql-docs |
John, please respond to the list next time.
On Thu, Sep 11, 2014 at 9:24 AM, John Lumby <johnlumby@hotmail.com> wrote:
Thanks David ... but ... (below)
----------------------------------------
> Date: Wed, 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,
>> 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, the following syntax is apparently not permitted
>> :
>> UPDATE my_nullable_table SET nullable_col = NULL;
>>
>> (I am not referring to other null-related constructs such as IS NULL,
>> IFNULL etc)
>>
>> I have to assume I'm missing something?
>> Or is it that NULL is so, well, null, that it cannot be described?
>
> 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?
As 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 = null" is valid. SET col = <expression>, <constants> are <expressions>, null is a valid <constant>, ergo SET col = null is valid.
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. The trick is avoiding adding comments pertaining to NULL all over the documentation (see below) and confusing the underlying normal non-null usage. Thus, right now, only when NULL behavior is important does it get addressed. SET col = NULL is no different then SET col = 'a string' so why make it seem like a special case by pointing out the "obvious"?
Even if 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 = NULL" returns true or "NULL = 'some other literal'" returns FALSE: i.e., that NULL is never special but is just another literal.
>
> 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,
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.
Fair enough...my response was somewhat unorganized. Again, this supports the theory that it is assumed people know what null is and 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, in
> order, did you look?
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. Most 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. IOW, It would be nice to get to know the target audience.
David J.
pgsql-docs by date: