{"action":"created","issue":{"url":"https://api.github.com/repos/radar/paranoia/issues/271","labels_url":"https://api.github.com/repos/radar/paranoia/issues/271/labels{/name}","comments_url":"https://api.github.com/repos/radar/paranoia/issues/271/comments","events_url":"https://api.github.com/repos/radar/paranoia/issues/271/events","html_url":"https://github.com/radar/paranoia/issues/271","id":112937210,"number":271,"title":"Unique index migration","user":{"login":"Aupajo","id":14123,"avatar_url":"https://avatars.githubusercontent.com/u/14123?v=3","gravatar_id":"","url":"https://api.github.com/users/Aupajo","html_url":"https://github.com/Aupajo","followers_url":"https://api.github.com/users/Aupajo/followers","following_url":"https://api.github.com/users/Aupajo/following{/other_user}","gists_url":"https://api.github.com/users/Aupajo/gists{/gist_id}","starred_url":"https://api.github.com/users/Aupajo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Aupajo/subscriptions","organizations_url":"https://api.github.com/users/Aupajo/orgs","repos_url":"https://api.github.com/users/Aupajo/repos","events_url":"https://api.github.com/users/Aupajo/events{/privacy}","received_events_url":"https://api.github.com/users/Aupajo/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/radar/paranoia/labels/bug","name":"bug","color":"e11d21"},{"url":"https://api.github.com/repos/radar/paranoia/labels/documentation","name":"documentation","color":"207de5"}],"state":"open","locked":false,"assignee":{"login":"krainboltgreene","id":334809,"avatar_url":"https://avatars.githubusercontent.com/u/334809?v=3","gravatar_id":"","url":"https://api.github.com/users/krainboltgreene","html_url":"https://github.com/krainboltgreene","followers_url":"https://api.github.com/users/krainboltgreene/followers","following_url":"https://api.github.com/users/krainboltgreene/following{/other_user}","gists_url":"https://api.github.com/users/krainboltgreene/gists{/gist_id}","starred_url":"https://api.github.com/users/krainboltgreene/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/krainboltgreene/subscriptions","organizations_url":"https://api.github.com/users/krainboltgreene/orgs","repos_url":"https://api.github.com/users/krainboltgreene/repos","events_url":"https://api.github.com/users/krainboltgreene/events{/privacy}","received_events_url":"https://api.github.com/users/krainboltgreene/received_events","type":"User","site_admin":false},"milestone":null,"comments":8,"created_at":"2015-10-23T03:03:50Z","updated_at":"2015-11-16T05:55:43Z","closed_at":null,"body":"The README suggests the following migration for unique indexes:\r\n\r\n```ruby\r\nadd_index :clients, [:group_id, 'COALESCE(deleted_at, false)'], unique: true\r\n```\r\n\r\nThere are two problems with this:\r\n\r\n1. Using Rails 4.2.4 with Postgres, the migration fails with the following:\r\n   \r\n   ```\r\n   PG::UndefinedColumn: ERROR:  column \"COALESCE(deleted_at, false)\" does not exist\r\n   ```\r\n   \r\n   My Postgres log is reports that the following statement was attempted:\r\n   \r\n   ```sql\r\n   CREATE UNIQUE INDEX  \"index_clients_on_group_id_and_deleted_at\" ON \"clients\"  (\"group_id\", \"COALESCE(deleted_at, false)\")\r\n   ```\r\n   \r\n   The `COALESCE` clause is being escaped and interpreted as a string literal. This may reflect a change in Rails. The Rails API documentation currently states that `add_index` accepts either a symbol or an array of symbols as the second argument. No way to give an unescaped clause is mentioned.\r\n2. In Postgres, running the query as intended results in the following error:\r\n   \r\n   ```\r\n   ERROR:  COALESCE types timestamp without time zone and boolean cannot be matched\r\n   ```\r\n   \r\n   This seems to be indicating that an index on the `deleted_at` field using a value other than the field's type (`false` in this case) is not allowed. This may be a Postgres-specific issue."},"comment":{"url":"https://api.github.com/repos/radar/paranoia/issues/comments/156924016","html_url":"https://github.com/radar/paranoia/issues/271#issuecomment-156924016","issue_url":"https://api.github.com/repos/radar/paranoia/issues/271","id":156924016,"user":{"login":"krainboltgreene","id":334809,"avatar_url":"https://avatars.githubusercontent.com/u/334809?v=3","gravatar_id":"","url":"https://api.github.com/users/krainboltgreene","html_url":"https://github.com/krainboltgreene","followers_url":"https://api.github.com/users/krainboltgreene/followers","following_url":"https://api.github.com/users/krainboltgreene/following{/other_user}","gists_url":"https://api.github.com/users/krainboltgreene/gists{/gist_id}","starred_url":"https://api.github.com/users/krainboltgreene/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/krainboltgreene/subscriptions","organizations_url":"https://api.github.com/users/krainboltgreene/orgs","repos_url":"https://api.github.com/users/krainboltgreene/repos","events_url":"https://api.github.com/users/krainboltgreene/events{/privacy}","received_events_url":"https://api.github.com/users/krainboltgreene/received_events","type":"User","site_admin":false},"created_at":"2015-11-16T05:55:43Z","updated_at":"2015-11-16T05:55:43Z","body":"@serg-kovalev Yeah, SQLite is like that."}}