Re: Fulltext index

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