Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#3115 closed defect (fixed)

Postgresql backend should convert Unicode input to bytestrings

Reported by: Manuel Saelices <msaelices@…> Owned by: adrian
Component: Database layer (models, ORM) Version: master
Severity: major Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


ORM fails in PostgreSQL when you filter with unicode strings. For example:

>>> from django.contrib.auth.models import User
>>> User.objects.filter(username=u'admin')
ProgrammingError: ERROR:  column "admin" does not exist

SELECT "auth_user"."id","auth_user"."username","auth_user"."first_name","auth_user"."last_name","auth_user"."email","auth_user"."password","auth_user"."is_staff","auth_user"."is_active","auth_user"."is_superuser","auth_user"."last_login","auth_user"."date_joined" FROM "auth_user" WHERE ("auth_user"."username" = admin) ORDER BY "auth_user"."username" ASC

The problem is on psycopg library, here are an example:

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('SELECT * from auth_user WHERE username = %s', [u'admin'])
ProgrammingError: ERROR:  column "admin" does not exist

SELECT * from auth_user WHERE username = admin

Ok, it's true... it's not a django error, but for example, in newforms all is unicode... ¿what we do? ¿Use psycopg2? ¿convert all unicodes to ascii in lookups? ¿convert all params on postgresql?. Call is made on django/db/models/, on this sentences:

     cursor = connection.cursor()
     select, sql, params = self._get_sql_clause()
     cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params)

Attachments (0)

Change History (5)

comment:1 Changed 7 years ago by adrian

Yes, we ought to change the postgresql Django database backend to check whether each string is Unicode before adding it to the statement.

This also ties into another suggestion people have had, which is to have a DATABASE_CHARSET setting. This would describe which character set the database uses.

comment:2 Changed 7 years ago by adrian

  • Summary changed from ORM problems with unicode in postgresql backend to Postgresql backend should convert Unicode input to bytestrings

comment:3 Changed 7 years ago by adrian

See also #952.

comment:4 Changed 7 years ago by adrian

  • Resolution set to fixed
  • Status changed from new to closed

(In [4244]) Fixed #3115 -- Changed postgresql backend to convert all Unicode strings to bytestrings according to DEFAULT_CHARSET. This is necessary because psycopg1 does not apply database quoting to Unicode strings

comment:5 Changed 7 years ago by anonymous

  • milestone Version 1.0 deleted

Milestone Version 1.0 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.