pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedupper and UTF-8
11+ messages / 4 participants
[nested] [flat]
* upper and UTF-8
@ 2010-07-26 21:03 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:17 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-27 02:09 ` Re: upper and UTF-8 Alvaro Herrera <alvherre@commandprompt.com>
2010-07-27 14:54 ` Re: upper and UTF-8 Michael Gould <mgould@intermodalsoftwaresolutions.net>
0 siblings, 3 replies; 11+ messages in thread
From: Benjamin Krajmalnik @ 2010-07-26 21:03 UTC (permalink / raw)
To: pgsql-admin
I just used the upper(text) function on a database which is utf8 encoded
and which has spanish text.
All of the regular characters were properly converted, except for
characters which had accents.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
@ 2010-07-26 21:17 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:18 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2010-07-26 21:17 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: pgsql-admin
On Mon, Jul 26, 2010 at 3:03 PM, Benjamin Krajmalnik <kraj@servoyant.com> wrote:
> I just used the upper(text) function on a database which is utf8 encoded and
> which has spanish text.
>
> All of the regular characters were properly converted, except for characters
> which had accents.
What are your various LC_* variables for that database?
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:17 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-07-26 21:18 ` Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:39 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Benjamin Krajmalnik @ 2010-07-26 21:18 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
CREATE DATABASE ishield
WITH OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C'
CONNECTION LIMIT = -1;
> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Monday, July 26, 2010 3:17 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] upper and UTF-8
>
> On Mon, Jul 26, 2010 at 3:03 PM, Benjamin Krajmalnik
> <kraj@servoyant.com> wrote:
> > I just used the upper(text) function on a database which is utf8
> encoded and
> > which has spanish text.
> >
> > All of the regular characters were properly converted, except for
> characters
> > which had accents.
>
> What are your various LC_* variables for that database?
>
> --
> To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:17 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:18 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
@ 2010-07-26 21:39 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:47 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2010-07-26 21:39 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: pgsql-admin
I'd try creating a db with en_US or even better whatever is spanish
encoding for lc_collate and see what happens.
On Mon, Jul 26, 2010 at 3:18 PM, Benjamin Krajmalnik <kraj@servoyant.com> wrote:
> CREATE DATABASE ishield
> WITH OWNER = postgres
> ENCODING = 'UTF8'
> LC_COLLATE = 'C'
> LC_CTYPE = 'C'
> CONNECTION LIMIT = -1;
>
>
>> -----Original Message-----
>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>> Sent: Monday, July 26, 2010 3:17 PM
>> To: Benjamin Krajmalnik
>> Cc: pgsql-admin@postgresql.org
>> Subject: Re: [ADMIN] upper and UTF-8
>>
>> On Mon, Jul 26, 2010 at 3:03 PM, Benjamin Krajmalnik
>> <kraj@servoyant.com> wrote:
>> > I just used the upper(text) function on a database which is utf8
>> encoded and
>> > which has spanish text.
>> >
>> > All of the regular characters were properly converted, except for
>> characters
>> > which had accents.
>>
>> What are your various LC_* variables for that database?
>>
>> --
>> To understand recursion, one must first understand recursion.
>
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:17 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:18 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:39 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-07-26 21:47 ` Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:51 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Benjamin Krajmalnik @ 2010-07-26 21:47 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
Unfortunately, the database has to accept data in multiple languages, since it is a SaaS offering.
It is not a big deal - I just found it interesting that it did not uppercase the accented letters.
The reason I came across it is that I created a table of all the ISO countries. I had found a NySQL script which created it, and it had the fields in both upper case and mixed case. Since our platform is multi-lingual, we expanded the table to add the language code and started adding the translation. After I finished the translation, I figured for consistency I would upper case the one field into the other, and this is where I saw the inconsistency.
Operationally, it does not affect me in any way - but I found it strange that it did not handle the accented characters.
For now we are keeping the column to facilitate the translation to other languages - ultimately it will be dropped.
> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Monday, July 26, 2010 3:39 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] upper and UTF-8
>
> I'd try creating a db with en_US or even better whatever is spanish
> encoding for lc_collate and see what happens.
>
> On Mon, Jul 26, 2010 at 3:18 PM, Benjamin Krajmalnik
> <kraj@servoyant.com> wrote:
> > CREATE DATABASE ishield
> > WITH OWNER = postgres
> > ENCODING = 'UTF8'
> > LC_COLLATE = 'C'
> > LC_CTYPE = 'C'
> > CONNECTION LIMIT = -1;
> >
> >
> >> -----Original Message-----
> >> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> >> Sent: Monday, July 26, 2010 3:17 PM
> >> To: Benjamin Krajmalnik
> >> Cc: pgsql-admin@postgresql.org
> >> Subject: Re: [ADMIN] upper and UTF-8
> >>
> >> On Mon, Jul 26, 2010 at 3:03 PM, Benjamin Krajmalnik
> >> <kraj@servoyant.com> wrote:
> >> > I just used the upper(text) function on a database which is utf8
> >> encoded and
> >> > which has spanish text.
> >> >
> >> > All of the regular characters were properly converted, except for
> >> characters
> >> > which had accents.
> >>
> >> What are your various LC_* variables for that database?
> >>
> >> --
> >> To understand recursion, one must first understand recursion.
> >
>
>
>
> --
> To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:17 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:18 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:39 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:47 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
@ 2010-07-26 21:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:58 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2010-07-26 21:51 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: pgsql-admin
On Mon, Jul 26, 2010 at 3:47 PM, Benjamin Krajmalnik <kraj@servoyant.com> wrote:
> Unfortunately, the database has to accept data in multiple languages, since it is a SaaS offering.
The encoding determines that, not the collation. UTF-8 allows you to
insert various languages in that encoding.
> It is not a big deal - I just found it interesting that it did not uppercase the accented letters.
Just tested it and the lc_collate seems to make the difference.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:17 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:18 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:39 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:47 ` Re: upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:51 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-07-26 21:58 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2010-07-26 21:58 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: pgsql-admin
On Mon, Jul 26, 2010 at 3:51 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Jul 26, 2010 at 3:47 PM, Benjamin Krajmalnik <kraj@servoyant.com> wrote:
>> Unfortunately, the database has to accept data in multiple languages, since it is a SaaS offering.
>
> The encoding determines that, not the collation. UTF-8 allows you to
> insert various languages in that encoding.
>
>> It is not a big deal - I just found it interesting that it did not uppercase the accented letters.
>
> Just tested it and the lc_collate seems to make the difference.
To be more specific, when my lc_collate is en_US, it works properly.
I didn't have to use a spanish collation to make it work. Note that
changing collation will change sort order, and some matching rules and
things like that. Also, a db is usually noticeably faster working
with text in locale of C, because it then treats the data mostly as
though it's in byte order.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
@ 2010-07-27 02:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-07-27 03:12 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2010-07-27 02:09 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: pgsql-admin
Excerpts from Benjamin Krajmalnik's message of lun jul 26 17:03:54 -0400 2010:
> I just used the upper(text) function on a database which is utf8 encoded
> and which has spanish text.
>
> All of the regular characters were properly converted, except for
> characters which had accents.
FWIW it works fine for me:
alvherre=# show lc_collate ;
lc_collate
------------
es_CL.utf8
(1 fila)
alvherre=# select upper('benjamín');
upper
----------
BENJAMÍN
(1 fila)
I suspect that the problem is an incorrect client_encoding setting.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-27 02:09 ` Re: upper and UTF-8 Alvaro Herrera <alvherre@commandprompt.com>
@ 2010-07-27 03:12 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-27 03:36 ` Re: upper and UTF-8 Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2010-07-27 03:12 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Benjamin Krajmalnik <kraj@servoyant.com>; pgsql-admin
On Mon, Jul 26, 2010 at 8:09 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from Benjamin Krajmalnik's message of lun jul 26 17:03:54 -0400 2010:
>> I just used the upper(text) function on a database which is utf8 encoded
>> and which has spanish text.
>>
>> All of the regular characters were properly converted, except for
>> characters which had accents.
>
> FWIW it works fine for me:
>
> alvherre=# show lc_collate ;
> lc_collate
> ------------
> es_CL.utf8
> (1 fila)
>
> alvherre=# select upper('benjamín');
> upper
> ----------
> BENJAMÍN
> (1 fila)
>
> I suspect that the problem is an incorrect client_encoding setting.
Yeah, OP had set lc_collate to C under the mistaken impression that
collation controlled the character sets you could insert into the
database. If you create a db with lc_collate='C' then the upper only
works on basic ascii characters near as I can tell.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-27 02:09 ` Re: upper and UTF-8 Alvaro Herrera <alvherre@commandprompt.com>
2010-07-27 03:12 ` Re: upper and UTF-8 Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-07-27 03:36 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2010-07-27 03:36 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Benjamin Krajmalnik <kraj@servoyant.com>; pgsql-admin
Excerpts from Scott Marlowe's message of lun jul 26 23:12:08 -0400 2010:
> On Mon, Jul 26, 2010 at 8:09 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> > I suspect that the problem is an incorrect client_encoding setting.
>
> Yeah, OP had set lc_collate to C under the mistaken impression that
> collation controlled the character sets you could insert into the
> database. If you create a db with lc_collate='C' then the upper only
> works on basic ascii characters near as I can tell.
Makes sense. The code seems to say that it's lc_ctype that's important
though, see str_toupper in formatting.c. So I think you could still set
collation to C and use a language-specific lc_ctype.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: upper and UTF-8
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
@ 2010-07-27 14:54 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
2 siblings, 0 replies; 11+ messages in thread
From: Michael Gould @ 2010-07-27 14:54 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; pgsql-admin
Benjamin,
We're using the contrib module citext for all text columns so that we can
do case insensitive searches and so far we haven't found any that it
doesn't find.
Best Regards
Mike Gould
"Benjamin Krajmalnik" <kraj@servoyant.com> wrote:
>
>
>I just used the upper(text) function on a database which is utf8 encoded
>and which has spanish text.
>
>
>All of the regular characters were properly converted, except for
>characters which had accents.
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-07-27 14:54 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-26 21:03 upper and UTF-8 Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:17 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:18 ` Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:39 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:47 ` Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-26 21:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-26 21:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-27 02:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-07-27 03:12 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-27 03:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-07-27 14:54 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox