2

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:

  1. How do I properly use the SELECT statement to grab the language from the lang table using its foreign key in the user table?

  2. 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"?

  • what are you trying to create the tsvector from? You specify the language, but what are you vectorizing? – Evan Carroll Aug 2 '17 at 15:26
  • @EvanCarroll, I'm trying to create the tsvector on name || ' ' || screen_name. – Brooks Aug 2 '17 at 15:31
  • that's totally not what tsvectors are for, you should read about fts. – Evan Carroll Aug 2 '17 at 15:34
  • Well, full text search is exactly what I'm trying to do and I did read the docs. The docs say to use to_tsvector. postgresql.org/docs/9.5/static/… – Brooks Aug 2 '17 at 15:40
  • It's not, but don't take my word for it. I just have over a decade working with it. I tried to clear up your confusion in my answer (revised) – Evan Carroll Aug 2 '17 at 15:43
1
  1. You don't. You can't create an index that references another table.
  2. To set the default see default-text-search-config

Workaround

You can however use functions in an index. And functions can references external tables. That said, using this is kind of hack because changing the table (common.lang) will require reindexing and clearing the session cache.

CREATE FUNCTION common.mylookup(id int)
RETURNS regconfig AS $$
  SELECT name::regconfig
  FROM common.lang
  WHERE id = id
$$
LANGUAGE sql
IMMUTABLE;

CREATE INDEX
  ON source.user
  USING GIN (to_tsvector(common.mylookup(profile_lang_id), name || ' ' || screen_name ));

You can mark functions as IMMUTABLE which makes this permissible. If the underlying table mutates you'll have to REINDEX the index. In projects that use this hack, like PostGIS, they recreate indexes on point releases.

Followup

@EvanCarroll, I'm trying to create the tsvector on name || ' ' || screen_name. – Brooks 4 mins ago

Full text search isn't there to do what you think it does. It's not there to search multiple fields. It's there to vectorize word content and make use of dictionaries, stubbing, lexers, gazetteers, stop-word elimination, and a slew of other tricks none of which apply. If this doesn't make sense to you, you'll have to read the docs. If what you want is grep then FTS is only seldom what you want. If you want to grep over small chunks of non-standard text (like names) it's not what you want. What you likely want trigram indexing.

If all you want is a %term% on two fields, you're better off just doing that with a trigram index.

CREATE EXTENSION pg_trgm;
CREATE INDEX ON source.user USING GIN ((name || ' ' || screen_name) gin_trgm_ops);
WHERE name || ' ' || screen_name like '%$1%';

Or even better,

CREATE INDEX ON source.user USING GIN (name gin_trgm_ops, screen_name gin_trgm_ops);
WHERE name LIKE '%$1%' OR screen_name LIKE '%$1%';
  • 1
    Ok, I'm not a DBA by any stretch of the imagination and I'm not trying to argue with you, I'm simply reading the documentation. The documentation says both that in order to create a full-text search index, to use a GIN over a tsvector and that it can be easily done over multiple columns. So, if that's not the way to do it, OK i'm totally open to other ways... – Brooks Aug 2 '17 at 15:46
  • That's not the way to do it. A tsvector can be used over multiple columns, and so can any other index. FTS provides special abilities because of vectorization. This uses dictionaries, stubbing, lexers, gazetteers, stop words and a slew of other tricks none of which apply to names or screen names. It sounds to me like you just want to grep in multiple columns. If what you want is grep then FTS is only seldom what you want. If you want to grep over small chunks of non-standard text (like names) it's not what you want. You want trigram indexing. – Evan Carroll Aug 2 '17 at 15:49
  • So, yes for the purposes of this particular search, I guess I would want just a standard grep-like function, however I am also trying to implement this over other fields in other tables which are more document-style fields where those other tricks would be appropriate. I did not consider that those would be inappropriate for username type fields. Was I trying to use tsvector correctly for the longer document type fields? I will add that to my original question. – Brooks Aug 2 '17 at 15:53
  • Yes, then it makes perfect sense to use tsvector, and my workaround will get you off the ground on that. ;) tsvector will handle the actually document searching very well. – Evan Carroll Aug 2 '17 at 15:54
  • 1
    Works for me! I don't do an awful lot of work with postgresql, so I know enough to be dangerous to myself and haven't touched it or sql code in about a year, so i'm a bit rusty... – Brooks Aug 2 '17 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.