473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

langauges, locales, regex, LIKE

If I've read everything right, in order to get:

multiple languages on a site

with the functionality of ALL of:

REGEX
LIKE
Correctly sorted text

A site would have to:

create a cluster for every language needed
run a separate database instance for every language
and have the database instances each have their own port
and use 8 bit encoding for that specific language

because:

Sorting is fixed at cluster/directory creation per single
database instance
And LIKE only works on C Locale with an eight bit encoding
and sorting (MAYBE?) works only on 8 bit encoding
when using C Locale.

If anyone can correct me on this, I'd love to hear it.

Boy, the old LOCALE system has really got to go someday.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
7 2015
> If I've read everything right, in order to get:

multiple languages on a site

with the functionality of ALL of:

REGEX
LIKE
Correctly sorted text

...


You might want to look at this:

http://www.unicode.org/reports/tr10/

to get an idea of where things stand at the Unicode Consortium.

But, for sorting mixed content, what sort order should take effect
between "now" and $B!V8=;~E@!W(B (the latter being Japanese)?

A little more down-to-earth, would you want "genjiten" and the hiragana
equivalent, $B!V$2$s$8$F$s!W(B to fold together in the collation? You
definitely would need a Japanese locale for that, if it ever could work.
(I'm not sure it could work, unless you had some way to specify the
method of romanization in a sublocale or something.)

And what happens when you mix "genjiten" with "genealogy"? Or the US
English name "Moench" with the German name using the correct character
for the "oe", with the romanized Japanese "moeru", and the same ($B!V$b$((B
$B$k!W(B) in hiragana?

--
Joel <re**@ddcom.co.jp>
Just ranting, ignore me.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
Dennis Gearon wrote:
If I've read everything right, in order to get:

multiple languages on a site

with the functionality of ALL of:

REGEX
LIKE
Correctly sorted text

A site would have to:

create a cluster for every language needed
run a separate database instance for every language
and have the database instances each have their own port
and use 8 bit encoding for that specific language
You'd need a separate database, not a separate cluster. Each database
can then have their own encoding and locale.
because:

Sorting is fixed at cluster/directory creation per single
database instance
To clarify, a cluster is a group of databases that share user logins and
can all be accessed via the same server.
And LIKE only works on C Locale with an eight bit encoding
and sorting (MAYBE?) works only on 8 bit encoding
when using C Locale.
You can sort, and I believe use LIKE on UTF etc. However, index use is a
different matter.
If anyone can correct me on this, I'd love to hear it.

Boy, the old LOCALE system has really got to go someday.


The issue isn't so much the difficulty of supporting multiple locales
(AFAIK). I believe it's more to do with interactions. If you have a
table containing multiple languages in the same column, what does it
mean to sort that table? Do you sort by language-name then by languages?
If you don't, what rules do you follow?

What happens if we compare different languages?
Does fr/fr:"a" == en/gb:"a"?
Does en/gb:"hello" == en/us:"hello"?

Messy, isn't it?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3
For what it's worth, we have a unicode 7.4.1 database which gives us the
sorting and searching behaviour that we expect (with the exception of
the upper and lower functions). We access the data via jdbc so we don't
have to deal with encoding issues per se as the driver does any
translation for us.

Currently we don't use any LIKE statements, but if we did, and wanted
them optimized then we'd use the appropriate OP Class when defining the
index. We also don't use any REGEX expressions. And we'll shortly be
experimenting with tsearch2...

List of databases
Name | Owner | Encoding
---------------+----------+----------
test | postgres | UNICODE

Setting the psql client encoding to Latin1 and inserting the following
data...

# select * from johntest;
id | value
----+-------
1 | test
2 | tést
3 | tèst
4 | taste
5 | TEST
6 | TÉST
7 | TÈST
8 | TASTE
(8 rows)

and then extracting the data in sorted order works as we would expect

# select * from johntest order by value (no index on the value field)
id | value
----+-------
8 | TASTE
5 | TEST
7 | TÈST
6 | TÉST
4 | taste
1 | test
3 | tèst
2 | tést
(8 rows)

however, applying the UPPER function to the data does not work as
expected, problem with ids 6,7,3,2 - should be ordered (3,7 or 7,3) ,
(6,2 or 2,6)

# select * from johntest order by upper(value);
id | value
----+-------
4 | taste
8 | TASTE
1 | test
5 | TEST
7 | TÈST
6 | TÉST
3 | tèst
2 | tést
(8 rows)

using a LIKE operation also works as expected (again no index on value
field)

# select * from johntest where value like 't%';
id | value
----+-------
1 | test
2 | tést
3 | tèst
4 | taste
(4 rows)

Here's our pg_controldata output:
version number: 72
Catalog version number: 200310211
Database cluster state: in production
pg_control last modified: Thu 24 Jun 2004 07:18:56 GMT
Current log file ID: 0
Next log file segment: 29
Latest checkpoint location: 0/1CA5F8D8
Prior checkpoint location: 0/1C8F2074
Latest checkpoint's REDO location: 0/1CA5F8D8
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's StartUpID: 17
Latest checkpoint's NextXID: 42355483
Latest checkpoint's NextOID: 29814
Time of latest checkpoint: Thu 24 Jun 2004 07:18:54 GMT
Database block size: 8192
Blocks per segment of large relation: 131072
Maximum length of identifiers: 64
Maximum number of function arguments: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: C
LC_CTYPE: C

and our locale is

locale
LANG=en_GB.UTF-8
LC_CTYPE="en_GB.UTF-8"
LC_NUMERIC="en_GB.UTF-8"
LC_TIME="en_GB.UTF-8"
LC_COLLATE="en_GB.UTF-8"
LC_MONETARY="en_GB.UTF-8"
LC_MESSAGES="en_GB.UTF-8"
LC_PAPER="en_GB.UTF-8"
LC_NAME="en_GB.UTF-8"
LC_ADDRESS="en_GB.UTF-8"
LC_TELEPHONE="en_GB.UTF-8"
LC_MEASUREMENT="en_GB.UTF-8"
LC_IDENTIFICATION="en_GB.UTF-8"
LC_ALL=

We are intending to support various European languages on our website,
and so far the unicode seems to be working for us.

But maybe we' re just happy in our ignorance!

John Sidney-Woollett

Dennis Gearon wrote:
If I've read everything right, in order to get:

multiple languages on a site

with the functionality of ALL of:

REGEX
LIKE
Correctly sorted text

A site would have to:

create a cluster for every language needed
run a separate database instance for every language
and have the database instances each have their own port
and use 8 bit encoding for that specific language

because:

Sorting is fixed at cluster/directory creation per single
database instance
And LIKE only works on C Locale with an eight bit encoding
and sorting (MAYBE?) works only on 8 bit encoding
when using C Locale.

If anyone can correct me on this, I'd love to hear it.

Boy, the old LOCALE system has really got to go someday.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4
Richard Huxton <de*@archonet.com> writes:
You'd need a separate database, not a separate cluster. Each database
can then have their own encoding and locale.


Not so, unfortunately. You get one locale per cluster, at least for
LC_COLLATE and LC_CTYPE which are the significant settings for this
discussion.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #5
Richard Huxton wrote:
Dennis Gearon wrote:
If I've read everything right, in order to get:

multiple languages on a site

with the functionality of ALL of:
REGEX
LIKE
Correctly sorted text

A site would have to:

create a cluster for every language needed
run a separate database instance for every language
and have the database instances each have their own port
and use 8 bit encoding for that specific language

You'd need a separate database, not a separate cluster. Each database
can then have their own encoding and locale.


If I wanted all the languages to be running concurently, I can't switch clusters that the database is connected to on the fly, right? The database stays in the cluster it was started in, right? So, if that's true, then I need separate database instances if I want truly accurate sorting.
because:

Sorting is fixed at cluster/directory creation per single
database instance

To clarify, a cluster is a group of databases that share user logins and
can all be accessed via the same server.
And LIKE only works on C Locale with an eight bit encoding
and sorting (MAYBE?) works only on 8 bit encoding
when using C Locale.

You can sort, and I believe use LIKE on UTF etc. However, index use is a
different matter.


Yup, there is no facility to declare character sets for indexes.
If anyone can correct me on this, I'd love to hear it.

Boy, the old LOCALE system has really got to go someday.

The issue isn't so much the difficulty of supporting multiple locales
(AFAIK). I believe it's more to do with interactions. If you have a
table containing multiple languages in the same column, what does it
mean to sort that table? Do you sort by language-name then by languages?
If you don't, what rules do you follow?

What happens if we compare different languages?
Does fr/fr:"a" == en/gb:"a"?
Does en/gb:"hello" == en/us:"hello"?

Messy, isn't it?

Without languge specific characters, they will sort exactly the same.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #6


Dennis Gearon wrote:
John Sidney-Woollett wrote:
For what it's worth, we have a unicode 7.4.1 database which gives us
the sorting and searching behaviour that we expect (with the
exception of the upper and lower functions). We access the data via
jdbc so we don't have to deal with encoding issues per se as the
driver does any translation for us.

Currently we don't use any LIKE statements, but if we did, and wanted
them optimized then we'd use the appropriate OP Class when defining
the index. We also don't use any REGEX expressions. And we'll shortly
be experimenting with tsearch2...

List of databases
Name | Owner | Encoding
---------------+----------+----------
test | postgres | UNICODE

Setting the psql client encoding to Latin1 and inserting the
following data...

# select * from johntest;
id | value
----+-------
1 | test
2 | tést
3 | tèst
4 | taste
5 | TEST
6 | TÉST
7 | TÈST
8 | TASTE
(8 rows)

[snip]

using a LIKE operation also works as expected (again no index on
value field)

# select * from johntest where value like 't%';
id | value
----+-------
1 | test
2 | tést
3 | tèst
4 | taste
(4 rows)

Like works, but it can't use an index, and so would have horibble
performance vs. the situation where it CAN use an index. I believe
this is how Postgres is working now.

If you use one of the OPCLASSes then LIKE operations using indexes
should work, I believe.

See http://www.postgresql.org/docs/7.4/s...s-opclass.html

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #7
John Sidney-Woollett wrote:
For what it's worth, we have a unicode 7.4.1 database which gives us the
sorting and searching behaviour that we expect (with the exception of
the upper and lower functions). We access the data via jdbc so we don't
have to deal with encoding issues per se as the driver does any
translation for us.

Currently we don't use any LIKE statements, but if we did, and wanted
them optimized then we'd use the appropriate OP Class when defining the
index. We also don't use any REGEX expressions. And we'll shortly be
experimenting with tsearch2...

List of databases
Name | Owner | Encoding
---------------+----------+----------
test | postgres | UNICODE

Setting the psql client encoding to Latin1 and inserting the following
data...

# select * from johntest;
id | value
----+-------
1 | test
2 | tést
3 | tèst
4 | taste
5 | TEST
6 | TÉST
7 | TÈST
8 | TASTE
(8 rows)

and then extracting the data in sorted order works as we would expect

# select * from johntest order by value (no index on the value field)
id | value
----+-------
8 | TASTE
5 | TEST
7 | TÈST
6 | TÉST
4 | taste
1 | test
3 | tèst
2 | tést
(8 rows)

however, applying the UPPER function to the data does not work as
expected, problem with ids 6,7,3,2 - should be ordered (3,7 or 7,3) ,
(6,2 or 2,6)

# select * from johntest order by upper(value);
id | value
----+-------
4 | taste
8 | TASTE
1 | test
5 | TEST
7 | TÈST
6 | TÉST
3 | tèst
2 | tést
(8 rows)

using a LIKE operation also works as expected (again no index on value
field)

# select * from johntest where value like 't%';
id | value
----+-------
1 | test
2 | tést
3 | tèst
4 | taste
(4 rows)

Like works, but it can't use an index, and so would have horibble performance vs. the situation where it CAN use an index. I believe this is how Postgres is working now.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ellen Herzfeld | last post by:
I have been tearing my hair out trying to get Python to sort strings containing accented characters according to the French locale settings on Mac OS X (10.3). I think I've done it correctly...
2
by: garykpdx | last post by:
Is there some sort of tutorial on locales or the locale module? I can't seem to find a list showing all possible locales. I made 'en' work alright, but when I tried 'de' or 'de_DE' or 'es_ES',...
2
by: Neil Morris | last post by:
I would like to know the benefits for javascripts' prototype objects as compared to say java class-based objects. The main points that I have on my mind are: 1. Is there a way to mark properties...
7
by: saroj.yadav | last post by:
As I understand it (correct me, if I am wrong) Unicode came into picture so that a document containing multiple language characters can be supported like somebody can write a document comparing...
17
by: clintonG | last post by:
I'm using an .aspx tool I found at but as nice as the interface is I think I need to consider using others. Some can generate C# I understand. Your preferences please... <%= Clinton Gallagher ...
3
by: Joshua Stewart | last post by:
Hi, I am trying to characterise a problem I am seeing on our C#/C++ xml driven application. We have recently added some basic Spanish language support to our application, but it seems that there...
8
by: Rik | last post by:
Hi all, is there a in PHP to get the available locales without shellacces (i.e. locale -a)? Grtz, -- Rik Wasmus
2
by: Adrian | last post by:
Hi All, Is there anyway to change what isspace thinks is a space character. I am parsing some log files and it would be nice to just read a field as what ever is between quotes or between 's ie...
4
by: Gilles Ganault | last post by:
Hello What does it take to support locales? The following test code found on the PHP site doesn't work as planned: ======= setlocale(LC_TIME, "C"); echo strftime("%A"); setlocale(LC_TIME,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.