Re: Index order

On Tue, 16 Sep 2008, Glyn Astill wrote:

> Hi chaps,
>
> I was just wondering if there's any way to tweak the way an an index is ordered on a text field?

Yes, it's called functional index. Write your function, which does
whatever you want with your data and create index (foo(artist))

>
> I have a table with a varchar field "artist" (see table def below), and if I order by the field "artist" it does not order as expected when there are characters such as ^ at the start of the text. For example, the query "select artist from artist order by artist;" orders as follows:
>
> BILLY BRAGG
> BILLY COBHAM & ASERE
> ^ BILLY ELLIOT 40 OFFER
> ^ BILLY ELLIOT HARD ROCK CAFE MEAL OFFER
> BILLY ELLIOT THE MUSICAL
> BILLY OCEAN
> BIOHAZARD
>
>
> I'd expect the rows starting with the caret to appear either at the start or end of, rather than in the middle, it appears as if the index ignores them.
>
> Database locale is Latin1
>
> Am I missing something obvious here?
>
> Thanks
> Glyn
>
>
> CREATE TABLE events.artist
> (
> recnum bigint NOT NULL DEFAULT nextval(('"events"."artist_dfseq"'::text)::regclass),
> artist character varying(50) NOT NULL DEFAULT ' '::character varying,
> price_list character varying(4) NOT NULL DEFAULT ' '::character varying,
> CONSTRAINT artist_index01 PRIMARY KEY (artist)
> )
>
> ALTER TABLE events.artist
> ADD CONSTRAINT artist_index01 PRIMARY KEY(artist);
>
>
>
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83