Обсуждение: What locale is my DB using?

I am having some trouble with my database when I try queries of the
SELECT * FROM table WHERE x::text=y::text

I think it may have to do with the locale as the text fields are in EUC.
How can I find out what locale my DB is using?

Thanks,

Jean-Christian Imbeault

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> How can I find out what locale my DB is using?

See pg_controldata (a standard program in 7.3, contrib in the release or
two before).

            regards, tom lane

Tom Lane wrote:
>
> See pg_controldata (a standard program in 7.3, contrib in the release or
> two before).

Thanks!

I have found the following differences between the pg_controldata output
from two postgres installation I had tought were identical:

DB1:

LC_COLLATE:                           C
LC_CTYPE:                             C

DB2:

LC_COLLATE:                           en_US.UTF-8
LC_CTYPE:                             en_US.UTF-8


Both databases contain EUC_JP data and DB2 does not seem to be able to
do EUC_JP text comparisons correctly whereas DB1 is. Am I right in
assuming this is because of the differences in the values of these two
variables?

If this is the cause of the problem how can I change the values of these
variables for DB2? I tried to set them in postgres.conf but that gave an
error:

FATAL:  'LC_COLLATE' is not a valid option nameFATAL:  'LC_COLLATE' is
not a valid option name

Thanks,

Jean-Christian Imbeault

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> If this is the cause of the problem how can I change the values of these
> variables for DB2?

I'm afraid an initdb is the only way ...

            regards, tom lane

Tom Lane wrote:
>
> I'm afraid an initdb is the only way ...

Ok. Luckily this is my test database so an initdb is not a problem.

Hopefully this also explains why my test database, which I had assumed
identical to my production database, was behaving so strangely?

Jean-Christian Imbeault

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> Hopefully this also explains why my test database, which I had assumed
> identical to my production database, was behaving so strangely?

I think so, but I'm not an expert on multibyte issues.

            regards, tom lane