Thread: Issue with order by for type varchar
Hi All, I am trying to order a select by the primary key which is a varchar field. I have two known conditions, 1. the varchar always contains 28 characters. ( and, yes, I know the irony of using a variable array on a known fixed length field ) 2. the content of this field contains only the characters used in base 64 encoding. However, when I do an order by I get misplacing of characters so character sequences like "000+..." are coming after "0000....". Is there any way of adding a function that would make the order by do ordering on this field in the same way as the Java Collections.sort() method? Cheers Simon Simon Kelly Java Developer Information Systems Development Information Technology Shared Services Ministry of Health DDI: Mobile: http://www.moh.govt.nz mailto:Simon_Kelly@moh.govt.nz **************************************************************************** Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. **************************************************************************** ************************************************************************************* This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway *************************************************************************************
On Tue, Sep 12, 2006 at 09:51:20AM +1200, Simon_Kelly@moh.govt.nz wrote:
> Hi All,
>
> I am trying to order a select by the primary key which is a varchar field.
>
> 1. the varchar always contains 28 characters. ( and, yes, I know the irony
> of using a variable array on a known fixed length field )
It doesn't make any difference in space usage.
> However, when I do an order by I get misplacing of characters so character
> sequences like "000+..." are coming after "0000....".
Check your LC_COLLATE setting ("show all" should tell you).
> Is there any way of adding a function that would make the order by do
> ordering on this field in the same way as the Java Collections.sort()
> method?
PostgreSQL uses the collation supported by your OS (you didn't say
what OS you're running). I believe Java has its own collation system?
Getting them to equal in general would be difficult, but in your case
it should work.
Which collation are you using in Java and which in postgres?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment
Thanks Martijn. Simon Kelly Java Developer Information Systems Development Information Technology Shared Services Ministry of Health DDI: Mobile: http://www.moh.govt.nz mailto:Simon_Kelly@moh.govt.nz pgsql-general-owner@postgresql.org wrote on 13/09/2006 01:33:15 a.m.: > On Tue, Sep 12, 2006 at 09:51:20AM +1200, Simon_Kelly@moh.govt.nz wrote: > > Hi All, > > > > I am trying to order a select by the primary key which is a varchar field. > > > > 1. the varchar always contains 28 characters. ( and, yes, I know the irony > > of using a variable array on a known fixed length field ) > > It doesn't make any difference in space usage. > > > However, when I do an order by I get misplacing of characters so character > > sequences like "000+..." are coming after "0000....". > > Check your LC_COLLATE setting ("show all" should tell you). > > > Is there any way of adding a function that would make the order by do > > ordering on this field in the same way as the Java Collections.sort() > > method? > > PostgreSQL uses the collation supported by your OS (you didn't say > what OS you're running). I believe Java has its own collation system? > Getting them to equal in general would be difficult, but in your case > it should work. > > Which collation are you using in Java and which in postgres? > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > From each according to his ability. To each according to his > ability to litigate. > [attachment "signature.asc" deleted by Simon Kelly/MOH] **************************************************************************** Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. **************************************************************************** ************************************************************************************* This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway *************************************************************************************