Thread: How can I index a substring attribute?

Hi,

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?

Cheers,

Thiemo

--
root ist die Wurzel allen Übels

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).

Am Donnerstag, 14. August 2003 01.22 schrieb Stephan Szabo:
> 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).
>
> 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).

So, if I understand right, I have to make my own function to wrap subst,
haven't I?

Thanks for advice.

Cheers,

Thiemo

--
root ist die Wurzel allen Übels