Thread: Sorting is ignoring spaces

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

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)

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