pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters)
6+ messages / 4 participants
[nested] [flat]

* defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters)
@ 2006-05-23 07:25 Adam Radlowski <adamr@informatyka.gdansk.pl>
  2006-05-23 08:00 ` Re: defining Your own sort order for already compiled PostgreSQL Robin Iddon <robin@edesix.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Adam Radlowski @ 2006-05-23 07:25 UTC (permalink / raw)
  To: pgsql-admin

I can't find in the documentation, if it is possible to define our own 
sort order for already compiled PostgreSQL.
I need it, because the best for my now building application were be to 
build sort with normal LATIN2 sort order (for non alphanumeric chars 
compatible with ASCII sort order).
I saw in the "configure" file, that it is possible to define, what is 
alpha, what is number and so on, but it is inpossible to administrators 
of ready for use systems to - always recompile PostgreSQL for only one 
application.
With greetings
Adam



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: defining Your own sort order for already compiled PostgreSQL
  2006-05-23 07:25 defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters) Adam Radlowski <adamr@informatyka.gdansk.pl>
@ 2006-05-23 08:00 ` Robin Iddon <robin@edesix.com>
  2006-05-25 10:59   ` Re: defining Your own sort order for already compiled PostgreSQL Adam Radlowski <adamr@informatyka.gdansk.pl>
  0 siblings, 1 reply; 6+ messages in thread

From: Robin Iddon @ 2006-05-23 08:00 UTC (permalink / raw)
  To: Adam Radlowski <adamr@informatyka.gdansk.pl>; +Cc: pgsql-admin

Adam Radlowski wrote:
> I can't find in the documentation, if it is possible to define our own 
> sort order for already compiled PostgreSQL.
> I need it, because the best for my now building application were be to 
> build sort with normal LATIN2 sort order (for non alphanumeric chars 
> compatible with ASCII sort order).
> I saw in the "configure" file, that it is possible to define, what is 
> alpha, what is number and so on, but it is inpossible to 
> administrators of ready for use systems to - always recompile 
> PostgreSQL for only one application.
> With greetings
> Adam

Adam,

I am not sure I completely understand what you're trying to do - 
however, initdb can set the locale for a cluster including setting 
specific locales for collation (which is what I think you want to do).  
I suppose you can invent your own locale if you need some special sort 
order.

These choices are frozen for the cluster (except that you can change the 
encoding for a specific database within the cluster, but that isn't 
going to do what you want, which is change the sort order).

So, if you can re-run initdb then you can do what you want without 
recompiling.

See "man initdb" ...

Hope this helps,

Robin




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: defining Your own sort order for already compiled PostgreSQL
  2006-05-23 07:25 defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters) Adam Radlowski <adamr@informatyka.gdansk.pl>
  2006-05-23 08:00 ` Re: defining Your own sort order for already compiled PostgreSQL Robin Iddon <robin@edesix.com>
@ 2006-05-25 10:59   ` Adam Radlowski <adamr@informatyka.gdansk.pl>
  0 siblings, 0 replies; 6+ messages in thread

From: Adam Radlowski @ 2006-05-25 10:59 UTC (permalink / raw)
  To: Robin Iddon <robin@edesix.com>; +Cc: pgsql-admin

Hello, Robin.
Thank You for Your answer.
What's going on:
Fox example, in old Paradox database system, It was possible to define 
Your own sort order. There was for example many ready-for-use sort 
definitions, like ASCII sort, DOS852,  DOS850. Every sort definition was 
in one file. When You renamed this file as "PARADOX.SOR", Paradox used 
it as default sort order (after restart of Paradox). It was possible to 
make Your own sort definition file.
Very simmilar is situation, when we at the first time run "initdb", but 
I don't know, how to make my own sort definition to use by "initdb". 
There is many read-to-use (like in Paradox) and I (after reading of 
docs) know, how to use them, but I want to define my own - not for 
example de_DE or en_US or pl_PL, but adam_ADAM ;-) and use it for "initdb".
Becouse Hakan's answers didn't help me, so I decided to workaround this 
problem, and - I suppose - with success (the idea looks good, but it is 
not realised to the end yet).
I don't write more about my "workaround idea", becouse it is a lot to 
write and I don't know, if it will be interesting for You (it was my 
problem, not Yours ;-) ).
Greetings from Gdynia
Adam

Robin Iddon wrote:

> Adam Radlowski wrote:
>
>> I can't find in the documentation, if it is possible to define our 
>> own sort order for already compiled PostgreSQL.
>> I need it, because the best for my now building application were be 
>> to build sort with normal LATIN2 sort order (for non alphanumeric 
>> chars compatible with ASCII sort order).
>> I saw in the "configure" file, that it is possible to define, what is 
>> alpha, what is number and so on, but it is inpossible to 
>> administrators of ready for use systems to - always recompile 
>> PostgreSQL for only one application.
>> With greetings
>> Adam
>
>
> Adam,
>
> I am not sure I completely understand what you're trying to do - 
> however, initdb can set the locale for a cluster including setting 
> specific locales for collation (which is what I think you want to 
> do).  I suppose you can invent your own locale if you need some 
> special sort order.
>
> These choices are frozen for the cluster (except that you can change 
> the encoding for a specific database within the cluster, but that 
> isn't going to do what you want, which is change the sort order).
>
> So, if you can re-run initdb then you can do what you want without 
> recompiling.
>
> See "man initdb" ...
>
> Hope this helps,
>
> Robin
>
>
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters)
@ 2006-05-23 08:14 Hakan Kocaman <Hakan.Kocaman@digame.de>
  2006-05-23 19:06 ` Re: defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters) Craig Servin <cservin@cromagnon.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Hakan Kocaman @ 2006-05-23 08:14 UTC (permalink / raw)
  To: Adam Radlowski <adamr@informatyka.gdansk.pl>; pgsql-admin

Hi,

i think this explains, what you ment:
http://www.linuxshare.ru/postgresql/manual/creating-cluster.html
http://www.linuxshare.ru/postgresql/manual/charset.html#LOCALE

So you don't need to set the Locale-Settings on compile-time, 
but on initdb-time.

Also check if this applies to your kinds of query, because with 
a non-C Locale you need to define the indices with a different operator-
class to use the indices with like-comparisons.
See 
http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
for more details.

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de



> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org 
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Adam Radlowski
> Sent: Tuesday, May 23, 2006 9:26 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] defining Your own sort order for already 
> compiled PostgreSQL (because PSQL sort orders are incopatible 
> with ASCII sort order for non -alpha characters)
> 
> 
> I can't find in the documentation, if it is possible to 
> define our own 
> sort order for already compiled PostgreSQL.
> I need it, because the best for my now building application 
> were be to 
> build sort with normal LATIN2 sort order (for non alphanumeric chars 
> compatible with ASCII sort order).
> I saw in the "configure" file, that it is possible to define, what is 
> alpha, what is number and so on, but it is inpossible to 
> administrators 
> of ready for use systems to - always recompile PostgreSQL for 
> only one 
> application.
> With greetings
> Adam
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org 
> so that your
>        message can get through to the mailing list cleanly
> 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters)
  2006-05-23 08:14 Re: defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters) Hakan Kocaman <Hakan.Kocaman@digame.de>
@ 2006-05-23 19:06 ` Craig Servin <cservin@cromagnon.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Craig Servin @ 2006-05-23 19:06 UTC (permalink / raw)
  To: pgsql-admin

Can you clarify this for me?  Does this mean that if we are using UTF-8 as the 
encoding a like clause will not use the index unless we specify 
varchar_pattern_ops as the index operator class?

Craig


On Tuesday 23 May 2006 03:14, Hakan Kocaman wrote:
> Hi,
>
> i think this explains, what you ment:
> http://www.linuxshare.ru/postgresql/manual/creating-cluster.html
> http://www.linuxshare.ru/postgresql/manual/charset.html#LOCALE
>
> So you don't need to set the Locale-Settings on compile-time,
> but on initdb-time.
>
> Also check if this applies to your kinds of query, because with
> a non-C Locale you need to define the indices with a different operator-
> class to use the indices with like-comparisons.
> See
> http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
> for more details.
>
> Best regards
>
> Hakan Kocaman
> Software-Development
>
> digame.de GmbH
> Richard-Byrd-Str. 4-8
> 50829 Köln
>
> Tel.: +49 (0) 221 59 68 88 31
> Fax: +49 (0) 221 59 68 88 98
> Email: hakan.kocaman@digame.de



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters)
@ 2006-05-24 05:45 Hakan Kocaman <Hakan.Kocaman@digame.de>
  0 siblings, 0 replies; 6+ messages in thread

From: Hakan Kocaman @ 2006-05-24 05:45 UTC (permalink / raw)
  To: Craig Servin <cservin@cromagnon.com>; pgsql-admin

jep

best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de



> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org 
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Craig Servin
> Sent: Tuesday, May 23, 2006 9:07 PM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] defining Your own sort order for already 
> compiled PostgreSQL (because PSQL sort orders are incopatible 
> with ASCII sort order for non -alpha characters)
> 
> 
> Can you clarify this for me?  Does this mean that if we are 
> using UTF-8 as the 
> encoding a like clause will not use the index unless we specify 
> varchar_pattern_ops as the index operator class?
> 
> Craig
> 
> 
> On Tuesday 23 May 2006 03:14, Hakan Kocaman wrote:
> > Hi,
> >
> > i think this explains, what you ment:
> > http://www.linuxshare.ru/postgresql/manual/creating-cluster.html
> > http://www.linuxshare.ru/postgresql/manual/charset.html#LOCALE
> >
> > So you don't need to set the Locale-Settings on compile-time,
> > but on initdb-time.
> >
> > Also check if this applies to your kinds of query, because with
> > a non-C Locale you need to define the indices with a 
> different operator-
> > class to use the indices with like-comparisons.
> > See
> > http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
> > for more details.
> >
> > Best regards
> >
> > Hakan Kocaman
> > Software-Development
> >
> > digame.de GmbH
> > Richard-Byrd-Str. 4-8
> > 50829 Köln
> >
> > Tel.: +49 (0) 221 59 68 88 31
> > Fax: +49 (0) 221 59 68 88 98
> > Email: hakan.kocaman@digame.de
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2006-05-25 10:59 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-23 07:25 defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters) Adam Radlowski <adamr@informatyka.gdansk.pl>
2006-05-23 08:00 ` Re: defining Your own sort order for already compiled PostgreSQL Robin Iddon <robin@edesix.com>
2006-05-25 10:59   ` Re: defining Your own sort order for already compiled PostgreSQL Adam Radlowski <adamr@informatyka.gdansk.pl>
2006-05-23 08:14 Re: defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters) Hakan Kocaman <Hakan.Kocaman@digame.de>
2006-05-23 19:06 ` Craig Servin <cservin@cromagnon.com>
2006-05-24 05:45 Re: defining Your own sort order for already compiled PostgreSQL (because PSQL sort orders are incopatible with ASCII sort order for non -alpha characters) Hakan Kocaman <Hakan.Kocaman@digame.de>

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