Re: Refined LC_COLLATE or multiple database clusters?

On Wed, Jun 09, 2004 at 12:33:03PM +0200, Grega Bremec wrote:
>
> Collate order for those databases, however, needs to be different. Obviously,

[...]

> Is it possible to do either of these things that could solve this problem
> adequately:
>
>     - somehow manage to make one postmaster run on top of two separate
>       database clusters that would each have a different collate ordering
>       scheme
>
>     - use some other method of initializing one database from a different
>       template and taking with it LC_COLLATE setting (I suppose not, as
>       the "${PGDATA}/global/" directory is global to the cluster)
>
>     - use a patch that would add such functionality or upgrade to a version
>       (even if release-candidate, beta is not really an option, i gather)
>       of PostgreSQL that supported it
>
>     - in absence of any other viable solution, change the global setting of
>       the database cluster without having to dump/reinitdb/restore it

If you do not need the collating sequence to affect index operations,
you can use nls_string function to sort using

    order by nls_string(column, 'sl_SI.utf-8')

where any locale can be specified in the runtime. The nls_string
result can also be used to compare strings in collating manner, however,
indexes will not be used in that case, which may or may not be a
problem for your usage.

The source of nls_string with installation instructions can be found
at

    http://www.fi.muni.cz/~adelton/l10n/

--
------------------------------------------------------------------------
 Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
 .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ...
        Only self-confident people can be simple.