[Django] #26355: Postgres ArrayField append
Django
----------------------------------+-----------------
Reporter: graup | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+-----------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26355>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
----------------------------------+--------------------------------------
Reporter: graup | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.9
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by graup):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:1>
Django
----------------------------------+------------------------------------
Reporter: graup | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.9
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:2>
Django
----------------------------------+------------------------------------
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.9
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 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
----------------------------------+------------------------------------
Type: New feature | Status: assigned
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by khorne):
* owner: => khorne
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:4>
Django
----------------------------------+------------------------------------
Type: New feature | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by khorne):
* has_patch: 0 => 1
Comment:
https://github.com/django/django/pull/6395
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:5>
Django
----------------------------------+------------------------------------
Reporter: Paul Grau | Owner: khorne
Type: New feature | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:6>
Django
----------------------------------+------------------------------------
Reporter: Paul Grau | Owner: khorne
Type: New feature | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:7>
Django
----------------------------------+------------------------------------
Reporter: Paul Grau | Owner: khorne
Type: New feature | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:8>
Django
----------------------------------+------------------------------------
Reporter: Paul Grau | Owner: khorne
Type: New feature | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:9>
Django
----------------------------------+------------------------------------
Reporter: Paul Grau | Owner: khorne
Type: New feature | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:10>
Django
Reporter: Paul Grau | Owner: Asif
| Saifuddin Auvi
Type: New feature | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Asif Saifuddin Auvi):
* owner: khorne => Asif Saifuddin Auvi
* version: 1.9 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Paul Grau | Owner: Asif
| Saifuddin Auvi
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Paul Grau | Owner: Asif
| Saifuddin Auvi
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"1e38f1191de21b6e96736f58df57dfb851a28c1f" 1e38f11]:
{{{
#!CommitTicketReference repository=""
revision="1e38f1191de21b6e96736f58df57dfb851a28c1f"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:13>
Django
Reporter: Paul Grau | Owner: (none)
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Mariusz Felisiak):
* owner: Asif Saifuddin Auvi => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:14>
Django
----------------------------------+------------------------------------
Reporter: Paul Grau | Owner: (none)
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by şuayip üzülmez):
* cc: şuayip üzülmez (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26355#comment:15>