Having a equal (=) operator on GIN-indexable columns

From: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
To: pgsql-general(at)postgresql(dot)org
Subject: Having a equal (=) operator on GIN-indexable columns
Date: 2011-06-13 19:34:27
Message-ID: 4DF66643.6000303@officenet.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

I'm not sure how to write a good $subject for this , but here it goes;
I'd like to have a multi-column index on a varchar-field and a
tsvector-field. GIN only likes tsvector-fields so I'm wondering if it's
possible to create a tsvector from a text which doesn't breake the text
up in vectors, but uses the whole string as the vector instead?

Given the schema:

CREATE TABLE mytable(
id SERIAL PRIMAY KEY,
field VARCHAR NOT NULL,
tsvector_col tsvector NOT NULL
);

I'd like to form a query as follows:
SELECT id FROM mytable WHERE tsvector_col @@ to_tsquery('simple',
'andre:*') AND to_tsvector('simple', field) @@ to_tsquery('simple',
'full_text_search');

And I'd like it to be semantically equivalent of:
SELECT id FROM mytable WHERE tsvector_col LIKE 'andrea%' AND field =
'full_text_search';

But I see that 'full_text_search' is broken up (of course):
andreak=# select to_tsvector('simple', 'full_text_search');
to_tsvector
------------------------------
'full':1 'search':3 'text':2

Is it a way to achieve what I'm trying here?

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
Org.nr: NO 981 479 076 | |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

Browse pgsql-general by date

  From Date Subject
Next Message Scot Kreienkamp 2011-06-13 19:42:07 Help with ERROR: character 0xc280 of encoding "UTF8" has no equivalent in "WIN1252"
Previous Message Magnus Hagander 2011-06-13 17:08:34 Re: Bad link to beta2 source