I had posted a question I had about why two databases I had that were
supposed to be identical were not. I did some more research into my
problem and my best guess points to a problem with language encoding.
Can anyone suggest what else I could to find out the cause of this
problem and hopefully solve it?
Problem:
I have a production 7.3.2 database that I 'pg_dump -C'. I used the
dumped file in order to re-create the database on another machine also
running 7.3.2. However the 'new' database seems to have identical
content but *not* identical behaviour.
I some more testing on tables containing EUC_JP text and found that the
TEST db does not seem to be able to do 'text'='text' comparison
correctly (the output is in EUC_JP so shows up as ??? in this email):
TAL=# select invoices.ward, wards.ward, wards.id from wards where
ward=invoices.ward;
NOTICE: Adding missing FROM-clause entry for table "invoices"
ward | ward | id
--------+--------+------
?????| ?????| 1016
?????| ?????| 1023
?????| ?????| 1023
??????| ??????| 1026
?????? | ?????? | 1038
(5 rows)
TEST=# select invoices.ward, wards.ward, wards.id from wards where
ward=invoices.ward;
NOTICE: Adding missing FROM-clause entry for table "invoices"
ward | ward | id
--------+--------+------
?????| ?????| 1016
?????| ?????| 1023
?????| ?????| 1023
??????| ??????| 1026
?????? | ?????? | 1038
?????| ?????? | 2065
(6 rows)
The last row of the TEST query does *not* contain a match yet it is
returned as a matching row ....
But both databases have the same encoding:
Name | Owner | Encoding
-----------+----------+----------
TAL | postgres | EUC_JP
Name | Owner | Encoding
-----------+----------+----------
TEST | postgres | EUC_JP
Why does my TEST db have a problem matching on ECU_JP text? What can I
do to find the root cause of the problem?
Thanks!
Jean-Christian Imbeault