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
orderas expected when there are characters such as ^ at the start of the text. For example, the query "select artist
fromartist 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
appearsas 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);