This project is archived and is in readonly mode.

#1333 ✓stale
Jason Yuen

Partial indices using add_index in DB migrations

Reported by Jason Yuen | November 5th, 2008 @ 01:25 PM | in 3.x

Currently using Rails 2.1.1 with Postgres 8.3.1 and it doesn't seem like add_index provides the ability to create partial indices on database columns (same as a regular index but with a WHERE clause). Not sure if partial indicies can be created in other database systems but it would be nice to be able to have support for this as an option when using add_index.

My workaround is to add the index manually in an execute statement in my migration. Is this the recommended approach?

Comments and changes to this ticket

  • Jason Yuen

    Jason Yuen November 5th, 2008 @ 02:05 PM

    • Title changed from “ Partial indicies using add_index in DB migrations” to “ Partial indices using add_index in DB migrations”
  • Pratik

    Pratik March 12th, 2009 @ 05:28 PM

    • State changed from “new” to “incomplete”

    Could you please submit a patch for this ? should come handy.

    Thanks !

  • Max Lapshin

    Max Lapshin April 20th, 2009 @ 05:35 PM

    Jason. It is very easy to monkey patch schema adaptor to add this functionality, but full patch is rather hard, because database driver must dump properly data of partial index.

    Take attached monkey patch and put it into config/initializers/ add_index :people, [:gender], :name => "male_idx", :options => "WHERE gender = 'm'"

  • CancelProfileIsBroken

    CancelProfileIsBroken August 6th, 2009 @ 01:53 PM

    • Tag changed from db, index, migrations, partial to bugmash, db, index, migrations, partial
  • Jason Yuen

    Jason Yuen August 6th, 2009 @ 02:32 PM

    Just heard about BugMash and I would like to help out with this ticket. I was envisioning the same type of patch (adding options) but I noticed you (Max) mentioned that the full patch is rather hard. Can you please clarify what you mean by the database driver needing to dump data of the partial index? Thanks for clarifying!

  • Elad Meidar

    Elad Meidar September 27th, 2009 @ 08:57 PM

    @Jason: as far as i can see (Google, that is), mysql does not support "where" clauses in CREATE INDEX syntax.. could it be that it's just a postgres's feature?

  • Jason Yuen

    Jason Yuen December 11th, 2009 @ 03:15 AM

    Since the where clause would be added as an option, is it fair to say that create_index will simply generate an invalid statement if the optional parameters (WHERE clause or otherwise) are invalid for the particular database you are using? I see this as similar to calls to find_by_sql where, if you put DB-specific syntax, it's usually because you know that you are using a particular database.

  • Rizwan Reza

    Rizwan Reza February 12th, 2010 @ 12:46 PM

    • Tag changed from bugmash, db, index, migrations, partial to db, index, migrations, partial
  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • Jeff Kreeftmeijer

    Jeff Kreeftmeijer November 8th, 2010 @ 08:27 AM

    • Tag cleared.

    Automatic cleanup of spam.

  • Santiago Pastorino

    Santiago Pastorino February 9th, 2011 @ 12:31 AM

    • State changed from “incomplete” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • Santiago Pastorino

    Santiago Pastorino February 9th, 2011 @ 12:31 AM

    • State changed from “open” to “stale”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>