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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83