Re: Unexplained case insensitive results

Hi Lloyd,

Please ignore searches on names that are in lower case. The PBSI-EMR will
search only on first and last names that are in upper case. In this way, we
are synchronized with PBSI-DOC which uses and sends only upper case names,
and we avoid searching problems. Teresa and I have discussed adding extra
fields to the patient tables in which the users can enter names with any
capitalization that they wish to use. These will be the names that they
will see on the screen but the queries and searches will be done on the
upper case names. I have discussed this with Graydon and Teresa several
times in the past two years so you may I prefer that to postpone the
research on this and devote your energies to other endeavors.

_____

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Lloyd Mason
Sent: Friday, March 30, 2007 4:44 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Unexplained case insensitive results

There appears to be a difference in the results from the following SQL
statement when run on an 8.1.5 version versus a 8.1.8 version:

SELECT T1.MRPtPro AS MRPtPro, T1.MRPtDOB, T1.MRPtSSN, T1.MRPtNbr, T2.MRMDNam
AS MRPtProN, T1.MRPtChtCmpFlg, T1.MRPtPhA, T1.MRPtPhP, T1.MRPtPhS,
T1.MRPtFNam, T1.MRPtLNam, T1.MRPtSts, T1.PrtNbr FROM (MR0011 T1 LEFT JOIN
MR00051 T2 ON T2.PrtNbr = T1.PrtNbr AND T2.MRMDNbr = T1.MRPtPro) WHERE
(T1.PrtNbr = 1 and T1.MRPtSts = 'A' and T1.MRPtLNam >= 'person
') AND (T1.MRPtLNam < 'persoo ') ORDER BY T1.PrtNbr,
T1.MRPtSts,

T1.MRPtLNam, T1.MRPtFNam

The column MRPtLAN has both upper and lower case data.

The 8.1.5 version shows all matches both upper and lower case. While the
8.1.8 version shows only those entries that are lower case. Any ideas as to
why this is?

We would like it to show all matches (case-insensitive) that are returned in
8.1.5 but would like to be on a newer version of the database 8.1.8 and
eventually 8.2.X.

Both databases are UTF-8 encoding and running on REDHAT EL 4.0. Both
databases were created from a pg_dump that came out of a 7.4.5 version of
Postgresql with SQL_ASCII encoding. I don't think the encoding should
matter but included it for your review.