CODEWITHSUNDEEP
pythondjangopostgresql
Ilya Voytov
Ilya Voytov

Reputation: 339

PostgreSQL - create a generated column for a column that already exists

I have the following raw SQL command that I am executing inside a Django migration file. I need to use raw SQL because Django does not support generated columns.

ALTER TABLE dockets_document ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(f_arr2text(content),'')), 'B') ||
    setweight(jsonb_to_tsvector('english', coalesce(tables), '["all"]'), 'C')
) STORED;

My models.py file has the following field:

search_vector = SearchVectorField(null=True)

This line triggers a migration that generates the column for me, then my custom migration applies the SQL. The custom migration fails because the column was already created (with a corresponding index) so ADD COLUMN returns the error ERROR: column "search_vector" of relation "dockets_document" already exists. I tried using ALTER COLUMN in place of ADD COLUMN but it did not work (ERROR: syntax error at or near "tsvector").

I tried removing the field from the models.py file but then Django doesn't know that the field exists and won't allow me to query against the column. And it doesn't feel right to remove it either.

How can I convert the existing null column into a GENERATED column instead?

Upvotes: 0

Views: 895

Answers (1)

iklinac
iklinac

Reputation: 15718

You could do RunSQL and add state_operation as documented

The state_operations argument allows you to supply operations that are equivalent to the SQL in terms of project state. For example, if you are manually creating a column, you should pass in a list containing an AddField operation here so that the autodetector still has an up-to-date state of the model

in manually generated empty migration

Upvotes: 2

Related Questions