pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jason Earl <jdearl@yahoo.com>
To: Bob Swerdlow <swerdlow@maine.rr.com>
To: pgsql-sql@postgresql.org
Subject: Re: ORDER BY case insensitive?
Date: Thu, 4 Oct 2001 14:22:55 -0700 (PDT)
Message-ID: <20011004212255.3654.qmail@web10002.mail.yahoo.com> (raw)
In-Reply-To: <02b101c14d16$f5c37050$6301a8c0@RSWERDLOW800>

My guess is that compared to the task of sorting
millions of names the fact that you have to lowercase
them first is not going to be a particular burden.  No
matter what you do you are going to get a table scan
(unless you qualify your select with a where clause).

You can, however, create an index like:

create index MyTable_lower_idx on MyTable
(lower(name));

It won't help with your particular query, but it
certainly would help for queries like:

SELECT * FROM MyTable WHERE lower(name) = 'jason';

It is also possible to create a trigger that would
automatically lowercase information when it is
inserted or updated.  Or even less high tech you can
make sure that all of your INSERTS and UPDATES use the
lower command like this:

INSERT into MyTable (name) VALUES (lower('VALUE'));

Jason

--- Bob Swerdlow <swerdlow@maine.rr.com> wrote:
> Thanks for the suggestion, Jason.
> 
> A co-worker of mine, however, had this response:
>     Yes, but my guess is that that will first
> convert all million (or
> whatever) records to upper case, and then physically
> sort them.  It won't be
> able to make use of the index.
> 
> To make this efficient, do we need to uppercase all
> of the data before
> adding to the table? (yuk)
> 
> - Bob
> 
> 
> ----- Original Message -----
> From: "Jason Earl" <jdearl@yahoo.com>
> To: "Bob Swerdlow" <swerdlow@maine.rr.com>;
> <pgsql-sql@postgresql.org>
> Sent: Thursday, October 04, 2001 3:47 PM
> Subject: Re: [SQL] ORDER BY case insensitive?
> 
> 
> > SELECT * FROM MyTable ORDER BY lower(Name);
> >
> > Should do the trick.
> >
> > Jason Earl
> >
> > --- Bob Swerdlow <swerdlow@maine.rr.com> wrote:
> > > How do I get the rows sorted in a case
> insensitive
> > > way?
> > >
> > > I have some queries that basically fit the form:
> > >      SELECT * FROM MyTable ORDER BY Name;
> > > When I view the results, all of the Name's that
> > > start with an upper case
> > > letter precede all of the Name's that start with
> a
> > > lower case letter.  I
> > > want them all in alphabetic order, regardless of
> > > case.
> > >
> > > I've looked in PostgreSQL Introduction and
> Concepts
> > > by Bruce Momjian and in
> > > the FAQ at
> > >
> http://postgresql.overtone.org/docs/faq-english.html
> > >
> > > Thanks for your help.
> > >
> > > --
> > > Bob Swerdlow
> > > Chief Operating Officer
> > > Transpose, LLC
> > > rswerdlow@transpose.com
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > NEW from Yahoo! GeoCities - quick and easy web
> site hosting, just
> $8.95/month.
> > http://geocities.yahoo.com/ps/info1
> >
> 


__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1



view thread (8+ messages)  latest in thread

Message-ID: <20011004212255.3654.qmail@web10002.mail.yahoo.com>
Permalink:  ../20011004212255.3654.qmail@web10002.mail.yahoo.com/
Also on:    postgresql.org/message-id/20011004212255.3654.qmail@web10002.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jdearl@yahoo.com, swerdlow@maine.rr.com
  Subject: Re: ORDER BY case insensitive?
  In-Reply-To: <20011004212255.3654.qmail@web10002.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox