Skip site navigation (1) Skip section navigation (2)

Re: When is a record NULL?

From: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: <pgsql-hackers(at)postgresql(dot)org>, "Sam Mason" <sam(at)samason(dot)me(dot)uk>
Subject: Re: When is a record NULL?
Date: 2009-07-25 23:41:44
Message-ID: EE822D5E-C6F4-4D7F-9D07-B7773BC60D71@kineticode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Jul 25, 2009, at 9:42 AM, Kevin Grittner wrote:

> I know that Codd was insistent that any relation (which included the
> result of any query) which could contain duplicate rows should be
> called a "corrupted relation".  (In fact, in one of his books I think
> he averaged a comment on this point about once every two pages.)  So I
> shudder to think what his reaction would be to a relation with a row
> which contained no values.  I have a really hard time figuring out
> what useful information such a row could represent.

I agree that it's pathological, but it's clearly allowed by SQL, so we  
need to be able to deal with it effectively. Intuitively would be  
nice, but effectively will do.

Consider:

     CREATE TABLE peeps (
         name TEXT NOT NULL,
         dob date,
         ssn text,
         active boolean NOT NULL DEFAULT true
     );

     INSERT INTO peeps
     VALUES ('Tom', '1963-03-23', '123-45-6789', true),
            ('Damian', NULL, NULL, true),
            ('Larry',  NULL, '932-45-3456', true),
            ('Bruce',  '1965-12-31', NULL, true);

     % SELECT dob, ssn from peeps where active;
         dob     |     ssn
     ------------+-------------
      1963-03-23 | 123-45-6789
      [null]     | [null]
      [null]     | 932-45-3456
      1965-12-31 | [null]

Useless perhaps, but it's gonna happen, and someone may even have a  
reason for it. Until such time as NULLs are killed off, we need to be  
able to deal with SQL's pathologies.

Best,

David

In response to

Responses

pgsql-hackers by date

Next:From: Joshua TolleyDate: 2009-07-25 23:45:23
Subject: Re: [PATCH] DefaultACLs
Previous:From: David E. WheelerDate: 2009-07-25 23:20:17
Subject: Re: When is a record NULL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group