#36418 closed Bug (invalid)
bulk_update None on JSON field sets the value as "null" string in Postgresql
Reported by: | Amit Maniar | Owned by: | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 5.2 |
Severity: | Normal | Keywords: | |
Cc: | Simon Charette, Sage Abdullah | Triage Stage: | Unreviewed |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Updating JSON field of Postgresql table to None stores "null" as a string when using bulk_update.
Model in models.py
# Create model in DB class TestModel(models.Model): created_at = models.DateTimeField(null=False) label = models.CharField(max_length=64) json_row = models.JSONField(null=False) def __str__(self) -> str: return self.label
from django.utils import timezone # Create few records in database TestModel.objects.create(created_at=timezone.now(), label='test label 1', json_row={'key1': 'value1', 'key2': 1234}) TestModel.objects.create(created_at=timezone.now(), label='test label 2', json_row={'key1': 'value1', 'key2': 5678}) # Create a list to update the records test_list = [] test_list.append(TestModel.objects.get(id=1)) # update JSON field to None test_list[0].json_row = None # Bulk update objects in fields. TestModel.objects.bulk_update(test_list, fields=['json_row']) # Check DB records, json_row of TestModel (id=1) will be "null" (null as a string value)
Change History (6)
comment:3 by , 3 months ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
follow-up: 6 comment:4 by , 3 months ago
Hello Amit Maniar, thank you for this ticket. I think the description lacks some clarity: could you please provide concrete details, ideally a failing test, about what do you expect to find in the DB record for the example that you provided?
I believe that Clifford understood as that you may be reporting that "null"
(with the double quotes) is being stored, but I think you meant that null
as the string is stored (instead of an actual SQL NULL
). I can replicate getting the json's null:
django-test=# select * from testapp_ticket36418model where json_row is NULL; id | label | json_row ----+-------+---------- (0 rows) django-test=# select * from testapp_ticket36418model where json_row = 'null'; id | label | json_row ----+--------------+---------- 1 | test label 1 | null (1 row)
Clifford has kindly created #36419 to follow up on the second half of this.
comment:5 by , 3 months ago
Cc: | added |
---|
follow-up: 7 comment:6 by , 3 months ago
Hi Natalia Bidart and Clifford, Apologies for the confusion. I was reporting that null
as the string is stored instead of an actual SQL NULL
. I used double quotes around null to specify null
as a string value.
Thanks for creating #36419.
Replying to Natalia Bidart:
Hello Amit Maniar, thank you for this ticket. I think the description lacks some clarity: could you please provide concrete details, ideally a failing test, about what do you expect to find in the DB record for the example that you provided?
I believe that Clifford understood as that you may be reporting that
"null"
(with the double quotes) is being stored, but I think you meant thatnull
as the string is stored (instead of an actual SQLNULL
). I can replicate getting the json's null:
django-test=# select * from testapp_ticket36418model where json_row is NULL; id | label | json_row ----+-------+---------- (0 rows) django-test=# select * from testapp_ticket36418model where json_row = 'null'; id | label | json_row ----+--------------+---------- 1 | test label 1 | null (1 row)Clifford has kindly created #36419 to follow up on the second half of this.
comment:7 by , 3 months ago
Replying to Amit Maniar:
Thanks for the follow-up, Amit.
I was reporting that
null
as the string is stored instead of an actual SQLNULL
. I used double quotes around null to specifynull
as a string value.
Ah, I see. Please note that an SQL NULL
wouldn't have been possible in your example as null=False
is set (unless you were expecting an IntegrityError
to be raised), which is why it was a little confusing. The null
you are seeing in the database is JSON scalar null, which is an explicit "empty value" marker in JSON syntax ('null'::jsonb). The string (which I had misunderstood you meant) would be '"null"'::jsonb
.
Fixing #36419 should make bulk updating json_row=None
store an SQL NULL
instead of JSON null, which would raise an IntegrityError
in your example.
Hello, Amit. Thanks for taking the time to create this ticket.
I can't reproduce with:
tests/queries/models.py
tests/queries/test_bulk_update.py
on PostgreSQL 16.8 and Django's main branch. I get a JSON null instead of a
"null"
string. Are you sure you're not getting the same?Sidenote: that this saves a JSON null is surprising since JSON null is supposed to be stored through
Value(None, JSONField())
as documented here. I'd have expected this to use SQLNULL
instead, raisingIntegrityError
, likeupdate()
andsave()
on JSONField withnull=False
.