Postgres 8.4 imposes strict typing on CharField and TextField
|Reported by:||Russell Keith-Magee||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.1|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||yes||Patch needs improvement:||no|
Consider the following model:
class MyObj(models.Model): CHOICES = ( ('1', 'first choice') ('2', 'second choice') ) choice = models.CharField(max_length=1, choices=CHOICES)
(i.e, a char field whose choices are integers, but stored as strings. Now run two queries. First, query using an integer:
This yields the SQL:
('SELECT `myapp_myobj`.`id`, `myapp_myobj`.`choice` FROM `myapp_myobj` WHERE `myapp_myobj`.`choice` = %s ', (1,))
Now, query with an actual string:
which yields the SQL:
('SELECT `myapp_myobj`.`id`, `myapp_myobj`.`choice` FROM `myapp_myobj` WHERE `myapp_myobj`.`choice` = %s ', ('1',))
As reported by Bill Freeman on django-users, both examples work find for all databases -- except for Postgres 8.4. Postgres 8.4 imposes strong type checking, and raises an error on the first query.
The fact that the first example (the integer lookup) passes at all is due to the good grace of the databases themselves - logically, I think Postgres 8.4 is correct in declaring this an error. After all, "1" != 1.
I think the fix is pretty simple. CharField doesn't currently have a get_db_prep_value() method, and it should.
Compare and contrast with IntegerField or BooleanField - both these fields have get_db_prep_value() methods that cast the provided value to int() and bool(). CharField (and TextField for that matter) should do the same, but with unicode(). This would force the filter value of 1 into '1', which will be passed to the backend as a string, as it should be.