Creating Gin index for Postgres stored generated column in Django

Question

I want to setup a Postgres "stored generated columns" with indexing to have full text search on two fields of a model. I created the generated column getting help from this tutorial, but when setting gin index on the column, I get this:

django.core.exceptions.FieldDoesNotExist: Article has no field named 'vector_column'. The app cache isn't ready yet, so if this is an auto-created related field, it won't be available yet.

Here's my Article model:

class Article(models.Model):
    title = models.CharField(...)
    content = models.TextField(...)
    class Meta:
        indexes = [GinIndex(fields=['vector_column'])]

and my custom sql in migration file:

    operations = [
        migrations.RunSQL(
            sql='''
              ALTER TABLE articles_article ADD COLUMN vector_column tsvector GENERATED ALWAYS AS (
                setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
                setweight(to_tsvector('english', coalesce(content,'')), 'B')
              ) STORED;
            ''',

            reverse_sql='''
              ALTER TABLE articles_article DROP COLUMN vector_column;
            '''
        ),
    ]

Is it possible to set index from Django? Seems Django needs the field to be defined on the model but that's not how generated column works. If not, what's the correct way of setting the proper index (mentioned in this Postgres doc, the one at the end of the page) in migration files?

If you create a generated column manually via a migration you can't define the index in your model's Meta, the article only adds the index this way when using the SearchVectorField — Iain Shelvington
– Iain Shelvington, Commented Dec 31, 2021 at 18:29
@IainShelvington So then I should manually create the index in migration files? — Alireza Farahani
– Alireza Farahani, Commented Dec 31, 2021 at 18:37
Yes, that is the only way to add an index to the column since Django does not know about it — Iain Shelvington
– Iain Shelvington, Commented Dec 31, 2021 at 18:42
Thank you very much for your comments. If you submit these commit along with samples for sql and reverse_sql parts, I would accept that as the answer. — Alireza Farahani
– Alireza Farahani, Commented Dec 31, 2021 at 18:46

Alireza Farahani · Accepted Answer · 2022-01-10 15:41:09Z

As Iain said in comments, index must be handled in migrations. I just added another migrations to operations array:

    operations = [
        migrations.RunSQL(
            sql='''
              ALTER TABLE articles_article ADD COLUMN vector_column tsvector GENERATED ALWAYS AS (
                setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
                setweight(to_tsvector('english', coalesce(content,'')), 'B')
              ) STORED;
            ''',

            reverse_sql='''
              ALTER TABLE articles_article DROP COLUMN vector_column;
            '''
        ),
        migrations.RunSQL(
            sql='''
                CREATE INDEX textsearch_idx ON articles_article USING GIN (vector_column);
            ''',

            reverse_sql='''
                DROP INDEX IF EXISTS textsearch_idx;
            '''
        ),
    ]

Collectives™ on Stack Overflow

Creating Gin index for Postgres stored generated column in Django

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related