Sorting is ignoring spaces

Started by Daniel Gaudreaultabout 22 years ago3 messagesgeneral
Jump to latest
#1Daniel Gaudreault
danielg@cadlink.com

One of our developers came across the fact that postgres is ignoring
spaces when sorting results from a query. We are using PGSQL 7.3.4 on
the backend. Is there something that needs to be set somewhere to for
the sort to not ignore spaces?

Thanks,
Dan

#2Alvaro Herrera
alvherre@dcc.uchile.cl
In reply to: Daniel Gaudreault (#1)
Re: Sorting is ignoring spaces

On Wed, Jan 28, 2004 at 09:32:56AM -0500, Daniel Gaudreault wrote:

One of our developers came across the fact that postgres is ignoring
spaces when sorting results from a query. We are using PGSQL 7.3.4 on
the backend. Is there something that needs to be set somewhere to for
the sort to not ignore spaces?

This is locale related; Postgres just uses strcoll. You could try
changing your locale, but it's difficult to do on an existing database.
I think the C locale orders by byte values, but most single-byte locales
have funny rules regarding whitespace.

See strcoll(3).

A workaround could be ask the server to sort not using the column
itself, but a function of it that replaces the spaces with something
else that has the desired sorting effect.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"�C�mo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germ�n Poo)

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Daniel Gaudreault (#1)
Re: Sorting is ignoring spaces

Daniel Gaudreault <danielg@cadlink.com> writes:

One of our developers came across the fact that postgres is ignoring
spaces when sorting results from a query. We are using PGSQL 7.3.4 on
the backend. Is there something that needs to be set somewhere to for
the sort to not ignore spaces?

Sounds like you initdb'd in a non-C locale. Unfortunately the only fix
is to re-initdb. You can't change locale on the fly because it affects
the sort ordering of indexes.

regards, tom lane