I'm attempting to create a GIN index for full-text search on a field that may have text in it that is one of several different languages. The languages are pre-set and we'll know which language we want to search in at query time. Here is the schema I've got thus far...
CREATE SCHEMA source; CREATE SCHEMA common; CREATE TABLE common.lang ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, code TEXT NOT NULL UNIQUE, created_at TIMESTAMP NOT NULL DEFAULT now() ); INSERT INTO common.lang (name, code) VALUES ('english', 'en'), ('arabic', 'ar'), ('chinese', 'zh'), ('undefined', 'und'); CREATE TABLE source.user ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, screen_name TEXT NOT NULL UNIQUE, profile_lang_id INTEGER NOT NULL REFERENCES common.lang(id) ON DELETE CASCADE ON UPDATE CASCADE, created_at TIMESTAMP NOT NULL DEFAULT now() ); CREATE INDEX source_user_names_idx ON source.user USING GIN (to_tsvector(SELECT common.lang.name FROM source.user INNER JOIN common.lang ON common.lang.id = source.user.profile_lang_id, name || ' ' || screen_name));
It reports a syntax error at or near "SELECT", which makes sense...I didn't think I could just shoehorn a select statement in there, but I'm not sure how to correctly use a select statement within the to_tsvector call.
I looked at https://stackoverflow.com/a/21299033/4471711, however that hardcodes the languages, whereas I want the languages to be pulled from a table dynamically at index creation time. Obviously, if/when we add a language, we would need to recreate the index.
I also looked at https://stackoverflow.com/a/15135730/4471711, however that is not in support of creating an index and only requires 1 parameter to to_tsvector, whereas I seem to need to pass in both the language and the text column.
I am also trying to create a full-text search index on a more document-style field as below:
CREATE TABLE source.post ( id BIGSERIAL PRIMARY KEY, text TEXT NOT NULL ); CREATE INDEX status_text_idx ON source.post USING GIN (to_tsvector(common_lang_id, text));
Again, with the above index, I want to also use the language which is specified by the foreign key to the lang table.
My two questions are:
How do I properly use the SELECT statement to grab the language from the lang table using its foreign key in the user table?
What if the lang is "undefined" as is one of the options? I wouldn't mind defaulting to english for purposes of the to_tsvector call...How do I add in that default while still allowing the user table to reference "undefined"?