Обсуждение: BUG #7783: lower & upper function incorrect work
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.
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
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
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