On Thu, 21 Oct 2004, Ray wrote: > Hi All, > > I have a table in my postgres: > Table: doc > Column | Type | Modifiers > ---------------+-----------------------------+----------- > doc_id | bigint | not null > comp_grp_id | bigint | not null > doc_type | character varying(10)| not null > doc_urn | character varying(20)| not null > > I want to create an index on doc_urn column with using substr function like this: > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); > > but there is an error: > > ERROR: parser: parse error at or near "10" at character 68 > > what's wrong for this SQL? As I have found some reference on the > internet, I can't find anything wrong in this SQL.
What version are you using? If you're using anything previous to 7.4 then the above definately won't work and the only work around I know of is to make another function which takes only the column argument and calls substr with the 10 constant. ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match