Обсуждение: order by
Is there a known problem with the SQL order by clause within postgres version 7.2.3?
I am trying to sort a field which holds file names that have some spaces as well non-alphanumeric characters, including underscores (see below).
The results of:
select study_name from study order by study_name;
Study1a
Study 1b
Study 1c
Study1d
The results of sorting in Perl (which uses Linux's underlying sort function)
Study 1c
Study 1b
Study1a
Study1d
Is there any way around this difference?
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
Jodi Kanter <jkanter@virginia.edu> writes:
> Is there a known problem with the SQL order by clause within postgres versi=
> on 7.2.3?
No ... but it does honor the sorting rules of the locale you initialize
the database in. You might want to re-initdb with LC_ALL set to C.
regards, tom lane
When we originally initialized I set the LC_COLLATE=C. Was this not enough? We set it in the environment variables for the postgres user. Thanks Jodi ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Jodi Kanter" <jkanter@virginia.edu> Cc: "Postgres SQL List" <pgsql-sql@postgresql.org> Sent: Thursday, March 27, 2003 1:57 AM Subject: Re: [SQL] order by > Jodi Kanter <jkanter@virginia.edu> writes: > > Is there a known problem with the SQL order by clause within postgres versi= > > on 7.2.3? > > No ... but it does honor the sorting rules of the locale you initialize > the database in. You might want to re-initdb with LC_ALL set to C. > > regards, tom lane >
Jodi Kanter <jkanter@virginia.edu> writes:
> When we originally initialized I set the LC_COLLATE=C. Was this not enough?
Should have been ... but see what pg_controldata says about the actual
setting.
regards, tom lane
Can I look at this via the command line? within psql? or in one of the .conf files? ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Jodi Kanter" <jkanter@virginia.edu> Cc: "Postgres SQL List" <pgsql-sql@postgresql.org> Sent: Thursday, March 27, 2003 9:38 AM Subject: Re: [SQL] order by > Jodi Kanter <jkanter@virginia.edu> writes: > > When we originally initialized I set the LC_COLLATE=C. Was this not enough? > > Should have been ... but see what pg_controldata says about the actual > setting. > > regards, tom lane >
I figured out how to run it and it says that LC_COLLATE and LC_CTYPE are equal to en_US. Can someone please outline the steps to correcting this. I thought we had done it correctly from initialization but apparently not. If I need to reinitialize now will that mess with my current databases? Will I have to shut down production, do a pg_dump and restore? Other than the sort ordering are there any other problems with NOT having the environment set to "C?" Thanks Jodi ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Jodi Kanter" <jkanter@virginia.edu> Cc: "Postgres SQL List" <pgsql-sql@postgresql.org> Sent: Thursday, March 27, 2003 9:38 AM Subject: Re: [SQL] order by > Jodi Kanter <jkanter@virginia.edu> writes: > > When we originally initialized I set the LC_COLLATE=C. Was this not enough? > > Should have been ... but see what pg_controldata says about the actual > setting. > > regards, tom lane >
On 27/3/03 14:55, "Jodi Kanter" <jkanter@virginia.edu> wrote: > Other than the sort ordering are there any other problems with NOT having > the environment set to "C?" Pattern matching e.g. "where foo ~~ 'bar%'" doesn't use an index on foo unless the locale is C. This can cause a significant performance hit. For examples as above you can workaround by defining a substring function for a functional index, but it's a bit clumsy. Must get round to doing a fresh initdb one of these days... ;-) Julian Scarfe