PostgreSQL 8.3 cannot do text matching on uncast non-text columns

I can confirm that this most likely has something to do with unique_together Meta class attribute and a ForeignKey? being one of the fields present. When I removed it from Meta class, I was able to create records successfully using the Admin interface.

As a side note, when I was adding records to that table before outside of Admin, using my own forms, I didn't experience this problem.

I can confirm this behaviour using django-trunk on postgres-8.3 on Ubuntu Hardy. It works using pg 8.2

Confirmed using PostgreSQL 8.3 too, it seems to be a problem with unique_together.

I imagine this can be solved by adding an explicit cast to text. See http://www.postgresql.org/docs/8.3/static/release-8-3.html#AEN85667

Adding redirects using the (old) admin site no longer works after upgrading to PosgreSQL 8.3, similar error message:

ERROR:  operator does not exist: integer ~~* unknown at character 290
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  SELECT "django_redirect"."id","django_redirect"."site_id","django_redirect"."old_path","django_redirect"."new_path" FROM "django_redirect"
INNER JOIN "django_site" AS "django_redirect__site" ON "django_redirect"."site_id" = "django_redirect__site"."id" 
WHERE ("django_redirect__site"."id" ILIKE '1' AND "django_redirect"."old_path" ILIKE '/agrarbuero')

I'm seeing this error too, if any of the fields in a unique_together() list are also foreign keys. Using PostgreSQL 8.3.0

Fix for iexact in PostgreSQL 8.3

I've attached a patch which fixes the problem. Could someone please review and check that this is the correct approach to fixing this? This should be high-priority as many people are using Django with PostgreSQL and 8.3 has been released now.

Havn't looked to much into this, but it seems to stand to reason that other operations like LIKE should also be casted. Like: 'iexact', 'contains', 'icontains', 'startswith', 'endswith', 'istartswith', 'iendswidth'; All are currently auto-casted to strings in PostgreSQL <8.3 as the release notes of 8.3 explains.

Possibly this problem can also appear with normal comparisons (not sure here) if you would try to compare a date or something to a raw string. (like FooModel?.objects.filter(my_date_fieldeq='01-01-2008') ? again not tested this myself.)

Essentially as i read the release notes of PostgreSQL 8.3, the driver should cast every case where auto-casting is now disabled to be fully compatible with pre 8.3 releases.

(Changing ticket title to reflect the problem and closing #6605 in favour of this one, since there are more details of the impact here.)

Following a discussion on django-dev about this, we're inclined, if at all possible, to not make any changes here. Instead, the right comparison types for the data should be used.

However, there may be some core functionality that isn't then possible, so that needs checking.

If there's a relatively simple patch that only has an impact on non-text fields (having a pointless cast for text fields is a performance penalty on people doing the right thing, which isn't cool) and only for PostgreSQL (other backends shouldn't have to pay the penalty), we can include it. But I, and others, would prefer that we instead avoided the need for this if at all possible.

Maybe there is another, better ticket for this, but I'd like to point out again that it affects unique_together(). Example:

class ServiceCategory?(models.Model):

service = models.ForeignKey?(Service) category = models.ForeignKey?(Category)

class Meta:

order_with_respect_to = 'service' unique_together = (('service', 'category'),)

This will cause a ProgrammingError? with PostgreSQL 8.3 with the following error:

operator does not exist: integer * unknown LINE 1: ...ory" WHERE "service_servicecategory"."service_id" ILIKE '1' ... ^{HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.}

I don't think this problem can be ignored, and it's only a matter of time before more people run into it, as they upgrade their PostgreSQL installations to 8.3.

(sorry for the double post, I'll format this one so it's actually readable)

Maybe there is another, better ticket for this, but I'd like to point out again that it affects unique_together(). Example:

class ServiceCategory(models.Model):
    service = models.ForeignKey(Service)
    category = models.ForeignKey(Category)

    class Meta:
        order_with_respect_to = 'service'
        unique_together = (('service', 'category'),)

This will cause a ProgrammingError? with PostgreSQL 8.3 with the following error:

operator does not exist: integer ~~* unknown LINE 1: ...ory" WHERE "service_servicecategory"."service_id" ILIKE '1' ... ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I don't think this problem can be ignored, and it's only a matter of time before more people run into it, as they upgrade their PostgreSQL installations to 8.3.

Replying to anonymous:

This is related to ticket #4562

http://code.djangoproject.com/ticket/4562

Because the orm mapper incorrectly uses iexact instead of exact for manipulators unique_together.

That patch also solves this problem.

Also at this moment i cannot think of anything why on earth iexact is used. Seem to me that only can create errors, as databases are suppose to be case-sensitive. (And that MySQL is case-insensitive by default is imho a very very big bug in there database)

Updated iexact patch, against r7566. Supports postgresql and psycopg2_postgresql backends.

I added an updated patch. It adds the functionality for the psycopg2_postgresql backend as well as the postgresql backend.

It also affects generic relations. You will run into problems trying to create a generic relation on models that have a non-integer primary key (if you've followed the examples and used IntegerField? for object_id on the generic model.

If you've previously worked around this by using CharField? for object_id, you will then run into problems when trying to access the GenericRelatedObjectManager? on models that have an integer primary key.

Without PostgreSQL's implicit type cast the only solution is to modify GenericRelatedObjectManager? to explicitly cast the content object's primary key to match the generic model's object_id field. This fix would still require users to manually cast primary key values when accessing generic relations manually.

If we're not going to fix this everywhere (e.g. field lookups in userland, where the user is able to cast their data manually) we should at least fix it everywhere in Django core where the user is unable to cast their data.

I can't reproduce the unique_together problem specifically after the newforms-admin merge, but here is a patch (with tests) to allow text matching on non-text fields in PostgreSQL 8.3.

text matching for non-text fields in PostgreSQL 8.3

(In [8242]) Fixed #6523 -- Use the correct cast on field types for PostgreSQL when searching within a field column (e.g. "like", "contains", etc). Required for PostgreSQL 8.3. Thanks to Dan Watson for the patch.