#33901: non-deterministic collations doesn't work with Unique=True on
Postgres13.3
-------------------------------------+-------------------------------------
Reporter: Ed Chai | Owner: Ed Chai
Type: Bug | Status: closed
Component: Migrations | Version: dev
Severity: Normal | Resolution: fixed
Keywords: collation unique | Triage Stage: Ready for
citext | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Ed Chai):
Replying to [comment:16 alfonsrv]:
The issue is likely caused by a "varchar_pattern_ops" that already existed
on your Postgres DB, you can run `select * from pg_indexes where
tablename='tablename';` in psql terminal to check if it was created. This
fix indeed only solves the problem when you first time creating model
fields with collation and prevents pattern ops index created. I'd be happy
to submit another PR for the migration.
> Despite this ticket being closed, I still get an error when attempting
to run the SQL migration. Running Django 4.2.3 and Postgres 15.x (also
tried 13.x)
>
> I created the collation required collation and attempt to run a
migration like this
>
> {{{
> #!python
> operations = [
> CreateCollation(
> "case_insensitive",
> provider="icu",
> locale="und-u-ks-level2",
> deterministic=False,
> ),
> ]
> }}}
>
> {{{
> #!python
> migrations.AlterField(
> model_name='user',
> name='email',
> field=models.EmailField(db_collation='case_insensitive',
max_length=254, unique=True, verbose_name='email address'),
> ),
> }}}
>
> This results in the following SQL:
>
> {{{
> #!sql
> BEGIN;
> --
> -- Alter field email on user
> --
> ALTER TABLE "core_user" ALTER COLUMN "email" TYPE varchar(254) COLLATE
"case_insensitive";
> COMMIT;
> }}}
>
> And the same error when attempting to run `python manage.py migrate`:
`django.db.utils.NotSupportedError: nondeterministic collations are not
supported for operator class "varchar_pattern_ops"`. I found that altering
the existing field doesn't work, but instead it has to be recreated
completely.
--
Ticket URL: <https://code.djangoproject.com/ticket/33901#comment:17>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018939ba1097-4999c5f9-9f94-48c7-9391-67468dfabfe1-000000%40eu-central-1.amazonses.com.
