[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
            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: 


What else am I missing? 

More information about the CentralOH mailing list