Issue with order by for type varchar

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/

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
*************************************************************************************