Postgres| Create partial GIN index with multiple partial options included in index

Question

Question is regarding partial GIN indexes construction.

I have a following table.

-- auto-generated definition
create table archives_subtitles
(
    id             serial     not null
        constraint archives_subtitles_pkey
            primary key,

    episode_number smallint   not null
        constraint archives_subtitles_episode_number_check
            check (episode_number >= 0),

    text           text       not null,

    language       varchar(2) not null

    season_id      integer    not null
        constraint archives_subtitles_season_id_e3690b93_fk_archives_
            references archives_seasonmodel
            deferrable initially deferred,
    constraint archives_subtitles_season_id_episode_number_fec69da8_uniq
        unique (season_id, episode_number, language)
);

I want to create partial GIN index for FTS like this one:

CREATE INDEX CONCURRENTLY IF NOT EXISTS test ON archives_subtitles
    USING GIN
    (to_tsvector('english', text))
    WITH (fastupdate = off)
    WHERE language = 'en'
;

Problem is that I need to specify few dozens languages in to_tsvector and in WHERE language = 'language_code'

Is it possible to somehow include few options with a few different (to_tsvector('language_name', text)) / WHERE language = 'language_code' pairs inside the index definition instead of manually repeating yourself like this:

CREATE INDEX CONCURRENTLY IF NOT EXISTS test1 ON archives_subtitles
    USING GIN
    (to_tsvector('english', text))
    WITH (fastupdate = off)
    WHERE language = 'en'
;
CREATE INDEX CONCURRENTLY IF NOT EXISTS test2 ON archives_subtitles
    USING GIN
    (to_tsvector('french', text))
    WITH (fastupdate = off)
    WHERE language = 'fr'
;
CREATE INDEX CONCURRENTLY IF NOT EXISTS test3 ON archives_subtitles
    USING GIN
    (to_tsvector('russian', text))
    WITH (fastupdate = off)
    WHERE language = 'ru'
;
etc
etc
etc
…

Thank you.

Laurenz Albe · Accepted Answer · 2020-09-03 19:29:57Z

1

Create a simple IMMUTABLE function like this:

CREATE FUNCTION lang_to_tsconfig(text) RETURNS regconfig
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE WHEN $1 = 'en' THEN 'english'::regconfig
              WHEN $1 = 'de' THEN 'german'::regconfig
              ...
         END$$;

and create your index like:

CREATE INDEX ON archives_subtitles USING gin (
   to_tsvector(lang_to_tsconfig(language), text))
   WITH (fastupdate = off);

answered Sep 3, 2020 at 19:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pepeyumpeter · Accepted Answer · 2021-05-12 09:28:45Z

@Laurenz Albe answer worked for me as in the following code implementation. Maybe someone facing the same issue finds it helpful (commented lines didn't work for me, but maybe someone can tweak them to work)

CREATE FUNCTION lang_to_tsconfig(text) RETURNS regconfig
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE
WHEN $1 = 'ar' THEN 'arabic'::regconfig
-- WHEN $1 = 'be' THEN 'belarusian'::regconfig
-- WHEN $1 = 'bg' THEN 'bulgarian'::regconfig
-- WHEN $1 = 'bs' THEN 'bosnian'::regconfig
-- WHEN $1 = 'cs' THEN 'czech'::regconfig
WHEN $1 = 'da' THEN 'danish'::regconfig
WHEN $1 = 'de' THEN 'german'::regconfig
--WHEN $1 = 'el' THEN 'greek'::regconfig
WHEN $1 = 'en' THEN 'english'::regconfig
WHEN $1 = 'es' THEN 'spanish'::regconfig
--WHEN $1 = 'et' THEN 'estonian'::regconfig
WHEN $1 = 'fi' THEN 'finnish'::regconfig
WHEN $1 = 'fr' THEN 'french'::regconfig
--WHEN $1 = 'he' THEN 'hebrew'::regconfig
--WHEN $1 = 'hr' THEN 'croatian'::regconfig
WHEN $1 = 'hu' THEN 'hungarian'::regconfig
WHEN $1 = 'id' THEN 'indonesian'::regconfig
--WHEN $1 = 'is' THEN 'icelandic'::regconfig
WHEN $1 = 'it' THEN 'italian'::regconfig
--WHEN $1 = 'ja' THEN 'japanese'::regconfig
--WHEN $1 = 'ko' THEN 'korean'::regconfig
WHEN $1 = 'lt' THEN 'lithuanian'::regconfig
--WHEN $1 = 'lv' THEN 'latvian'::regconfig
WHEN $1 = 'nl' THEN 'dutch'::regconfig
WHEN $1 = 'no' THEN 'norwegian'::regconfig
--WHEN $1 = 'pl' THEN 'polish'::regconfig
WHEN $1 = 'pt' THEN 'portuguese'::regconfig
WHEN $1 = 'ro' THEN 'romanian'::regconfig
WHEN $1 = 'ru' THEN 'russian'::regconfig
--WHEN $1 = 'sk' THEN 'slovak'::regconfig
--WHEN $1 = 'sl' THEN 'slovenian'::regconfig
--WHEN $1 = 'sr' THEN 'serbian'::regconfig
WHEN $1 = 'sv' THEN 'swedish'::regconfig
WHEN $1 = 'tr' THEN 'turkish'::regconfig
--WHEN $1 = 'uk' THEN 'ukrainian'::regconfig
--WHEN $1 = 'vi' THEN 'vietnamese'::regconfig
--WHEN $1 = 'zh' THEN 'chinese'::regconfig
         END$$;

Collectives™ on Stack Overflow

Postgres| Create partial GIN index with multiple partial options included in index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related