Add support for PostgreSQL's array_append to ArrayField

[Paul Grau]

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.

[Nick Pope]

I would consider expanding the scope of this somewhat...

In the following examples, value is the array [1, 2, 3].

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]

[khorne]

​https://github.com/django/django/pull/6395

[Malik A. Rumi]

Where do we stand on this feature since "needs improvement"? I notice it still isn't mentioned in the docs ​https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/fields/. Should we go with this ​https://django-postgres-extensions.readthedocs.io/en/latest/arrays.html instead? Thx.

[Tim Graham]

There hasn't been any response from the pull request author for some time. Feel free to pick it up if you like.

[Baptiste Mispelon]

For reference, I just closed #24709 as a duplicate. It had some ideas about a possible API which might be worth a look for anyone interested in picking this up again.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 1e38f11:

Fixed #30446 -- Resolved Value.output_field for stdlib types.

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.