Comments on: Case-insensitive ordering with Django and PostgreSQL http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/ Code and comments on web development, Django, Python and things (un)related. Wed, 03 Apr 2013 13:15:20 +0000 hourly 1 http://wordpress.org/?v=4.3 By: raman http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-1086 Sat, 04 Feb 2012 18:59:49 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-1086 Depending on your table size/need for efficiency, another method is to create a ‘sort’ field on your table. Example:

class FoodItem(models.Model)
  	name = models.CharField(max_length=255)
  	sort_name = models.CharField(max_length=255, blank=True)
	
	def save(self):
		if self.name:
        	        self.name = self.name.strip()
			self.sort_name = self.name.lower()
                else:
			self.sort_name = ''
			
		super(FoodItem, self).save()
		

Then the ordering would be:

l = FoodItem.objects.all().order_by(‘sort_name’)

]]>
By: MMusashi http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-1019 Mon, 18 Apr 2011 07:27:41 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-1019 Thanks a lot !!
Love this solution.

]]>
By: ram http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-1011 Mon, 14 Mar 2011 07:18:21 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-1011 Great Solution, Is it works with foreignkey values?
Forexample:

Class A(models.Model):
name = models.CharField(max_length = 50)
…..

Class B(models.Model):
a = models.ForeignKey(A)
…..

Now i want to order the all “b” objects based on “a__name”. Is this is possible?
Can any one help me.

]]>
By: LC CLS Ron http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-905 Mon, 29 Nov 2010 05:15:53 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-905 Ok, you’ll have to read . between . the . lines. Most < (less than) and > (greater than) are missing.

]]>
By: LC CLS Ron http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-904 Mon, 29 Nov 2010 05:11:28 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-904 Yes, unless just released a new version with collation support, what users are requesting to be able to control (override) this:

‘AAA’ false in collations where sort order is like aaa AAA bbb BBB … (dictionary sort order)
‘AAA’ true in collations where sort order is like AAA … ZZZ aaa … ZZZ (binary)

It is VERY important to be able to override ANY operator to a given collation. In my experience, most databases do allow this.

for example:

‘AAA’ < 'aaa' collate SQL_Latin1_General_CP1_CS_AS
CS = case sensitive
AS = accent sensitive

'AAA' < 'aaa' collate SQL_Latin1_General_CP1_CI_AS
CI = case insensitive
AS = accent sensitive

or Oracle's nls_sort() for example.

Also, these databases can be told what collation to use at various levels including per db/table/statement so on.

I find this VITALLY important. So postgreSQL, I suggest very strongly that you do work on this.
Depending on LC_XXX is okay for default, but you must be able to override as far down as to the:

= = != operators
sort by clauses
like operations
so on…

Check out Oracle, SQL Server, MySql, so on… they all have it.

Cheers,
Ron Lentjes
LC CLS

]]>
By: Dave Naffziger http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-735 Tue, 27 Oct 2009 20:04:24 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-735 Great solution, super helpful. Thanks for posting it.

It might be helpful also to know that Wikipedia moved the page you linked to about different SQL dialects. The new page is:
http://en.wikibooks.org/wiki/SQL_Dialects_Reference/Functions_and_expressions/String_functions

It looks like the syntax is ok unless for some reason we want to switch to Virtuoso =)

]]>
By: Chris http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-698 Thu, 16 Jul 2009 08:36:48 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-698 Very nice. A great simple solution.

]]>
By: DjangoGuy http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-581 Thu, 13 Nov 2008 16:02:56 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-581 Thanks for the hint. It is quite useful!

]]>
By: Sundance http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/comment-page-1/#comment-126 Wed, 21 Nov 2007 09:52:29 +0000 http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/#comment-126 Ayup: Postgres doesn’t support collations. It claims to, but in truth, as far as I know, what it does is simply to use the OS-level LC_COLLATE variable (or a delegate thereof like LC_ALL) to its sorting functions and consider it a done deal. So it’s impossible to have per-database, or per-table, or, heavens forbid, per-column collations, as in, you know… other RDBMS.

This is easily the biggest wart in this otherwise wonderful RDBMS and I WISH they’d stop pretending they support collations. For all intents and purposes, they don’t.

]]>