Re: Issue with order by for type varchar

On Tue, Sep 12, 2006 at 09:51:20AM +1200, Simon_Kelly(at)moh(dot)govt(dot)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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.