#26355: Add support for PostgreSQL's array_append to ArrayField
----------------------------------+------------------------------------
Reporter: graup | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.9
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
----------------------------------+------------------------------------
Comment (by pope1ni):
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[3:array_upper(value, 1)];
Result: [1, 4, 5, 6, 3]
}}}
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]
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:3>
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/063.ae15b06a0987342d0a3606b4ed445d5b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
