Opened 9 years ago
Closed 9 years ago
#25091 closed Bug (duplicate)
Array field equality lookup fails with ProgrammingError
Reported by: | Villiers Strauss | Owned by: | |
---|---|---|---|
Component: | contrib.postgres | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Note: This bug report is mostly based on a reddit post by Glueon: http://redd.it/38j43l
With the model:
class MyModel(models.Model): emails = ArrayField(models.EmailField())
When trying to fetch a row with a list of emails:
MyModel.objects.filter(emails=['test@test.com'])
the following error occurs:
ProgrammingError: operator does not exist: character varying[] = text[] HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Because the resulting query is:
SELECT * FROM some_table WHERE emails = ARRAY['test@test.com'];
By default type of ARRAY['test@test.com']
is text []
while Django stores it as a varchar []
.
Glueon suggests that casting the array to varchar []
using raw sql solves the problem:
SELECT * FROM some_table WHERE emails = ARRAY['test@test.com']::varchar[];
I did post some other workarounds in the comments of the reddit post, but there are some situations in which the workarounds are not possible, such as when using get_or_create
or update_or_create
.
Change History (6)
comment:1 by , 9 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 1.8 → master |
comment:2 by , 9 years ago
Here’s a ready-to-use workaround for get_or_create
, as described in this reddit post.
def bugfree_get_or_create(manager, **kwargs): """ This is workaround for https://code.djangoproject.com/ticket/25091 """ model = manager.model defaults = kwargs.pop('defaults', {}) for k, array in tuple(kwargs.items()): if isinstance(model._meta.get_field(k), ArrayField): for i, item in enumerate(array): kwargs[k + '__%d' % i] = item kwargs[k + '__len'] = len(array) del kwargs[k] try: return manager.get(**kwargs) except model.DoesNotExist: kwargs.update(defaults) return manager.create(**kwargs)
Instead of writing YourModel.objects.get_or_create(your_array_field=['abc', 'def'], other_kwargs=…)
, write bugfree_get_or_create(YourModel.objects, your_array_field=['abc', 'def'], other_kwargs=…)
.
comment:3 by , 9 years ago
I would like to add a notice that the code above is not even close to be as bullet proof as get_or_create
and you should expect race conditions if you were to use it.
comment:4 by , 9 years ago
I have found two simple workarounds to this, but they may have some issues that I am not aware of. The first is to pass the array to the get or get_or_create method not as a list, but as a string formatted using
'{1.0, 2.0}'
instead of
ARRAY[1.0, 2.0]
which causes postgres to handle an implicit cast from numeric to real (which I needed in this case).
The other is to perform this conversion in get_db_prep_value()
by overriding the ArrayField class. For my specific case, this appears to work, but it may not be robust to arrays of different types (especially strings):
class StrFormatArrayField(ArrayField): """ Override the array format to use "'{1.0, 2.0, 3.0}'" instead of "ARRAY([1.0, 2.0, 3.0])" to get around an issue with implicit type conversion. """ def get_db_prep_value(self, value, connection, prepared=False): if isinstance(value, list) or isinstance(value, tuple): prepped = [self.base_field.get_db_prep_value(i, connection, prepared) for i in value] joined = ', '.join([str(v) for v in prepped]) return '{' + joined + '}' else: return value
Any advice on improving upon this or issues I may face would be greatly appreciated.
comment:6 by , 9 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
#25666 is a duplicate with a patch.
This seems related to #24726.