Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #7783: lower & upper function incorrect work

BUG #7783: lower & upper function incorrect work

From
alex.shniperson@gmail.com
Date:
The following bug has been logged on the website:

Bug reference:      7783
Logged by:          Alexander Shniperson
Email address:      alex.shniperson@gmail.com
PostgreSQL version: 9.1.7
Operating system:   osx lion 10.7.5
Description:        =


Hi.

I have problem at my project, try to do simple steps:
1) create DataBase with
ENCODING =3D 'UTF8'
LC_COLLATE =3D 'C'
LC_CTYPE =3D 'C'

2) create new table at just created database

CREATE TABLE testtable
(
  id serial NOT NULL,
  name character varying(50) NOT NULL,
  CONSTRAINT testtable_pkey PRIMARY KEY (id )
)

3) insert one simple record with Russian word

INSERT INTO testtable (id, name) VALUES (1, '=D0=97=D0=B0=D0=BB=D0=BE=D0=B3=
');

4) try to select data with upper & lower functions with 'like' operator =


select *, lower(name), upper(name) from testtable where lower(name) like
'%=D0=B7=D0=B0=D0=BB=D0=BE=D0=B3%'

5) as result you must see nothing, just fields without data

fix it please, thanks.

Re: BUG #7783: lower & upper function incorrect work

From
Pavel Stehule
Date:
Hello

maybe this is same problem

http://postgresql.1045698.n5.nabble.com/PostgreSQL-UTF-8-and-Mac-OS-X-td185=
2185.html

please, try to use different collate - maybe this advice should to help to =
you

http://yzisin.wordpress.com/2012/01/09/how-to-fix-locale-issues-in-mac-os-x=
-lion-terminal/

regards

Pavel Stehule

2013/1/3  <alex.shniperson@gmail.com>:
> The following bug has been logged on the website:
>
> Bug reference:      7783
> Logged by:          Alexander Shniperson
> Email address:      alex.shniperson@gmail.com
> PostgreSQL version: 9.1.7
> Operating system:   osx lion 10.7.5
> Description:
>
> Hi.
>
> I have problem at my project, try to do simple steps:
> 1) create DataBase with
> ENCODING =3D 'UTF8'
> LC_COLLATE =3D 'C'
> LC_CTYPE =3D 'C'
>
> 2) create new table at just created database
>
> CREATE TABLE testtable
> (
>   id serial NOT NULL,
>   name character varying(50) NOT NULL,
>   CONSTRAINT testtable_pkey PRIMARY KEY (id )
> )
>
> 3) insert one simple record with Russian word
>
> INSERT INTO testtable (id, name) VALUES (1, '=D0=97=D0=B0=D0=BB=D0=BE=D0=
=B3');
>
> 4) try to select data with upper & lower functions with 'like' operator
>
> select *, lower(name), upper(name) from testtable where lower(name) like
> '%=D0=B7=D0=B0=D0=BB=D0=BE=D0=B3%'
>
> 5) as result you must see nothing, just fields without data
>
> fix it please, thanks.
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

Re: BUG #7783: lower & upper function incorrect work

From
Aleksander Shniperson
Date:
Hi, thanks for answer.

Yes this may help, but Postgres must convert it automatically.

03.01.2013, =D7 22:27, Pavel Stehule =CE=C1=D0=C9=D3=C1=CC(=C1):

> Hello
>=20
> maybe this is same problem
>=20
> =
http://postgresql.1045698.n5.nabble.com/PostgreSQL-UTF-8-and-Mac-OS-X-td18=
52185.html
>=20
> please, try to use different collate - maybe this advice should to =
help to you
>=20
> =
http://yzisin.wordpress.com/2012/01/09/how-to-fix-locale-issues-in-mac-os-=
x-lion-terminal/
>=20
> regards
>=20
> Pavel Stehule
>=20
> 2013/1/3  <alex.shniperson@gmail.com>:
>> The following bug has been logged on the website:
>>=20
>> Bug reference:      7783
>> Logged by:          Alexander Shniperson
>> Email address:      alex.shniperson@gmail.com
>> PostgreSQL version: 9.1.7
>> Operating system:   osx lion 10.7.5
>> Description:
>>=20
>> Hi.
>>=20
>> I have problem at my project, try to do simple steps:
>> 1) create DataBase with
>> ENCODING =3D 'UTF8'
>> LC_COLLATE =3D 'C'
>> LC_CTYPE =3D 'C'
>>=20
>> 2) create new table at just created database
>>=20
>> CREATE TABLE testtable
>> (
>>  id serial NOT NULL,
>>  name character varying(50) NOT NULL,
>>  CONSTRAINT testtable_pkey PRIMARY KEY (id )
>> )
>>=20
>> 3) insert one simple record with Russian word
>>=20
>> INSERT INTO testtable (id, name) VALUES (1, '=FA=C1=CC=CF=C7');
>>=20
>> 4) try to select data with upper & lower functions with 'like' =
operator
>>=20
>> select *, lower(name), upper(name) from testtable where lower(name) =
like
>> '%=DA=C1=CC=CF=C7%'
>>=20
>> 5) as result you must see nothing, just fields without data
>>=20
>> fix it please, thanks.
>>=20
>>=20
>>=20
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs

Re: BUG #7783: lower & upper function incorrect work

From
Tom Lane
Date:
alex.shniperson@gmail.com writes:
> PostgreSQL version: 9.1.7
> Operating system:   osx lion 10.7.5

> 1) create DataBase with
> ENCODING = 'UTF8'
> LC_COLLATE = 'C'
> LC_CTYPE = 'C'

> 3) insert one simple record with Russian word

> INSERT INTO testtable (id, name) VALUES (1, 'Залог');

> 4) try to select data with upper & lower functions with 'like' operator

> select *, lower(name), upper(name) from testtable where lower(name) like
> '%залог%'

This is not a bug.  You specified LC_CTYPE = "C", so upper/lower case
conversion converts only ASCII letters.  You need to specify a Russian
locale if you want case conversion to work on Russian letters.

Now having said that, I'm pretty sure that UTF8 locales are completely
broken on OS X, so that this wouldn't work even if you had specified
the right locale.  But that's Apple's fault; there's little we can do
about it.  I believe locale ru_RU.ISO8859-5 (with the matching encoding)
would behave sanely on OS X, if that's an acceptable fix for you.

            regards, tom lane