The following bug has been logged online:

Bug reference: 1590
Logged by: Michaela
Email address: rambo1503-postgresql (AT) yahoo (DOT) de
PostgreSQL version: 8.0.1
Operating system: SuSE Linux 9.1
Description: Comparison Operation with Strings
Details:

Hello!

conditions:
-----------
Our database is defined with SQL_ASCII. We also tested Latin1.

CREATE TABLE test(feld varchar(100))
INSERT INTO test VALUES ('ABC');
INSERT INTO test VALUES ('?');
INSERT INTO test VALUES ('100');
INSERT INTO test VALUES ('B');
INSERT INTO test VALUES ('? ABC');
INSERT INTO test VALUES ('Z');

We want to use e.g. the following conditions:

1.example
---------
select * from test where field>='?' and field<'A'

result:
?
100

But it doesn`t find the '? ABC'. And '100' is wrong.

This command should give me this output:
?
? ABC

2.example
---------
select * from test where feld>='?' and feld<'@'

result:
?

It doesn`t find the '? ABC'.

This command should give me this output:
?
? ABC

3.example
---------
select * from test where feld>='A' and feld<'C'

result:
ABC
B
? ABC

the correct result would be:
ABC
B

We tested it with PostgreSQL version 7.2.1. There it works.

Kind Regards
Michaela

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Michaela" <rambo1503-postgresql (AT) yahoo (DOT) de> writes:
This is determined by locale, not encoding. I suspect you have a
non-C locale selected.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Thu, 7 Apr 2005, Michaela wrote:

This depends on the locale, not the encoding.

Specifically, for example, 'A' < '? ABC' in en_US and probably other
similar locales.

For the data you give, en_US and de_DE appear to sort as:
?
100
ABC
? ABC
B
Z

If you want byte order sorting, you need to make sure to initdb in C
locale.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend