Postgres Pro
Downloads
Home   >   mailing lists

Thread: Index on a function(field)

Index on a function(field)

From
Gabriel Fernandez
Date:
Hi,

Is it possible to create an index using a function(field) sintaxis ?

For instance:

CREATE  INDEX "i1_cdu" on "cdu" using btree ( substr(cdu_code,1,1)
"varchar_ops" );

If not, should I alter the table to include a field with the value
'substr(codigo,1,1)'.

Thanks

Gabi :-)

Re: Index on a function(field)

From
"Mitch Vincent"
Date:
> Is it possible to create an index using a function(field) sintaxis ?

As far as I know you can -- I have lots of indexes on lower(varchar).. There
may be limitations though so I'll let someone else have the final word :-)

-Mitch

Oracle to pg update

From
Gilles DAROLD
Date:
Hi,

Just to tell you that if you have dowloaded the previous version of
Ora2pg (tool to convert Oracle database to PostgreSQL) you must
try the new version. The previous one doesn't work as I expected.

Now I can test it with the Oracle Application DB and so test my
old poor perl code.

Here what it really does now, and todo :

            - Database schema export, with unique, primary and foreign key.

            - Grants/privileges export by user and group.
            - Indexes and unique indexes export.
            - Table or view selection (by name and max table) export.
            - Sequence exportation (todo).
            - Predefined function/trigger export (todo)
            - Data export (todo)
            - Sql query converter (todo)

If some of you can test it and give me reports and ideas it will be more
usefull and less buggy. I will try to do my best to implement sequence
and trigger the new week.

If anyone know if it's possible to know on witch table/column a sequence
is associated, couln'd find any tips on it.

New version can be download at :
http://www.samse.fr/GPL/ora2pg/ora2pg-1.2.tar.gz
or in /contrib/oracle/ when available.

Bruce can you update it. Thanks...

Regards

Gilles DAROLD

Re: Index on a function(field)

From
Tom Lane
Date:
Gabriel Fernandez <gabi@unica.edu> writes:
> Is it possible to create an index using a function(field) sintaxis ?

Yes, but *only* on a function of one or more raw fields.

> CREATE  INDEX "i1_cdu" on "cdu" using btree ( substr(cdu_code,1,1)
> "varchar_ops" );

This doesn't work because you have some constants floating around in the
mix.

In theory you can work around this by creating a custom function, say
firstchar(varchar), that does "substr(x,1,1)", and then writing

CREATE  INDEX "i1_cdu" on "cdu" using btree ( firstchar(cdu_code) );

It's kind of a pain though...

            regards, tom lane

Re: Oracle to pg update

From
Bruce Momjian
Date:
> New version can be download at :
> http://www.samse.fr/GPL/ora2pg/ora2pg-1.2.tar.gz
> or in /contrib/oracle/ when available.
>
> Bruce can you update it. Thanks...

Done in current CVS.  This will not appear in 7.1.X though, just 7.2.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026