Re: Fulltext index

Andreas Kraftl <andreas(dot)kraftl(at)kraftl(dot)at> writes:
> CREATE INDEX idx ON table USING gin(
> to_tsvector(
> case
> when a = 'de' then 'german'
> when a = 'en' then 'english'
> else 'english'
> end
> ), b);

> This doesn't work. Error Message in german:
> FEHLER: Zugriffsmethode gin untersttzt keine mehrspaltigen Indexe
> SQL state: 0A000
> means, gin doesn't accept multicolumn indexes.

You've got the parentheses in the wrong place.

The way I'd suggest doing this is

regression=# create table tab (a regconfig, b text);
CREATE TABLE
regression=# create index idx on tab using gin(to_tsvector(a,b));
CREATE INDEX
regression=# explain select * from tab where to_tsvector(a,b) @@ to_tsquery('english','foo');
QUERY PLAN
----------------------------------------------------------------
Index Scan using idx on tab (cost=0.00..8.27 rows=1 width=36)
Index Cond: (to_tsvector(a, b) @@ '''foo'''::tsquery)
(2 rows)

If you want to use abbreviations like 'en' and 'de', create text search
configurations named that way instead of inserting a run-time
conversion.

regards, tom lane