#34059 (Validation of check constraints on JSONField key transforms with None produces invalid SQL on PostgreSQL.) – Django (original) (raw)

#34059 closed New feature (fixed)

Given a model with constraints within the JSONField:

class Version(models.Model): metadata = models.JSONField(blank=True, default=dict)

class Meta:
    constraints = [
        models.CheckConstraint(
            name='version_metadata_has_schema_version',
            check=~Q(metadata__schemaVersion=None),
        )
    ]

The following code produces an error:

version = Version(metadata={'foo': 'bar'}) version.validate_constraints()

WARNING Got a database error calling check() on <Q: (AND: (NOT (AND: ('metadata__schemaVersion', None))))>: operator is not unique: unknown -> unknown
LINE 1: SELECT 1 AS "_check" WHERE NOT (('{"foo": "bar"}' -> 'schema...
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts

Internally it's running the following query:

SELECT 1 AS "_check" WHERE NOT (('{"foo": "bar"}' -> 'schemaVersion') = 'null')

This appears similar to #33905.

Change History(11)

comment:2 by David Sanders, 3 years ago

comment:3 by David Sanders, 3 years ago

comment:4 by David Sanders, 3 years ago

Thanks for the report Dan. Yes this is similar to #33905 👍

comment:5 by Mariusz Felisiak, 3 years ago

comment:6 by Mariusz Felisiak, 3 years ago

Validation of json_field__key_transform=None doesn't work only on PostgreSQL. Creating check constraint with __exact lookup e.g. json_field__key_transform="value" crashes on Oracle but it's a separate issue and not a regression (see #34060).

comment:7 by David Sanders, 3 years ago

comment:8 by Mariusz Felisiak, 3 years ago

comment:10 by Mariusz Felisiak, 14 months ago

Note: See TracTickets for help on using tickets.

Back to Top