1. Matthew Schinckel
  2. django-jsonfield
Issue #13 resolved

IntegrityErrors when using empty string values under Postgres

inactivist
created an issue

Is it acceptable to use an empty string value (or None) for a JSONField with default parameters in constructor? I keep running into problems in my tests:

{{{models.py}}}: {{{

!python

import jsonfield from django.db import models

class TestModel(models.Model): field1 = jsonfield.JSONField()

}}} {{{test.py}}}: {{{

!python

from django.test import TestCase from fieldtest.models import TestModel

class SimpleTest(TestCase): def test_jsonfield(self): x = TestModel() x.field1='' x.save()

}}} I get this result when running django tests: {{{ ~/sites/django-jsonfield-test/jsonfield_test$ python manage.py test

ERROR: test_jsonfield (fieldtest.tests.SimpleTest)

Traceback (most recent call last): File "/home/inactivist/sites/django-jsonfield-test/jsonfield_test/fieldtest/tests.py", line 17, in test_jsonfield x.save() File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/base.py", line 460, in save self.save_base(using=using, force_insert=force_insert, force_update=force_update) File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/base.py", line 553, in save_base result = manager._insert(values, return_id=update_pk, using=using) File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/manager.py", line 195, in _insert return insert_query(self.model, values, **kwargs) File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/query.py", line 1436, in insert_query return query.get_compiler(using=using).execute_sql(return_id) File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 791, in execute_sql cursor = super(SQLInsertCompiler, self).execute_sql(None) File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 735, in execute_sql cursor.execute(sql, params) File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute return self.cursor.execute(query, args) IntegrityError: null value in column "field1" violates not-null constraint }}}

If I use the Django admin site to add a new {{{TestModel}}} instance with an empty {{{field1}}} value, Django admin reports: {{{This field cannot be null.}}} But I did not use {{{null=True}}} so according to the Django docs (https://docs.djangoproject.com/en/dev/ref/models/fields/#null says: //Note that empty string values will always get stored as empty strings, not as NULL.//) -- and a JSONField is fundamentally a string value as far as database storage is concerned, isn't it? === Version Info === Ubuntu 11.04 x86, Postgres 8.4.11, Python 2.7.1

{{{requires.txt}}}: {{{ django==1.3.1 django-jsonfield==0.8.5 psycopg2==2.4.1 }}}

(For Django test suite purposes I'm using psycopg2 2.4.1 because newer versions cause problems with Django TestRunner. See https://code.djangoproject.com/ticket/16250)

I can attach my test Django project if that'll help.

Comments (19)

  1. inactivist reporter

    It is my expectation, since Django docs say that null=False by default on model fields, and that empty string values are not stored as NULL, that a blank (empty) value is (or should be) acceptable with a JSONField that uses a default constructor as is used in my test code (field1 = JSONField()).

    Or have I gone off the road here?

  2. Matthew Schinckel repo owner

    It's to do with the to_python method, that explicitly converts an empty string to None.

    The rationale behind this is that json.loads('') is invalid.

    I'm not quite sure what should be the best way to handle this at this stage.

  3. inactivist reporter

    I neglected to mention that the same problem occurs if I set blank=True when I declare my JSONField:

    models.py:

    import jsonfield
    from django.db import models
    
    class TestModel(models.Model):
        field1 = jsonfield.JSONField(blank=True)
    

    tests.py:

    from django.test import TestCase
    from fieldtest.models import TestModel
    
    class SimpleTest(TestCase):
        def test_jsonfield(self):
            x = TestModel()
            x.field1=''
            x.save()
    

    And the test results:

    ~/sites/django-jsonfield-test/jsonfield_test$ python manage.py test
    Creating test database for alias 'default'...
    ........................................................................................................................................................................................................................................................................E..........................................................
    ======================================================================
    ERROR: test_jsonfield (fieldtest.tests.SimpleTest)
    ----------------------------------------------------------------------
    Traceback (most recent call last):
      File "/home/inactivist/sites/django-jsonfield-test/jsonfield_test/fieldtest/tests.py", line 9, in test_jsonfield
        x.save()
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/base.py", line 460, in save
        self.save_base(using=using, force_insert=force_insert, force_update=force_update)
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/base.py", line 553, in save_base
        result = manager._insert(values, return_id=update_pk, using=using)
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/manager.py", line 195, in _insert
        return insert_query(self.model, values, **kwargs)
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/query.py", line 1436, in insert_query
        return query.get_compiler(using=using).execute_sql(return_id)
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 791, in execute_sql
        cursor = super(SQLInsertCompiler, self).execute_sql(None)
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 735, in execute_sql
        cursor.execute(sql, params)
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
        return self.cursor.execute(query, args)
    IntegrityError: null value in column "field1" violates not-null constraint
    
    
    ----------------------------------------------------------------------
    Ran 323 tests in 15.325s
    
    FAILED (errors=1)
    Destroying test database for alias 'default'...
    

    And Django's admin site reports an uncaught IntegrityError if I try to add a new TestModel instance with an empty field1 value.

    Traceback:
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
      111.                         response = callback(request, *callback_args, **callback_kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in wrapper
      307.                 return self.admin_site.admin_view(view)(*args, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
      93.                     response = view_func(request, *args, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
      79.         response = view_func(request, *args, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/contrib/admin/sites.py" in inner
      197.             return view(request, *args, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapper
      28.             return bound_func(*args, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
      93.                     response = view_func(request, *args, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/utils/decorators.py" in bound_func
      24.                 return func(self, *args2, **kwargs2)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/transaction.py" in inner
      217.                 res = func(*args, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in add_view
      882.                 self.save_model(request, new_object, form, change=False)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/contrib/admin/options.py" in save_model
      665.         obj.save()
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/base.py" in save
      460.         self.save_base(using=using, force_insert=force_insert, force_update=force_update)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/base.py" in save_base
      553.                     result = manager._insert(values, return_id=update_pk, using=using)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/manager.py" in _insert
      195.         return insert_query(self.model, values, **kwargs)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/query.py" in insert_query
      1436.     return query.get_compiler(using=using).execute_sql(return_id)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
      791.         cursor = super(SQLInsertCompiler, self).execute_sql(None)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
      735.         cursor.execute(sql, params)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/backends/util.py" in execute
      34.             return self.cursor.execute(sql, params)
    File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py" in execute
      44.             return self.cursor.execute(query, args)
    
    Exception Type: IntegrityError at /admin/fieldtest/testmodel/add/
    Exception Value: null value in column "field1" violates not-null constraint
    
  4. inactivist reporter

    Matthew Henderson: OK, your comment about json.loads('') being invalid makes sense. I guess my confusion comes from the Django docs and convention that string values are always stored as an empty string, and that JSONFields are stored as text strings.

    Edit: I can set blank=True, null=True and everything works as expected when I set field1='' -- which is another bit of confusion; if an empty value is invalid JSON, that is.

  5. Matthew Schinckel repo owner
    • changed status to open

    I think my rationale behind that was purely that when re-loading data from the database, stuff that is stored as an empty string is invalid, but a NULL value is valid.

    It may be that the code that handles that needs revisiting, or that it is just a documentation thing.

  6. inactivist reporter

    I think that it could be handled in documentation for now. More examples in the documentation would help a lot, as I would have seen that empty strings aren't used.

    I'm not sure how the NULL/blank issue ought to be dealt with internally, though personally I would prefer to see consistency between JSONField's and TextField's validation (and NULL usage guidelines per the Django docs) for empty strings based on the current null= and blank= settings, since JSONField is based on TextField -- that's the mental model that led to my confusion and this issue report.

    I can deal with ensuring that my code does the right thing with empty values once I know the rules, but it's nice to be able to use the admin pages to edit models, and indeed, that's often the first method used to generate model instances during testing.

    The main issue for me is how Django's form validation handles the blank JSONFields instantiated with blank=True, null=False parameters -- JSONField prevented me from using default (blank) JSONField values in Django admin forms, and the Django admin barfed on the exception.

    (Edit: Clarifying my response -- I realize that I need to set blank=True if I want to use empty values on both TextField and JSONField, but on JSONField I also need to set null=True when I use blank=True, else I'll get IntegrityErrors on save(), which isn't the case with TextFields.)

  7. inactivist reporter

    Here's a concise test case that fails with django-jsonfield 0.8.5:

    # models.py
    
    import jsonfield
    from django.db import models
    
    class TestModel(models.Model):
        textfield = models.TextField(blank=True)
        jsonfield = jsonfield.JSONField(blank=True)
    
    #tests.py
    
    from django.test import TestCase
    from fieldtest.models import TestModel
    
    class SimpleTest(TestCase):
        def test_textfield(self):
            x = TestModel()
            x.textfield=''
            x.save()
        
        def test_jsonfield(self):
            x = TestModel()
            x.jsonfield=''
            x.save()
            
    

    Results:

    ERROR: test_jsonfield (fieldtest.tests.SimpleTest)
    ----------------------------------------------------------------------
    Traceback (most recent call last):
    [snip]
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
        return self.cursor.execute(query, args)
    IntegrityError: null value in column "jsonfield" violates not-null constraint
    
    
    ======================================================================
    ERROR: test_textfield (fieldtest.tests.SimpleTest)
    ----------------------------------------------------------------------
    Traceback (most recent call last):
    [snip]
      File "/home/inactivist/sites/django-jsonfield-test/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
        return self.cursor.execute(query, args)
    IntegrityError: null value in column "jsonfield" violates not-null constraint
    
    
    ----------------------------------------------------------------------
    Ran 324 tests in 15.607s
    
    FAILED (errors=2)
    
    

    I expect both test cases to pass; both fail because you can't save a TestModel instance with a default (blank) value for jsonfield. Adding the null=True option to TestModel.jsonfield resolves the issue but I didn't think that should be necessary.

  8. Matthew Schinckel repo owner

    I've committed some code that automatically converts "" to None in cases where null=True, and None to "", in cases where blank=True (and not null=True).

    This passes your tests above, but has the side effect of the data being fetched back from the db not matching that which was saved.

    I think it should throw a validation error when a null value is passed to a field that has null=False, and same when a blank value on blank=False.

  9. Matthew Schinckel repo owner

    Been a while since I looked at this. I believe I have committed some code (and maybe dropped some new releases) that might resolve this.

    The thing I see is that JSONField will be like INET field: it is invalid to store an empty string. In cases like this, null=True is appropriate. In this case, it's because json.loads('') will fail, but in INET's case (in Postgres, at least), the database will reject an empty string.

    Newer versions of PG are likely to choke on a string, if and when I support the native JSON datatype in the DBMS.

    That's all without the snarky side-note about NULL actually being useful in a DBMS. If you store empty strings in a field instead of NULL, you cannot stick a UNIQUE constraint on that field, and have multiple empty values. This is actually very useful, IMHO.

  10. Matthew Schinckel repo owner

    I'm actually thinking that you _need_ to have at least one of (null=True, blank=True or default=<something>).

    I've implemented something that causes an AssertionError when attempting to add to a class a field that does not have at least one of these three attributes in a valid state.

  11. inactivist reporter

    I agree with your reasoning -- the AssertionError is helpful, I think. In any case, thanks for investigating and providing feedback.

  12. Log in to comment