0

In my project I want to get people who have birthday between some days, I hope to find a solution which does not force any limitations to queries.

I have found this solution which seems efficient and suite for my problem. But now I have a second problem to create the function in database using django ORM, because this must be portable and works with test database also. I could not find any proper way to able to define the function and the index based on it in django.

In brief I want to create below function in database using django:

CREATE OR REPLACE FUNCTION indexable_month_day(date) RETURNS TEXT as $BODY$
SELECT to_char($1, 'MM-DD');
$BODY$ language 'sql' IMMUTABLE STRICT;

CREATE INDEX person_birthday_idx ON people (indexable_month_day(dob));
motam
  • 677
  • 1
  • 6
  • 24

1 Answers1

0

To answer your question, using RunSQL you can insert raw SQL into a migration -- it looks like you should be able to put this raw SQL into a migration file, including the function that would create the custom index. So running the migration would create the custom in

But don't do this -- you should just use Django to index the dob field, i.e. 

dob = models.DateField(db_index=True)

and use Django to write your queries as well.

Mark Chackerian
  • 21,866
  • 6
  • 108
  • 99
  • But `dob` is a `DateField` and I want just to use month and day from it and I try to do this as efficient as possible something like has explained in the post I have shared, so I think using just `db_index` is impossible for this manner. – motam Apr 25 '18 at 05:48
  • If I use a raw sql statement in migration, how could I track this in my git repo? Do you think it is true to add this migration to git repo? I think this could corrupt migrations when another one get the repo and use `makemigrations` because the order migrations are applied could not define there. – motam Apr 25 '18 at 05:51
  • Yes, it's standard practice to put migrations into your git repo. Django migrations have some dependency order logic to ensure that they are run in the correct order. – Mark Chackerian Apr 25 '18 at 12:57