[CentralOH] Django/Postgresql unique_together with None/NULL Values
jep200404 at columbus.rr.com
jep200404 at columbus.rr.com
Tue Nov 20 16:26:41 CET 2012
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.
def none_to_zero(previous):
return previous.id if previous else 0
class Book(models.Model):
class Meta:
'''
From Postgresql dox[1] about unique constraints:
However, two null values are not considered equal in this
comparison. That means even in the presence of a unique
constraint it is possible to store duplicate rows that
contain a null value in at least one of the constrained
columns. This behavior conforms to the SQL standard, ...
That behavior is undesirable.
unique_together must not allow two records that have
the same name and the same 'previous' value of None/NULL.
Because of that, 'previous' can not be used in unique_together.
The workaround is to use a redundant attribute, 'previous_id_num',
in unique_together that uses 0 instead of None/NULL.
[1] At bottom of section 5.3.3. Unique Constraints
http://www.postgresql.org/docs/devel/static/ddl-constraints.html
'''
unique_together = (('name', 'previous_id_num'),)
name = FixedCharField(max_length=35, null=True, blank=True)
stuff = models.CharField(null=True, blank=True, max_length=2000)
'''
Versioning of Book Data
From time to time, the existing book data is updated.
So for a book as indentified by a particular name
there may be multiple versions.
We preserve all versions in the database.
In the following, we discuss the various versions indentified
by a particular name.
A singly-linked list of records is maintained from newer to older
versions. The newest version is the head and the oldest version is
the tail. For the oldest version, the previous link will be None (NULL).
'''
previous = models.ForeignKey('self', null=True, blank=True, db_index=True)
previous_id_num = models.IntegerField() # Filled by pre_save().
@receiver(pre_save, sender=Book)
def do_book_pre_save_stuff(sender, **kwargs):
book = kwargs['instance']
book.previous_id_num = none_to_zero(book.previous)
Additionally with updates, if 'previous' is updated,
I need to remember to update 'previous_id_num' also.
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.
The closest thing I've found on the web is:
http://stackoverflow.com/questions/1006617/models-unique-together-constraint-none-fail
What else am I missing?
More information about the CentralOH
mailing list