Re: How can I index a substring attribute?

On Thu, 14 Aug 2003, Thiemo Kellner wrote:

> I try to have an index that only contains substrings of an attribute. (This is
> for a try of a port of a MySQL repository).
>
> I did:
> --
> create sequence s_Filename;
>
> create table Filename (
>   FilenameId         integer     default nextval('s_Filename')       not null,
>   Name               text                                            not null,
>   primary key (FilenameId)
> );
>
> create index Filename_Name30_FI on Filename (substr(Name, 1, 30));
> --
>
> However, I got the following:
> --
> bacula=# create sequence s_Filename;
> CREATE SEQUENCE
> bacula=#
> bacula=# create table Filename (
> bacula(#   FilenameId         integer     default nextval('s_Filename')
> not null,
> bacula(#   Name               text
> not null,
> bacula(#   primary key (FilenameId)
> bacula(# );
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'filename_pkey'
> for table 'filename'
> CREATE TABLE
> bacula=#
> bacula=# create index Filename_Name30_FI on Filename (substr(Name, 1, 30));
> ERROR:  parser: parse error at or near "1" at character 59
> --
>
> I also replaced "substr(Name, 1, 30)" with "substring(Name from 1 for 30)" to
> no avail.
>
> Does somebody have an idea?

For 7.3 and earlier, you'll need to wrap the substr in another function
that takes just Name and passes the arguments because the functional
indexes won't take the constant arguments.

In 7.4 the indexes are more powerful and can do this directly (and many
more things too).