Re: create index with substr function

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.