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
34553	Can't create CheckConstraint with percent characters in values on postgresql due to broken quoting	Thomas Kolar	Simon Charette	"It is currently impossible to create check constraints that reference values containing percent characters (`%`).

Consider the following model definition:
{{{#!python
from django.db import models


class Dolor(models.Model):
    sit = models.CharField(max_length=42069)

    class Meta:
        constraints = (
            models.CheckConstraint(
                check=models.Q(sit=""%""),
                name=""amet"",
            ),
        )
}}}

This will indeed result in a constraint being created. However, the percent character will be doubled, and constrain `sit` to contain `%%`, not `%`:
{{{#!python
>>> from ipsum.models import *
>>> Dolor.objects.create(sit=""%"")
Traceback (most recent call last):
 (... snip ...)
psycopg2.errors.CheckViolation: new row for relation ""ipsum_dolor"" violates check constraint ""amet""
DETAIL:  Failing row contains (1, %).


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
 (... snip ...)
django.db.utils.IntegrityError: new row for relation ""ipsum_dolor"" violates check constraint ""amet""
DETAIL:  Failing row contains (1, %).

>>> Dolor.objects.create(sit=""%%"")
<Dolor: Dolor object (2)>
>>>
}}}

This may be a super contrived example, but using an `__in` lookup in the check with a list of allowed values also does not work (`choices` are not a suitable alternative here as that isn't enforced at the DB level).

This was likely introduced with the fix for #30484 - here, the schema editor's `quote_value` method was changed to always double percent characters.

I'd also like to note that creating a `CheckConstraint` like this DOES work in Django 2.2. Likely, that stopped being the case when #30060 was fixed. I can attest that this is a serious roadblock when moving a project from 2.2 to a modern and supported version of Django.

Honestly, I'm surprised that this doesn't break in way more instances. ""Quoting"" like this should only be correct specifically for `LIKE` (and the likes of it).

Suggested fix: remove the percentage character doubling from the general-purpose escaping method, and reintroduce it in a targeted way where needed.

I unfortunately don't know enough about the ORM's internals to make a more detailed suggestion, or know for just how much headache creation I am to apologize. But the current behavior is obviously incorrect.

Additional notes:
 - This affects django 3.2 as well
 - This almost certainly affects the oracle backend as well
 - This works as expected on sqlite3

I have attached a sample django project that reproduces the issue.

Steps to reproduce (require docker installation):
 - Spin up a postgres instance: `docker run -e POSTGRES_USER=lorem -e POSTGRES_PASSWORD=lorem -e POSTGRES_DB=lorem -p 5432:5432 postgres`
 - Extract `lorem.zip`
 - Create a virtual environment
 - Install `django==4.2.1`, `psycopg2-binary==2.9.6`
 - Activate the virtual environment
 - Extract `lorem.zip` and change into django directory
 - `python -m manage migrate`
 - `python -m manage shell`
 - `from ipsum.models import *`
 - `Dolor.objects.create(sit=""%"")`
   - expected behavior: This should return a model instance
   - actual behavior: exception (as described above)
 - `Dolor.objects.create(sit=""%%"").sit`
   - expected behavior: exception (as described above)
   - actual behavior: returns ""%%""

"	Bug	closed	Database layer (models, ORM)	4.2	Normal	fixed		Simon Charette	Ready for checkin	1	0	0	0	0	0