{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/3238","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/3238/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/3238/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/3238/events","html_url":"https://github.com/sequelize/sequelize/issues/3238","id":59274109,"number":3238,"title":"Create indexes adding a where clause (Conditional/Partial index)","user":{"login":"lao","id":197033,"avatar_url":"https://avatars.githubusercontent.com/u/197033?v=3","gravatar_id":"","url":"https://api.github.com/users/lao","html_url":"https://github.com/lao","followers_url":"https://api.github.com/users/lao/followers","following_url":"https://api.github.com/users/lao/following{/other_user}","gists_url":"https://api.github.com/users/lao/gists{/gist_id}","starred_url":"https://api.github.com/users/lao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lao/subscriptions","organizations_url":"https://api.github.com/users/lao/orgs","repos_url":"https://api.github.com/users/lao/repos","events_url":"https://api.github.com/users/lao/events{/privacy}","received_events_url":"https://api.github.com/users/lao/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/sequelize/sequelize/labels/feature","name":"feature","color":"0b02e1"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-02-27T18:20:22Z","updated_at":"2015-02-28T08:27:22Z","closed_at":null,"body":"I am using sequelize for a postgresql database with paranoid.\r\n\r\nI have the current Table:\r\n\r\nTable_A:\r\nidB,\r\nidC,\r\nidD,\r\ndeletedAt\r\n\r\nI want to create an unique index like for the following columns: (idB, idC, idD).\r\n\r\nI created the index using migration.addIndex. And it created a problem because of paranoid.\r\n\r\nLets say we create an instance {idB:1, idC:2, idD:3}, and delete it. After, if we want to create another instance like {idB:1, idC:2, idD:3} we cant, because of the unique index, even though we \"deleted\" the previous one.\r\n\r\nSo I was forced to create the index like this:  (idB, idC, idD, deletedAt).\r\n\r\nBut there is a problem with index for NULL values. Postgres doesn't throw unique constraint if we try to add the following instance twice {idB:1, idC:2, idD:3, deletedAt:null}.\r\n\r\nYou can see this issue on the following stackoverflow post: http://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns\r\n\r\nSo, to do something to solve the problem I have to create two indices. Like this:\r\n\r\nCREATE UNIQUE INDEX \"Table_A_unique_index_with_deletedAt\"\r\n  ON \"Table_A\"\r\n  USING btree\r\n  (\"idB\", \"idC\", \"idD\", \"deletedAt\")\r\n WHERE \"deletedAt\" IS NOT NULL\r\n\r\nCREATE UNIQUE INDEX \"Table_A_unique_index_without_deletedAt\"\r\n  ON \"Table_A\"\r\n  USING btree\r\n  (\"idB\", \"idC\", \"idD\")\r\n WHERE \"deletedAt\" IS NULL\r\n\r\nThe problem is that currently sequelize wont let me create an index with a where clause and I believe that sequelize should have this feature.\r\n\r\nAnd whe using the sync option sequelize should automatically create these 2 indices for paranoid tables, dont you think?"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/76516983","html_url":"https://github.com/sequelize/sequelize/issues/3238#issuecomment-76516983","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3238","id":76516983,"user":{"login":"mickhansen","id":699049,"avatar_url":"https://avatars.githubusercontent.com/u/699049?v=3","gravatar_id":"","url":"https://api.github.com/users/mickhansen","html_url":"https://github.com/mickhansen","followers_url":"https://api.github.com/users/mickhansen/followers","following_url":"https://api.github.com/users/mickhansen/following{/other_user}","gists_url":"https://api.github.com/users/mickhansen/gists{/gist_id}","starred_url":"https://api.github.com/users/mickhansen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mickhansen/subscriptions","organizations_url":"https://api.github.com/users/mickhansen/orgs","repos_url":"https://api.github.com/users/mickhansen/repos","events_url":"https://api.github.com/users/mickhansen/events{/privacy}","received_events_url":"https://api.github.com/users/mickhansen/received_events","type":"User","site_admin":false},"created_at":"2015-02-28T08:27:22Z","updated_at":"2015-02-28T08:27:22Z","body":"https://github.com/sequelize/sequelize/pull/2696 some work was done here but is not ocmplete."}}