#28291: ArrayField cannot contain JSONField; causes SQL error
----------------------------------+--------------------------------------
Reporter: Richard Eames | Owner: Zac Yauney
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------
Changes (by David Hagen):
* cc: David Hagen (added)
Comment:
I did some investigation to figure out why this happens because it is kind
of annoying to be unable to reuse `Field`s backed by `JsonField` in
`ArrayField`s.
The problem comes in the call to `psycopg2.extensions.cursor.execute(sql,
params)` in `django.db.backends.utils.CursorWrapper._execute` (line 85 in
Django 2.0). In this example, `execute` is called with `sql = ... VALUES
(%s) ...` and `params = [[JsonAdaptor({"a": 1}), JsonAdaptor({"a": 1})]]`,
where `JsonAdaptor` is `django.contrib.postgres.fields.JsonAdaptor` which
is a thin wrapper around `psycopg2._json.Json`. Now, `execute` is C code
in `psycopg2` which expands the SQL command to `... VALUES (ARRAY['{"a":
1}', '{"b": 2}'])`. It gets those string representations of the JSON by
calling `Json.getquoted`. If the field has type `jsonb` then the quoted
string appears to be a perfectly valid value to insert into the table.
However, if the field has type `jsonb[]`, then an array of quoted strings
does not appear to be a valid value to insert, which is why the error is
raised. The solution is to either cast the strings to `jsonb` or to cast
the whole array to `jsonb[]` like this:
{{{
... VALUES (ARRAY['{"a": 1}'::jsonb, '{"b": 2}'::jsonb]) ...
... VALUES (ARRAY['{"a": 1}', '{"b": 2}']::jsonb[]) ...
}}}
Whether the bug fundamentally lies with psycopg2 or django depends on
whose duty it is to make sure the conversion to string properly casts to
the right type, and I don't know that.
--
Ticket URL: <https://code.djangoproject.com/ticket/28291#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.a7c597591926433b144722a7cd5e828c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
