int4 in a GiST index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello all,

I am using tsearch2 to (imagine this... :) index a text field.  There
is also a, for lack of a better name, "classification" field called
'field' that will be used to group certain rows together.

CREATE TABLE biblio.metarecord_field_entry (
        record          BIGINT  REFERENCES biblio.metarecord (id)
                                        ON UPDATE CASCADE
                                        ON DELETE SET NULL
                                        DEFERRABLE
                                        INITIALLY DEFERRED,
        field           INT     NOT NULL
                                REFERENCES biblio.metarecord_field_map (id)
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE
                                        DEFERRABLE
                                        INITIALLY DEFERRED,
        value           TEXT,
        value_fti       tsvector,
        source          BIGINT  NOT NULL
                                REFERENCES biblio.record (id)
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE
                                        DEFERRABLE
                                        INITIALLY DEFERRED
) WITHOUT OIDS;


Because there will be "or" queries against the 'value_fti' I want to
create a multi-column index across the tsvector and classification
columns as that should help with selectivity.  But because there is no
GiST opclass for INT4 the index creation complains thusly:

  oils=# CREATE INDEX metarecord_field_entry_value_and_field_idx ON
biblio.metarecord_field_entry USING GIST (field, value_fti);
  ERROR:  data type integer has no default operator class for access
method "gist"
  HINT:  You must specify an operator class for the index or define a
default operator class for the data type.

I attempted to give it the 'int4_ops' class, but that also complains:

  oils=# CREATE INDEX metarecord_field_entry_value_and_field_idx ON
biblio.metarecord_field_entry USING GIST (value_fti, field int4_ops);
  ERROR:  operator class "int4_ops" does not exist for access method "gist"

I couldn't find any info in the docs (7.4 and 8.0.0b4) for getting
GiST to index standard integers.  I'm sure this has been done before,
but I've note found the magic spell.  Of course, I may just be barking
up the wrong tree altogether...

Thanks in advance!


-- 
Mike Rylander
mrylander@xxxxxxxxx
GPLS -- PINES Development
Database Developer


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux