Django and Postgresql operator does not exist: integer = character varying

Question

I have these two models:

class CachedRecord(models.Model):
    recordname = models.CharField(max_length=100,primary_key=True)
    recordcount = models.IntegerField()
    def __unicode__(self):
        return self.recordname

class CachedRecordData(models.Model):
    record = models.ForeignKey(CachedRecord)
    data = models.CharField(max_length=100)
    def __unicode__(self):
        return self.data

When I try to delete a CachedRecord from the admin panel I get this errror:

ProgrammingError at /admin/myapp/cachedrecord/

operator does not exist: integer = character varying
LINE 1: ...ON ( "myapp_cachedrecorddata"."record_id" = "myapp...
                                                             ^
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

I have found many questions (so this might be a duplicate), but I really don't understand any answer.

heroku, postgreSQL, django, comments, tastypie: No operator matches the given name and argument type(s). You might need to add explicit type casts

No operator matches the given name and argument type(s). You might need to add explicit type casts. -- Netbeans, Postgresql 8.4 and Glassfish

Where would I need to add these castings in django?

Answer 1

Been a couple of years since this question was asked/answered but I hit on the same issue today and found the accepted answer, while "easy" didn't really address the underlying issue with unexpected database table types. Django is built to "manage the keys correctly" for user defined primary keys just fine.

I hit a similar issue with a message of ProgrammingError: operator does not exist: character = uuid.

I hit this issue after migrating my Django project's MySQL database to PostgreSQL. MySQL doesn't have a native UUID field so it represents the models.UUIDField with a VARCHAR(32). After the migration the field type in PostgreSQL was also created as a character(32). In order to resolve the issue I had to alter the field type using the PostgreSQL command line:

ALTER TABLE my_table ALTER COLUMN my_field TYPE uuid USING uuid::uuid;

This casts the field type into the native UUID type for PostgreSQL that Django expected to find.

I'm guessing the OP added the recordname field after the CachedRecord table already had to CachedRecordData using the integer ID field that Django creates by default if primary_key isn't defined. After adding the new recordname field as the primary key the OP needed to update the existing relationships to use the new primary key.

Answer 2

You have set a character field (recordname) as the primary key for CachedRecord.

Django created an automatic primary key (of type integer) for CachedRecordData called id - since there is no primary key specified in the model definition.

Now when you try to delete CachedRecord, django is creating a primary key lookup to make sure all related CachedRecordData instances are deleted, and since one key is a character and the other an integer - the database is giving this error.

The easiest way to solve this problem is remove primary_key=True from recordname and let django manage the keys correctly. You can always add an index on that column, or other constraints (for example, set unique=True).

You also have a similar problem here:

def __unicode__(self):
    return self.recordname+":"+self.recordcount

You are adding a string ':' with self.recordcount which will result in a TypeError exception, as you cannot combine a string with a number:

>>> ':'+3
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: cannot concatenate 'str' and 'int' objects

To solve this issue:

def __unicode__(self):
    return u'{}:{}'.format(self.recordname, self.recordcount)