Re: Unicode vs SQL_ASCII DBs

From: "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com>
To: "Kris Jurka" <books(at)ejurka(dot)com>
Cc: "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Unicode vs SQL_ASCII DBs
Date: 2004-02-02 11:08:54
Message-ID: 1772.192.168.0.64.1075720134.squirrel@mercury.wardbrook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Kris Jurka said:
> If you need "english" sorting like "en_GB" then that is the best option,
> but if you just need regular sorting the C locale might be better. It is
> sometimes confusing how en_US (I assume GB is similar) sorts strings with
> spaces and punctuation and so on.

If I switch from "en_GB" locale to "C" locale (by recreating the cluster
using "initdb --no-locale"), will I still get accented characters be
sorted correctly, ie tast, test, tést, tost (if the DB encoding is
UNICODE)?

From what I've read, the "C" locale will give me better performance and
optimization for certain functions/expressions (eg "like").

Do you think that the "C" locale (which affects the LC_CTYPE setting) will
allow me to assume that searching and sorting will operate as I
hope/expect, and that all my existing functions etc will work as expected?

> LOWER()/UPPER() only work correctly in a single byte encoding (not
> unicode)

In one pl/pgsql function, I need the ability to lowercase (LOWER) the
three character file extension of a filename stored in the datbase. Will
the LOWER call do nothing with a char/varchar object in a unicode
database?

If so, is there somekind of workaround - I "know" the file extensions that
I'm interested in - can I replace the call to LOWER by a *huge* if then
elsif block of code which does a string match to workout the replacement
extension (in lowercase)?

Thanks again for your patience and help

John Sidney-Woollett

ps I will do some more testing, but with this topic I'm groping around in
the dark...

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2004-02-02 11:11:48 Re: I can't upgrade to PostgreSQL 7.4 in RedHat 9.0
Previous Message Kris Jurka 2004-02-02 10:27:43 Re: Unicode vs SQL_ASCII DBs