Re: Case-insensitive non-deterministic collation
ocs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups
Regards,
Chetan Ganji
+91-900-483-4183
ganji.che...@gmail.com
http://ryucoder.in
On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst
wrote:
The following warning triggered a bit of research which
looks like a significant amount of study will be
required to find the collation needed ...
django.contrib.postgres.fields.CICharField is
deprecated. Support for it (except in historical
migrations) will be removed in Django 5.1.
HINT: Use CharField(db_collation="…") with a
case-insensitive non-deterministic collation instead.
Does anyone have experience they would like to share?
What replaces that ellipsis?
The primary use case is to establish case-insensitivity
when checking names - including usernames, company
names and abbreviations/acronyms. Maybe there is a
better way to handle that?
This is my typical PostgreSQL database spec ...
CREATE DATABASE
WITH
OWNER = miked
ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
Many thanks for any help
Cheers
Mike
--
You received this message because you are subscribed to
the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving
emails from it, send an email to
django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au
<https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au?utm_medium=email&utm_source=footer>.
--
You received this message because you are subscribed to the
Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to
django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com
<https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Your
email software can handle signing.
--
You received this message because you are subscribed to the
Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to
django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/e3d57fb4-5899-a04c-f2b8-f39591c978c7%40dewhirst.com.au
<https://groups.google.com/d/msgid/django-users/e3d57fb4-5899-a04c-f2b8-f39591c978c7%40dewhirst.com.au?utm_medium=email&utm_source=footer>.
--
You received this message because you are subscribed to the
Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from
it, send an email to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAMKMUju4y_H%2BDUr1qn0Y4eNjYC_o%2BzCe5PNRiLctUZxfos5TSA%40mail.gmail.com
<https://groups.google.com/d/msgid/django-users/CAMKMUju4y_H%2BDUr1qn0Y4eNjYC_o%2BzCe5PNRiLctUZxfos5TSA%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Your
email software can handle signing.
--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Your
email software can handle signing.
--
You received this message because you are subscribed to the Google
Groups "Django users" group.
To
Re: Case-insensitive non-deterministic collation
Hi Mike, On Tue, Aug 15, 2023 at 4:30 AM Mike Dewhirst wrote: > Found a great article by Adam Johnson written in February ... > > > https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation/ > This is a great reference. It helped me out with the migration from postgresql ci fields to db collations. Everything about this is new for me as well. I'm sure the db collation strategy is more powerful and I can see the benefits. However, the postgresql ci fields were way easier to implement. Right now I'm testing it out on a smaller project. One problem that I'm currently facing is that exposing some fields that have the db_collation configuration to django-filters or to Django Admin search parameters are causing an exception: NotSupportedError nondeterministic collations are not supported for LIKE This is the collation that I'm using: CreateCollation( "case_insensitive", provider="icu", locale="und-u-ks-level2", deterministic=False, ) Anyway, all the icu / und-u-ks stuff look a little bit confusing. It would be good to have some guidelines or some quick recipes on the docs that would help us out making the migration :-) Kind regards, Vitor > > > Covers all the bases. > > Thank you Adam > > Cheers > > Mike > > On 7/08/2023 12:28 pm, Mike Dewhirst wrote: > > On 6/08/2023 9:17 pm, Chetan Ganji wrote: > > Thanks Chetan > > I have seen that 'icu' and 'und-whatever...' in various places on the web > - so it seems to be spreading - but I haven't had the brainspace to > understand it yet. > > I'll try an experiment with provider='C' and locale='C' because that is > how most of my databases are already established. If that passes my tests I > might move on to other things. > > From what I can see, PostgreSQL are likely to deprecate citext as > inelegant. That would be why Django has deprecated it. > > Thanks again. > > Mike > > Check this out. > https://gist.github.com/hleroy/2f3c6b00f284180da10ed9d20bf9240a > > # According to Django documentation, it’s preferable to use > non-deterministic collations > # instead of the citext extension for Postgres > 12. > # Example migation to create the case insensitive collation > class Migration(migrations.Migration): > operations = [ > CreateCollation( > 'case_insensitive', > provider='icu', > locale='und-u-ks-level2', > deterministic=False > ) > ] > # Example model using the new db_collation parameter introduced with > Django 3.2 > class Tag(models.Model): > name = models.CharField(max_length=50, db_collation='case_insensitive') > class Meta: > ordering = ['name'] > def __str__(self): > return self.name > > Regards, > Chetan Ganji > +91-900-483-4183 > ganji.che...@gmail.com > http://ryucoder.in > > > On Sun, Aug 6, 2023 at 12:32 PM Mike Dewhirst > wrote: > >> On 5/08/2023 7:58 pm, Chetan Ganji wrote: >> >> Hi Mike >> >> RE: The primary use case is to establish case-insensitivity when checking >> names - including usernames, company names and abbreviations/acronyms. >> >> I dont know anything about db_collation. >> >> >> Me neither >> >> Below 4 lookups should solve most common scenarios. >> >> >> Actually that was how I did it originally. I switched to using the >> PostgreSQL CI field because it is all done in the database - much faster - >> and my code is much reduced and therefore fewer possibilities for bugs etc. >> >> Judging from the Django release notes and the PostgreSQL docs there >> should be a straightforward answer to my question. Researching the correct >> answer is complex enough to make me ask here first. >> >> Cheers >> >> Mike >> >> https://docs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups >> >> >> >> Regards, >> Chetan Ganji >> +91-900-483-4183 >> ganji.che...@gmail.com >> http://ryucoder.in >> >> >> On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst >> wrote: >> >>> The following warning triggered a bit of research which looks like a >>> significant amount of study will be required to find the collation needed >>> ... >>> >>> >>> django.contrib.postgres.fields.CICharField is deprecated. Support for it >>> (except in historical migrations) will be removed in Django 5.1. >>> HINT: Use CharField(db_collation="…") with a case-insensitive >>> non-deterministic collation instead. >>> >
Re: Case-insensitive non-deterministic collation
Found a great article by Adam Johnson written in February ...
https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation/
Covers all the bases.
Thank you Adam
Cheers
Mike
On 7/08/2023 12:28 pm, Mike Dewhirst wrote:
On 6/08/2023 9:17 pm, Chetan Ganji wrote:
Thanks Chetan
I have seen that 'icu' and 'und-whatever...' in various places on the
web - so it seems to be spreading - but I haven't had the brainspace
to understand it yet.
I'll try an experiment with provider='C' and locale='C' because that
is how most of my databases are already established. If that passes my
tests I might move on to other things.
From what I can see, PostgreSQL are likely to deprecate citext as
inelegant. That would be why Django has deprecated it.
Thanks again.
Mike
Check this out.
https://gist.github.com/hleroy/2f3c6b00f284180da10ed9d20bf9240a
# According to Django documentation, it’s preferable to use
non-deterministic collations
# instead of the citext extension for Postgres > 12.
# Example migation to create the case insensitive collation
class Migration(migrations.Migration):
operations = [
CreateCollation(
'case_insensitive',
provider='icu',
locale='und-u-ks-level2',
deterministic=False
)
]
# Example model using the new db_collation parameter introduced with
Django 3.2
class Tag(models.Model):
name = models.CharField(max_length=50, db_collation='case_insensitive')
class Meta:
ordering = ['name']
def __str__(self):
return self.name
Regards,
Chetan Ganji
+91-900-483-4183
ganji.che...@gmail.com
http://ryucoder.in
On Sun, Aug 6, 2023 at 12:32 PM Mike Dewhirst
wrote:
On 5/08/2023 7:58 pm, Chetan Ganji wrote:
Hi Mike
RE: The primary use case is to establish case-insensitivity when
checking names - including usernames, company names and
abbreviations/acronyms.
I dont know anything about db_collation.
Me neither
Below 4 lookups should solve most common scenarios.
Actually that was how I did it originally. I switched to using
the PostgreSQL CI field because it is all done in the database -
much faster - and my code is much reduced and therefore fewer
possibilities for bugs etc.
Judging from the Django release notes and the PostgreSQL docs
there should be a straightforward answer to my question.
Researching the correct answer is complex enough to make me ask
here first.
Cheers
Mike
https://docs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups
Regards,
Chetan Ganji
+91-900-483-4183
ganji.che...@gmail.com
http://ryucoder.in
On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst
wrote:
The following warning triggered a bit of research which
looks like a significant amount of study will be required to
find the collation needed ...
django.contrib.postgres.fields.CICharField is deprecated.
Support for it (except in historical migrations) will be
removed in Django 5.1.
HINT: Use CharField(db_collation="…") with a
case-insensitive non-deterministic collation instead.
Does anyone have experience they would like to share? What
replaces that ellipsis?
The primary use case is to establish case-insensitivity when
checking names - including usernames, company names and
abbreviations/acronyms. Maybe there is a better way to
handle that?
This is my typical PostgreSQL database spec ...
CREATE DATABASE
WITH
OWNER = miked
ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
Many thanks for any help
Cheers
Mike
--
You received this message because you are subscribed to the
Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to
django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au
<https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au?utm_medium=email&utm_source=footer>.
--
You received this message because you are subscribed to the
Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from
it, send an email to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7S
Re: Case-insensitive non-deterministic collation
My tests stopped working so I have decided to abandon case-insensitive fields and do it all manually. Thanks everyone. Cheers Mike On Monday, August 7, 2023 at 12:29:28 PM UTC+10 Mike Dewhirst wrote: > On 6/08/2023 9:17 pm, Chetan Ganji wrote: > > Thanks Chetan > > I have seen that 'icu' and 'und-whatever...' in various places on the web > - so it seems to be spreading - but I haven't had the brainspace to > understand it yet. > > I'll try an experiment with provider='C' and locale='C' because that is > how most of my databases are already established. If that passes my tests I > might move on to other things. > > From what I can see, PostgreSQL are likely to deprecate citext as > inelegant. That would be why Django has deprecated it. > > Thanks again. > > Mike > > Check this out. > https://gist.github.com/hleroy/2f3c6b00f284180da10ed9d20bf9240a > > # According to Django documentation, it’s preferable to use > non-deterministic collations > # instead of the citext extension for Postgres > 12. > # Example migation to create the case insensitive collation > class Migration(migrations.Migration): > operations = [ > CreateCollation( > 'case_insensitive', > provider='icu', > locale='und-u-ks-level2', > deterministic=False > ) > ] > # Example model using the new db_collation parameter introduced with > Django 3.2 > class Tag(models.Model): > name = models.CharField(max_length=50, db_collation='case_insensitive') > class Meta: > ordering = ['name'] > def __str__(self): > return self.name > > Regards, > Chetan Ganji > +91-900-483-4183 > ganji@gmail.com > http://ryucoder.in > > > On Sun, Aug 6, 2023 at 12:32 PM Mike Dewhirst > wrote: > >> On 5/08/2023 7:58 pm, Chetan Ganji wrote: >> >> Hi Mike >> >> RE: The primary use case is to establish case-insensitivity when checking >> names - including usernames, company names and abbreviations/acronyms. >> >> I dont know anything about db_collation. >> >> >> Me neither >> >> Below 4 lookups should solve most common scenarios. >> >> >> Actually that was how I did it originally. I switched to using the >> PostgreSQL CI field because it is all done in the database - much faster - >> and my code is much reduced and therefore fewer possibilities for bugs etc. >> >> Judging from the Django release notes and the PostgreSQL docs there >> should be a straightforward answer to my question. Researching the correct >> answer is complex enough to make me ask here first. >> >> Cheers >> >> Mike >> >> https://docs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups >> >> >> >> Regards, >> Chetan Ganji >> +91-900-483-4183 >> ganji@gmail.com >> http://ryucoder.in >> >> >> On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst >> wrote: >> >>> The following warning triggered a bit of research which looks like a >>> significant amount of study will be required to find the collation needed >>> ... >>> >>> >>> django.contrib.postgres.fields.CICharField is deprecated. Support for it >>> (except in historical migrations) will be removed in Django 5.1. >>> HINT: Use CharField(db_collation="…") with a case-insensitive >>> non-deterministic collation instead. >>> >>> >>> Does anyone have experience they would like to share? What replaces that >>> ellipsis? >>> >>> The primary use case is to establish case-insensitivity when checking >>> names - including usernames, company names and abbreviations/acronyms. >>> Maybe there is a better way to handle that? >>> >>> This is my typical PostgreSQL database spec ... >>> >>> CREATE DATABASE >>> WITH >>> OWNER = miked >>> ENCODING = 'UTF8' >>> LC_COLLATE = 'C' >>> LC_CTYPE = 'C' >>> TABLESPACE = pg_default >>> CONNECTION LIMIT = -1 >>> IS_TEMPLATE = False; >>> >>> Many thanks for any help >>> >>> Cheers >>> >>> Mike >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Django users" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to django-users...@googlegroups.com.
Re: Case-insensitive non-deterministic collation
On 6/08/2023 9:17 pm, Chetan Ganji wrote:
Thanks Chetan
I have seen that 'icu' and 'und-whatever...' in various places on the
web - so it seems to be spreading - but I haven't had the brainspace to
understand it yet.
I'll try an experiment with provider='C' and locale='C' because that is
how most of my databases are already established. If that passes my
tests I might move on to other things.
From what I can see, PostgreSQL are likely to deprecate citext as
inelegant. That would be why Django has deprecated it.
Thanks again.
Mike
Check this out.
https://gist.github.com/hleroy/2f3c6b00f284180da10ed9d20bf9240a
# According to Django documentation, it’s preferable to use
non-deterministic collations
# instead of the citext extension for Postgres > 12.
# Example migation to create the case insensitive collation
class Migration(migrations.Migration):
operations = [
CreateCollation(
'case_insensitive',
provider='icu',
locale='und-u-ks-level2',
deterministic=False
)
]
# Example model using the new db_collation parameter introduced with
Django 3.2
class Tag(models.Model):
name = models.CharField(max_length=50, db_collation='case_insensitive')
class Meta:
ordering = ['name']
def __str__(self):
return self.name
Regards,
Chetan Ganji
+91-900-483-4183
ganji.che...@gmail.com
http://ryucoder.in
On Sun, Aug 6, 2023 at 12:32 PM Mike Dewhirst
wrote:
On 5/08/2023 7:58 pm, Chetan Ganji wrote:
Hi Mike
RE: The primary use case is to establish case-insensitivity when
checking names - including usernames, company names and
abbreviations/acronyms.
I dont know anything about db_collation.
Me neither
Below 4 lookups should solve most common scenarios.
Actually that was how I did it originally. I switched to using the
PostgreSQL CI field because it is all done in the database - much
faster - and my code is much reduced and therefore fewer
possibilities for bugs etc.
Judging from the Django release notes and the PostgreSQL docs
there should be a straightforward answer to my question.
Researching the correct answer is complex enough to make me ask
here first.
Cheers
Mike
https://docs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups
Regards,
Chetan Ganji
+91-900-483-4183
ganji.che...@gmail.com
http://ryucoder.in
On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst
wrote:
The following warning triggered a bit of research which looks
like a significant amount of study will be required to find
the collation needed ...
django.contrib.postgres.fields.CICharField is deprecated.
Support for it (except in historical migrations) will be
removed in Django 5.1.
HINT: Use CharField(db_collation="…") with a
case-insensitive non-deterministic collation instead.
Does anyone have experience they would like to share? What
replaces that ellipsis?
The primary use case is to establish case-insensitivity when
checking names - including usernames, company names and
abbreviations/acronyms. Maybe there is a better way to handle
that?
This is my typical PostgreSQL database spec ...
CREATE DATABASE
WITH
OWNER = miked
ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
Many thanks for any help
Cheers
Mike
--
You received this message because you are subscribed to the
Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from
it, send an email to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au
<https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au?utm_medium=email&utm_source=footer>.
--
You received this message because you are subscribed to the
Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com
<https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
Signed email is an absolute defence against phishin
Re: Case-insensitive non-deterministic collation
Any help visit www.bansloi.com this is django website On Sun, 6 Aug 2023, 4:48 pm Chetan Ganji, wrote: > Check this out. > https://gist.github.com/hleroy/2f3c6b00f284180da10ed9d20bf9240a > > # According to Django documentation, it’s preferable to use > non-deterministic collations > # instead of the citext extension for Postgres > 12. > # Example migation to create the case insensitive collation > > class Migration(migrations.Migration): > > operations = [ > CreateCollation( > 'case_insensitive', > provider='icu', > locale='und-u-ks-level2', > deterministic=False > ) > ] > > > # Example model using the new db_collation parameter introduced with > Django 3.2 > > class Tag(models.Model): > name = models.CharField(max_length=50, db_collation='case_insensitive') > > class Meta: > ordering = ['name'] > > def __str__(self): > return self.name > > Regards, > Chetan Ganji > +91-900-483-4183 > ganji.che...@gmail.com > http://ryucoder.in > > > On Sun, Aug 6, 2023 at 12:32 PM Mike Dewhirst > wrote: > >> On 5/08/2023 7:58 pm, Chetan Ganji wrote: >> >> Hi Mike >> >> RE: The primary use case is to establish case-insensitivity when checking >> names - including usernames, company names and abbreviations/acronyms. >> >> I dont know anything about db_collation. >> >> >> Me neither >> >> Below 4 lookups should solve most common scenarios. >> >> >> Actually that was how I did it originally. I switched to using the >> PostgreSQL CI field because it is all done in the database - much faster - >> and my code is much reduced and therefore fewer possibilities for bugs etc. >> >> Judging from the Django release notes and the PostgreSQL docs there >> should be a straightforward answer to my question. Researching the correct >> answer is complex enough to make me ask here first. >> >> Cheers >> >> Mike >> >> https://docs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups >> >> >> >> Regards, >> Chetan Ganji >> +91-900-483-4183 >> ganji.che...@gmail.com >> http://ryucoder.in >> >> >> On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst >> wrote: >> >>> The following warning triggered a bit of research which looks like a >>> significant amount of study will be required to find the collation needed >>> ... >>> >>> >>> django.contrib.postgres.fields.CICharField is deprecated. Support for it >>> (except in historical migrations) will be removed in Django 5.1. >>> HINT: Use CharField(db_collation="…") with a case-insensitive >>> non-deterministic collation instead. >>> >>> >>> Does anyone have experience they would like to share? What replaces that >>> ellipsis? >>> >>> The primary use case is to establish case-insensitivity when checking >>> names - including usernames, company names and abbreviations/acronyms. >>> Maybe there is a better way to handle that? >>> >>> This is my typical PostgreSQL database spec ... >>> >>> CREATE DATABASE >>> WITH >>> OWNER = miked >>> ENCODING = 'UTF8' >>> LC_COLLATE = 'C' >>> LC_CTYPE = 'C' >>> TABLESPACE = pg_default >>> CONNECTION LIMIT = -1 >>> IS_TEMPLATE = False; >>> >>> Many thanks for any help >>> >>> Cheers >>> >>> Mike >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Django users" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to django-users+unsubscr...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au >>> <https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au?utm_medium=email&utm_source=footer> >>> . >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to django-users+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m
Re: Case-insensitive non-deterministic collation
Check this out. https://gist.github.com/hleroy/2f3c6b00f284180da10ed9d20bf9240a # According to Django documentation, it’s preferable to use non-deterministic collations # instead of the citext extension for Postgres > 12. # Example migation to create the case insensitive collation class Migration(migrations.Migration): operations = [ CreateCollation( 'case_insensitive', provider='icu', locale='und-u-ks-level2', deterministic=False ) ] # Example model using the new db_collation parameter introduced with Django 3.2 class Tag(models.Model): name = models.CharField(max_length=50, db_collation='case_insensitive') class Meta: ordering = ['name'] def __str__(self): return self.name Regards, Chetan Ganji +91-900-483-4183 ganji.che...@gmail.com http://ryucoder.in On Sun, Aug 6, 2023 at 12:32 PM Mike Dewhirst wrote: > On 5/08/2023 7:58 pm, Chetan Ganji wrote: > > Hi Mike > > RE: The primary use case is to establish case-insensitivity when checking > names - including usernames, company names and abbreviations/acronyms. > > I dont know anything about db_collation. > > > Me neither > > Below 4 lookups should solve most common scenarios. > > > Actually that was how I did it originally. I switched to using the > PostgreSQL CI field because it is all done in the database - much faster - > and my code is much reduced and therefore fewer possibilities for bugs etc. > > Judging from the Django release notes and the PostgreSQL docs there should > be a straightforward answer to my question. Researching the correct answer > is complex enough to make me ask here first. > > Cheers > > Mike > > https://docs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups > > > > Regards, > Chetan Ganji > +91-900-483-4183 > ganji.che...@gmail.com > http://ryucoder.in > > > On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst > wrote: > >> The following warning triggered a bit of research which looks like a >> significant amount of study will be required to find the collation needed >> ... >> >> >> django.contrib.postgres.fields.CICharField is deprecated. Support for it >> (except in historical migrations) will be removed in Django 5.1. >> HINT: Use CharField(db_collation="…") with a case-insensitive >> non-deterministic collation instead. >> >> >> Does anyone have experience they would like to share? What replaces that >> ellipsis? >> >> The primary use case is to establish case-insensitivity when checking >> names - including usernames, company names and abbreviations/acronyms. >> Maybe there is a better way to handle that? >> >> This is my typical PostgreSQL database spec ... >> >> CREATE DATABASE >> WITH >> OWNER = miked >> ENCODING = 'UTF8' >> LC_COLLATE = 'C' >> LC_CTYPE = 'C' >> TABLESPACE = pg_default >> CONNECTION LIMIT = -1 >> IS_TEMPLATE = False; >> >> Many thanks for any help >> >> Cheers >> >> Mike >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to django-users+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au >> <https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au?utm_medium=email&utm_source=footer> >> . >> > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to django-users+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com > <https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > > > -- > Signed email is an absolute defence against phishing. This email has > been signed with my private key. If you import my public key you can > automatically decrypt my signature and be sure it came from me. Your > email software can handle signing. > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To unsubscribe from t
Re: Case-insensitive non-deterministic collation
On 5/08/2023 7:58 pm, Chetan Ganji wrote: Hi Mike RE: The primary use case is to establish case-insensitivity when checking names - including usernames, company names and abbreviations/acronyms. I dont know anything about db_collation. Me neither Below 4 lookups should solve most common scenarios. Actually that was how I did it originally. I switched to using the PostgreSQL CI field because it is all done in the database - much faster - and my code is much reduced and therefore fewer possibilities for bugs etc. Judging from the Django release notes and the PostgreSQL docs there should be a straightforward answer to my question. Researching the correct answer is complex enough to make me ask here first. Cheers Mike https://docs.djangoproject.com/en/4.2/ref/models/querysets/#field-lookups Regards, Chetan Ganji +91-900-483-4183 ganji.che...@gmail.com http://ryucoder.in On Sat, Aug 5, 2023 at 1:35 PM Mike Dewhirst wrote: The following warning triggered a bit of research which looks like a significant amount of study will be required to find the collation needed ... django.contrib.postgres.fields.CICharField is deprecated. Support for it (except in historical migrations) will be removed in Django 5.1. HINT: Use CharField(db_collation="…") with a case-insensitive non-deterministic collation instead. Does anyone have experience they would like to share? What replaces that ellipsis? The primary use case is to establish case-insensitivity when checking names - including usernames, company names and abbreviations/acronyms. Maybe there is a better way to handle that? This is my typical PostgreSQL database spec ... CREATE DATABASE WITH OWNER = miked ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = pg_default CONNECTION LIMIT = -1 IS_TEMPLATE = False; Many thanks for any help Cheers Mike -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au <https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au?utm_medium=email&utm_source=footer>. -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com <https://groups.google.com/d/msgid/django-users/CAMKMUjuxfeV5m4QiPz1jEyh7fRobqZn7SCp4dnXnjrSOBirh7Q%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- Signed email is an absolute defence against phishing. This email has been signed with my private key. If you import my public key you can automatically decrypt my signature and be sure it came from me. Your email software can handle signing. -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/e3d57fb4-5899-a04c-f2b8-f39591c978c7%40dewhirst.com.au. OpenPGP_signature Description: OpenPGP digital signature
Case-insensitive non-deterministic collation
The following warning triggered a bit of research which looks like a significant amount of study will be required to find the collation needed ... django.contrib.postgres.fields.CICharField is deprecated. Support for it (except in historical migrations) will be removed in Django 5.1. HINT: Use CharField(db_collation="…") with a case-insensitive non-deterministic collation instead. Does anyone have experience they would like to share? What replaces that ellipsis? The primary use case is to establish case-insensitivity when checking names - including usernames, company names and abbreviations/acronyms. Maybe there is a better way to handle that? This is my typical PostgreSQL database spec ... CREATE DATABASE WITH OWNER = miked ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = pg_default CONNECTION LIMIT = -1 IS_TEMPLATE = False; Many thanks for any help Cheers Mike -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2eccab9e-e296-55e0-05de-e8d4cf708262%40dewhirst.com.au.
