How can I index a substring attribute? - Mailing list pgsql-novice

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