CODEWITHSUNDEEP
djangopostgresqldjango-migrations
Alireza Farahani
Alireza Farahani

Reputation: 2539

Creating Gin index for Postgres stored generated column in Django

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?

Upvotes: 1

Views: 1752

Answers (1)

Alireza Farahani
Alireza Farahani

Reputation: 2539

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;
            '''
        ),
    ]

Upvotes: 2

Related Questions