Django database model creation and indexing for Postgres

Question

I'm trying to create new table and index the columns in Postgres through Django model. The table creation uses another table's setup and the indexing uses pg_trgm

I can do it directly on Postgres by

CREATE TABLE my_table (like my_other_table);

and Insert data like

INSERT INTO my_table SELECT * FROM MY_OTHER_TABLE TABLESAMPLE BERNOULLI (5);

This will take 5% of the data from my_other_table into my_table

Then I index the column by

CREATE INDEX mycolumn_trgm_idx ON my_table USING GIN (my_column gin_trgm_ops);

There's no issue when I perform these actions on postgres directly, but I don't know how (or if it's possible) to do it through django migration

dghy · Accepted Answer · 2018-10-03 18:03:29Z

1

I think something like this should be possible. First create your models.py Classes so they represent tables that you want to take the data from (there is also possibility of auto discovery of existing database - details here). For indexing you can set index_db=True in the required model Class fields. When the model is set up create migrations by ./manage.py makemigrations and ./manage.py migrate. Django should create/update fields in the DB it's connected to. In the end you would need to create your own migration and take required data from one table (Model) and put it in the other table (Model). Here's how to write custom migrations. Don't forget to write necessary functions inside migrations file as Model methods won't work during migration.

answered Oct 3, 2018 at 18:03

dghy

1051 silver badge7 bronze badges

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

3 Comments

JChao Over a year ago

thanks! I have no issue with migration itself. I just have never done it using commands like LIKE and don't know if Django supports that. Also setting db_index=True creates a default btree index, but I'm trying to use a custom index, which I don't know if django supports. I guess I can run migrations.RunSQL in migration script, but I don't know if there's a cleaner way to do it

dghy Over a year ago

Maybe this could help a little

Waqas Ali Over a year ago

@JChao You can create different types of indexes in PostgreSQL using Django. Details here: docs.djangoproject.com/en/2.2/ref/contrib/postgres/indexes

Collectives™ on Stack Overflow

Django database model creation and indexing for Postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related