Re: Index order

Use a functional index!

Il Tuesday 16 September 2008 14:34:44 Glyn Astill ha scritto:
> Hi chaps,
>
> I was just wondering if there's any way to tweak the way an an index is
> ordered on a text field?
>
> 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);