[Django] #36690: Use case for EXTRA()

[Django]

#36690: Use case for EXTRA()
------------------------------+-----------------------------------------
Reporter: gleake-reorg | Type: Uncategorized
Status: new | Component: Uncategorized
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
------------------------------+-----------------------------------------
Django's ORM leverages UPPER() and casts to text `UPPER(...)::text` when
doing a case insensitive search. This is not adequately performant for our
use case, and loses the use of an index due to the UPPER() call. The
`extra()` function allows me to explicitly make a case insensitive ILIKE
call on postgres.
--
Ticket URL: <https://code.djangoproject.com/ticket/36690>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36690: Use case for EXTRA()

--------------------------------+--------------------------------------
Reporter: Gareth Leake | Owner: (none)

Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.2

Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Unreviewed

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

--------------------------------+--------------------------------------
Changes (by ontowhee):

* keywords: => QuerySet.extra

Comment:

I found ticket #3575, which reported that using `ILIKE` in postgres is
inefficient, and the solution changed the SQL from using `ILIKE` to
`UPPER`. Since `extra()` is planned for deprecation, could the query be
achieved by implementing a `Func` for `ILIKE` instead?
--
Ticket URL: <https://code.djangoproject.com/ticket/36690#comment:1>

[Django]

#36690: Use case for EXTRA()
--------------------------------+--------------------------------------
Reporter: Gareth Leake | Owner: (none)

Type: Uncategorized | Status: closed
Component: Uncategorized | Version: 5.2
Severity: Normal | Resolution: invalid

Keywords: QuerySet.extra | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------+--------------------------------------

Changes (by Simon Charette):

* resolution: => invalid
* status: new => closed

Comment:

As ontowee pointed out this isn't a valid use case for `extra`; simply use
`Func` or `RawSQL` for this purpose or
[https://docs.djangoproject.com/en/5.2/howto/custom-lookups/ define your
own custom lookup].
--
Ticket URL: <https://code.djangoproject.com/ticket/36690#comment:2>