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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
