id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28702	Querying CIText fields should use ::citext type cast	Дилян Палаузов	Tim Graham <timograham@…>	"According to https://docs.djangoproject.com/en/1.11/howto/custom-model-fields/#custom-database-types db_type() is used when constructing a WHERE clause that includes the model field – that is, when you retrieve data using QuerySet methods like get(), filter(), and exclude() and have the model field as an argument.

Then https://docs.djangoproject.com/en/1.11/howto/custom-model-fields/#emulating-built-in-field-types states, that get_internal_type() is used for correct usage of columns and during migrations.

This would imply that having a django.contrib.postgresq.fields.CICharField field, calling Model.objects.filter(field_contains=""ABC"") would be converted to 'WHERE field::citext LIKE ""%ABC%""' but it is converted to 'WHERE field::text LIKE ""%ABC%""', which does not work as expected, so one has to call filter(field_icontains=""ABC"") instead.  The reason is that django/db/models/lookups.py:BuiltinLookup.process_lhs() passes the result of  get_internal_type to db/backends/postgresql/operations.py:lookup_cast() and this is used to distinguish the right lookup.

My proposal for fixing the code for ::citext:
{{{
diff --git a/django/contrib/postgres/fields/citext.py b/django/contrib/postgres/fields/citext.py
--- a/django/contrib/postgres/fields/citext.py
+++ b/django/contrib/postgres/fields/citext.py
@@ -7,6 +7,9 @@ class CIText:
     def db_type(self, connection):
         return 'citext'
 
+    def get_internal_type(self):
+        return ""CI"" + super().get_internal_type()
+
 
 class CICharField(CIText, CharField):
     pass
diff --git a/django/db/backends/postgresql/operations.py b/django/db/backends/postgresql/operations.py
--- a/django/db/backends/postgresql/operations.py
+++ b/django/db/backends/postgresql/operations.py
@@ -77,6 +77,8 @@ class DatabaseOperations(BaseDatabaseOperations):
                            'istartswith', 'endswith', 'iendswith', 'regex', 'iregex'):
             if internal_type in ('IPAddressField', 'GenericIPAddressField'):
                 lookup = ""HOST(%s)""
+            elif internal_type in ('CICharField', 'CITextField'):
+                lookup = ""%s::citext""
             else:
                 lookup = ""%s::text""

diff --git a/django/db/backends/postgresql/schema.py b/django/db/backends/postgresql/schema.py
--- a/django/db/backends/postgresql/schema.py
+++ b/django/db/backends/postgresql/schema.py
@@ -112,6 +112,7 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
         if (old_field.db_index or old_field.unique) and (
             (old_type.startswith('varchar') and not new_type.startswith('varchar')) or
             (old_type.startswith('text') and not new_type.startswith('text'))
+            (old_type.startswith('citext') and not new_type.startswith('citext'))
         ):
             index_name = self._create_index_name(model._meta.db_table, [old_field.column], suffix='_like')
             self.execute(self._delete_constraint_sql(self.sql_delete_index, model, index_name))
}}}"	Bug	closed	contrib.postgres	1.11	Release blocker	fixed	citext postgres cast	Mads Jensen	Accepted	1	0	0	0	0	0