Opened 3 years ago

Last modified 6 days ago

#26355 assigned New feature

Add support for PostgreSQL's array_append to ArrayField

Reported by: Paul Grau Owned by: khorne
Component: contrib.postgres Version: 1.9
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (10)

comment:1 Changed 3 years ago by Paul Grau

Description: modified (diff)

comment:2 Changed 3 years ago by Tim Graham

Summary: Postgres ArrayField appendAdd support for PostgreSQL's array_append to ArrayField
Triage Stage: UnreviewedAccepted

comment:3 Changed 3 years ago by 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]
Last edited 3 years ago by Nick Pope (previous) (diff)

comment:4 Changed 3 years ago by khorne

Owner: set to khorne
Status: newassigned

comment:6 Changed 23 months ago by Tim Graham

Patch needs improvement: set

comment:7 Changed 23 months ago by Tim Graham

Patch needs improvement: unset

comment:8 Changed 23 months ago by Tim Graham

Patch needs improvement: set

comment:9 Changed 6 days ago by 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.

comment:10 Changed 6 days ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top