By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

partial index on a text field

P: n/a
Hi all,

I have a text field which I'll be doing LIKE searches against so I
wanted to set up an index on it.

The data itself is too long to create a full index, so I can't just:

chris=> create index blah on ff_index(icontent);
ERROR: index row requires 21216 bytes, maximum size is 8191

So I thought I'd set up an index to use the first say 200 chars of the
string

chris=> create index blah on ff_index(substring(icontent, 0, 200));
ERROR: syntax error at or near "(" at character 40

I've tried many variations of this, same result.

I know I can create a partial index on it if the field is less than 200
chars, but that doesn't help me.

I'm running v7.4.5.

Any suggestions ?

Thanks,
Chris.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Chris" <ch***@interspire.com> writes:
chris=> create index blah on ff_index(substring(icontent, 0, 200));
ERROR: syntax error at or near "(" at character 40 I'm running v7.4.5.


Put an extra set of parens around it:

create index blah on ff_index((substring(icontent, 0, 200)));

"substring" looks like a function invocation but it isn't exactly,
so you have to treat this as a general expression index. (This little
inconsistency is fixed for 8.0, btw.)

Note that the index will only do you any good if your queries are
specifically written as "substring(icontent, 0, 200) LIKE 'pattern'".
I suspect that you should instead be looking at full-text-indexing
methods (see contrib/tsearch2, for instance).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
Hi Tom,

Perfect, thanks :)

I'll have another look at FTI now to see how it works (though from
memory it's a tedious process to get up and running).

Chris.

-----Original Message-----
From: Tom Lane [mailto:tg*@sss.pgh.pa.us]
Sent: Monday, October 25, 2004 1:57 PM
To: Chris
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] partial index on a text field
"Chris" <ch***@interspire.com> writes:
chris=> create index blah on ff_index(substring(icontent, 0, 200));
ERROR: syntax error at or near "(" at character 40 I'm running v7.4.5.


Put an extra set of parens around it:

create index blah on ff_index((substring(icontent, 0, 200)));

"substring" looks like a function invocation but it isn't exactly, so
you have to treat this as a general expression index. (This little
inconsistency is fixed for 8.0, btw.)

Note that the index will only do you any good if your queries are
specifically written as "substring(icontent, 0, 200) LIKE 'pattern'". I
suspect that you should instead be looking at full-text-indexing methods
(see contrib/tsearch2, for instance).

regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.