[CentralOH] Django/Postgresql unique_together with None/NULL Values

Eric Floehr eric at intellovations.com
Tue Dec 11 21:43:36 CET 2012


Jim,

On Tue, Nov 20, 2012 at 10:26 AM, <jep200404 at columbus.rr.com> wrote:

> What's a cleaner way of accomplishing the unique_together stuff,
> hopefully without the (redundant) previous_id_num attribute?
>
> I'm using Postgresql with Django. Postgresql's implementation of
> unique constraints when a value is NULL (None in Python) does not
> work as desired. I figured out an ugly workaround. Hopefully,
> you know a better way.
>


Null in a database sense means "missing data", and the SQL standard
specifically states that two NULL values are not equal. That's why the
unique-together stuff doesn't work as expected.  The thought being that
since the data point is "unknown" you can't ascribe equality to any two
unknown data points. For more than you ever wanted to know about NULL,
there is a fairly comprehensive Wikipedia article [1].

Now you might say, when I do group by or ordering in SQL, the NULL's are
grouped and ordered together.  How can that be in NULL != NULL? The SQL
standard gets around that by defining group by and order by as putting "not
distinct" items together.  And SQL defines "any two values that are equal
to one another, or any two Nulls" as being "not distinct". So while NULL
does not equal NULL, they are indistinct from one another as far as SQL
goes.


An (ugly) thought is to not use None/NULL values for previous to
> indicate end of linked list.
> Instead one would set up a sentinel dummy Book record for the end of
> a linked list. That would make unique_constraint happy, but push
> ugliness into the code that groks the linked list.
>


Not ugly at all, that is a standard way to do that in a relational
database.  Another common way is to use an order or positional field as
part of the unique-together rather than the index field. It would start at
1 (or 0) for the first item in the list, and increment as you add items.
 That works best if you are just adding to the end of the list, not
reordering, and it involves adding another field to the table and is
another field to maintain.

[1] http://en.wikipedia.org/wiki/Null_(SQL)

-Eric
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/centraloh/attachments/20121211/4de07b88/attachment.html>


More information about the CentralOH mailing list