Andreas Kraftl <andreas.kraftl@kraftl.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« unterstützt 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
