Opened 4 weeks ago

Closed 4 weeks ago

#36690 closed Uncategorized (invalid)

Use case for EXTRA()

Reported by: Gareth Leake Owned by:
Component: Uncategorized Version: 5.2
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (2)

comment:1 by ontowhee, 4 weeks ago

Keywords: QuerySet.extra added

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?

comment:2 by Simon Charette, 4 weeks ago

Resolution: → invalid
Status: new → closed

As ontowee pointed out this isn't a valid use case for extra; simply use Func or RawSQL for this purpose or ​define your own custom lookup.

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