Fulltext index

Hello again,

my previous post wasn't answered. I think i told my question wrong :-).

I have a table like
a | b
--------------------
'de' | Hallo welt
'en' | Hello world

How can I create a full text index over b?
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.

Any ideas?

Greetings Andreas
--
Kraftl EDV - Dienstleistungen
Linux, Linuxschulungen, Webprogrammierung
Autofabrikstraße 16/6
1230 Wien

On Sat, 08 Nov 2008 09:44:17 +0100
Andreas Kraftl <andreas(dot)kraftl(at)kraftl(dot)at> wrote:

> Hello again,

> my previous post wasn't answered. I think i told my question
> wrong :-).
>
> I have a table like
> a | b
> --------------------
> 'de' | Hallo welt
> 'en' | Hello world
>
> How can I create a full text index over b?
> 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.
>
> Any ideas?

Multicolumn indexes should get into 8.4.

You may add a column tsvector and compute it with a trigger that
chose the correct language when generating the tsvector.
Then you'll have to pick up the correct language when you generate
the tsquery in your search.

http://www.sigaev.ru/gin/fastinsert_and_multicolumn_GIN.pdf

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Sat, 8 Nov 2008, Andreas Kraftl wrote:

> Hello again,
>
> my previous post wasn't answered. I think i told my question wrong :-).
>
> I have a table like
> a | b
> --------------------
> 'de' | Hallo welt
> 'en' | Hello world
>
> How can I create a full text index over b?

use concatenation operator: tsvector('de',b_de) || tsvector('en',b_en)

> 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.
>
> Any ideas?
>
> Greetings Andreas
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Am Sat, 08 Nov 2008 09:44:17 +0100 schrieb Andreas Kraftl:
> How can I create a full text index over b?

Hello

thanks for the answers. But nothing matches my problem.

I read the manual again and decide me for an other way.
I change my table that it looks like:

lang | text
----------------------
german | hallo welt
english | hello world

Then i follow the manual which says:
http://www.postgresql.org/docs/8.3/interactive/textsearch-
tables.html#TEXTSEARCH-TABLES-INDEX
It is possible to set up more complex expression indexes wherein the
configuration name is specified by another column, e.g.:
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body));

It is possible to set up more complex expression indexes wherein the
configuration name is specified by another column, e.g.:

I did a
CREATE INDEX idx ON test.test1 USING gin(to_tsvector(lang, "text"));
and
CREATE INDEX idx ON test.test1 USING gin(to_tsvector(lang::regconfig,
"text"));

For both I get this error in german:
FEHLER: Funktionen im Indexausdruck müssen als IMMUTABLE markiert sein
SQL state: 42P17
means in english
functions in index expression must be marked IMMUTABLE

Now I have no idea. My experience with databases and postgresql are too
less to decide if this is a bug, or myself is the bug ;-).

Thanks again for help
Andreas

On Mon, Nov 10, 2008 at 09:14:21AM +0100, Andreas Kraftl wrote:
> Am Sat, 08 Nov 2008 09:44:17 +0100 schrieb Andreas Kraftl:
> > How can I create a full text index over b?
>
> thanks for the answers. But nothing matches my problem.

I'm not sure what's wrong with Oleg's suggestion--he's the guy who wrote
most of the code so his suggestions should be reasonable! I'm just
learning about this stuff myself, so it may be somewhat sub-optimal.
That said, I got things working when doing the following:

CREATE TABLE test (
lang TEXT,
text TEXT
);

INSERT INTO test VALUES
('german', 'hallo welt'),
('english', 'hello world');

CREATE INDEX idx ON test USING gin(tsvector_concat(
to_tsvector('german', CASE lang WHEN 'german' THEN text ELSE '' END),
to_tsvector('english', CASE lang WHEN 'english' THEN text ELSE '' END)));

"text" as a column name gets a bit confusing to read, but I'm trying
to follow your names. Also my version of PG didn't seem to know that
the '||' operator knows how to concat tsvectors, so I had to spell out
tsvector_concat in full. Querying is a bit awkward, but works:

SELECT *
FROM test
WHERE tsvector_concat(
to_tsvector('english', CASE lang WHEN 'english' THEN text ELSE '' END),
to_tsvector('german', CASE lang WHEN 'german' THEN text ELSE '' END))
@@ to_tsquery('english', 'hello');

Putting most of the above into a query would work, as would having PG
automatically maintaining a column of type TSVECTOR.

> I read the manual again and decide me for an other way.
> I change my table that it looks like:
>
> lang | text
> ----------------------
> german | hallo welt
> english | hello world

What types do these columns have? if "lang" is of type REGCONFIG all
works for me:

CREATE TABLE test (
lang REGCONFIG,
text TEXT
);

INSERT INTO test VALUES
('german', 'hallo welt'),
('english', 'hello world');

CREATE INDEX idx ON test USING gin(to_tsvector(lang, text));

SELECT *
FROM test
WHERE to_tsvector(lang, text) @@ to_tsquery('english', 'hello');

This all seems much easier than having "lang" as a TEXT column.

> Now I have no idea. My experience with databases and postgresql are too
> less to decide if this is a bug, or myself is the bug ;-).

I think the awkward thing is that text/strings are visually indistin-
guishable from arbitrary literals in SQL. The 'english' that's going
into the to_tsquery() call above is actually of type REGCONFIG, but it
looks like a string literal. I think that could be why you were getting
confused before.

Hope that all makes sense and helps a bit!

Sam

Before waking up properly, I wrote:
> Querying is a bit awkward, but works:
>
> SELECT *
> FROM test
> WHERE tsvector_concat(
> to_tsvector('english', CASE lang WHEN 'english' THEN text ELSE '' END),
> to_tsvector('german', CASE lang WHEN 'german' THEN text ELSE '' END))
> @@ to_tsquery('english', 'hello');
>
> Putting most of the above into a query would work

That should be "Putting most of the above into a VIEW would work"! The
example would be:

CREATE VIEW test_v AS
SELECT lang, text, tsvector_concat(
to_tsvector('english', CASE lang WHEN 'english' THEN text ELSE '' END),
to_tsvector('german', CASE lang WHEN 'german' THEN text ELSE '' END))
AS tsvec
FROM test;

Allowing you to do:

SELECT lang, text
FROM test_v
WHERE tsvec @@ to_tsquery('english', 'hello');

Sam