I am using an ArrayField to store a list of dates. I would like to use update() to add a value to the list, only if the list doesn't contain it already.
The contains part is already supported by Django, but not the array_append .
​ http://www.postgresql.org/docs/9.1/static/arrays.html#ARRAYS-MODIFYING
Eventually I'd like to be able to do something like this:
User.objects.exclude(active_in__contains=[current_month]).update(active_in=F('active_in') + [current_month])
which should result in this SQL:
UPDATE user_user SET active_in = array_append(active_in, [%s]) WHERE NOT(active_in @> ARRAY[%s]::varchar[]) ['2012-01']
PS: I want to use update instead of get() and save() for performance reasons.
I want to add that in this particular use case I can get around using it (because I already have the user object anyway), but it would nevertheless be nice of have this feature, I think.
Taking this back to the simple case, the following should be supported for array_append (or the || operator):

Python: Item.objects.update(value=F('value') + 3)
SQL:    UPDATE item SET value = array_append(value, 3)
SQL:    UPDATE item SET value = value || 3
Result: [1, 2, 3, 3]
In addition, the array_cat function or || operator can be used to concatenate arrays:

Python: Item.objects.update(value=F('value') + [3, 4])
SQL:    UPDATE item SET value = array_cat(value, ARRAY[3, 4])
SQL:    UPDATE item SET value = value || ARRAY[3, 4]
Result: [1, 2, 3, 3, 4]
Going back to the original description, we could implement set-like updates to arrays (using bitwise-or):

Python: Item.objects.exclude(value__contains=[3]).update(value=F('value') + 3)
Python: Item.objects.update(value=F('value') | 3)
SQL:    UPDATE item SET value = array_append(value, 3)
            WHERE NOT (value @> ARRAY[3]::int[])
Result: [1, 2, 3]
The main challenge in all this is how to sensibly handle nested arrays.

Also note that PostgreSQL supports assigning to indexes or slices of arrays - maybe this could also be added somehow:

Python: Item.objects.update(value__1=5)  # would need different syntax?
SQL:    UPDATE item SET value[2] = 5
Result: [1, 5, 3]
Python: Item.objects.update(value__1_2=[5])  # would need different syntax?
SQL:    UPDATE item SET value[2:2] = ARRAY[5]
Result: [1, 5, 3]
Python: Item.objects.update(value__1_3=[2, 1])  # would need different syntax?
SQL:    UPDATE item SET value[2:3] = ARRAY[2, 1]
Result: [1, 2, 1]
Python: Item.objects.update(value__1_1=[4, 5, 6])  # would need different syntax?
SQL:    UPDATE item SET value = value[array_lower(value, 1):1]
            || ARRAY[4, 5, 6] || value[2:array_upper(value, 1)];
Result: [1, 4, 5, 6, 2, 3]
Note that updating a slice with more or less values that the width of the slice will require the same approach as the above example because PostgreSQL silently drops excess values provided and ERROR:  source array too small is thrown if there are not enough values.

Another consideration is for the array_prepend function, but I'm not sure how that could be handled nicely in the expressions syntax.

We could also use array_remove, but this will remove all values that match - a bit like set difference:

Python: Item.objects.update(value=F('value') - 2)
SQL:    UPDATE item SET value = array_remove(value, 2)
Result: [1, 3]
Last edited 9 years ago by Nick Pope  (previous)
  (diff)
This required implementing a limited form of dynamic dispatch to combine

expressions with numerical output. Refs #26355 should eventually provide

a better interface for that.