Thread
-
ORDER BY case insensitive?
Bob Swerdlow <swerdlow@maine.rr.com> — 2001-10-03T14:06:39Z
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 -
Re: ORDER BY case insensitive?
Jeff Boes <jboes@nexcerpt.com> — 2001-10-03T14:17:35Z
In article <PXEu7.602317$T97.80972819@typhoon.nyroc.rr.com>, "Bob Swerdlow" <swerdlow@maine.rr.com> wrote: > How do I get the rows sorted in a case insensitive way? > SELECT * FROM MyTable ORDER BY Name; Try SELECT * FROM MyTable ORDER BY upper(Name); (or 'lower(Name)'). -- Jeff Boes vox 616.226.9550 Database Engineer fax 616.349.9076 Nexcerpt, Inc. jboes@nexcerpt.com
-
Re: ORDER BY case insensitive?
Jason Earl <jdearl@yahoo.com> — 2001-10-04T19:47:46Z
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
-
Re: ORDER BY case insensitive?
Bob Swerdlow <swerdlow@maine.rr.com> — 2001-10-04T20:55:51Z
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 > -
Re: ORDER BY case insensitive?
Jason Earl <jdearl@yahoo.com> — 2001-10-04T21:22:55Z
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 -
Re: ORDER BY case insensitive?
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-10-04T22:20:20Z
On Thu, 4 Oct 2001, Jason Earl wrote: > 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: Current sources do (and I think older sources may) consider such an index to replace the sort step. On a test table with 100000 strings, explain shows an index scan for the lower(<col>) ordering and a seq scan/sort for <col> ordering. At this point there's actually a performance gain for the lower() index scan.
-
Re: ORDER BY case insensitive?
Keith Gray <keith@heart.com.au> — 2001-10-04T23:56:48Z
Jason Earl wrote: > > 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'; > How would PostgreSQL know to use the index MyTable_lower_idx when I do a ... SELECT * FROM MyTable WHERE lower(name) LIKE 'jas%'; -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:keith@heart.com.au
-
Re: ORDER BY case insensitive?
Tom Lane <tgl@sss.pgh.pa.us> — 2001-10-05T00:32:08Z
Keith Gray <keith@heart.com.au> writes: > How would PostgreSQL know to use the index > MyTable_lower_idx when I do a ... > SELECT * FROM MyTable WHERE lower(name) LIKE 'jas%'; The same way it knows to use any other index: it matches up the things mentioned in the WHERE clause with the available indexes, and then sees whether the clauses they are mentioned in are comparisons that the index can help with. In this case the "thing" mentioned is "function(column)" rather than just "column", but otherwise it's just like a simple index. regards, tom lane