{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/8552","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/8552/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/8552/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/8552/events","html_url":"https://github.com/sequelize/sequelize/issues/8552","id":269192920,"number":8552,"title":"Upsert uses a WHERE clause with both primary key and unique indices on Postgres","user":{"login":"DanielBrookRoberge","id":9043311,"avatar_url":"https://avatars1.githubusercontent.com/u/9043311?v=4","gravatar_id":"","url":"https://api.github.com/users/DanielBrookRoberge","html_url":"https://github.com/DanielBrookRoberge","followers_url":"https://api.github.com/users/DanielBrookRoberge/followers","following_url":"https://api.github.com/users/DanielBrookRoberge/following{/other_user}","gists_url":"https://api.github.com/users/DanielBrookRoberge/gists{/gist_id}","starred_url":"https://api.github.com/users/DanielBrookRoberge/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DanielBrookRoberge/subscriptions","organizations_url":"https://api.github.com/users/DanielBrookRoberge/orgs","repos_url":"https://api.github.com/users/DanielBrookRoberge/repos","events_url":"https://api.github.com/users/DanielBrookRoberge/events{/privacy}","received_events_url":"https://api.github.com/users/DanielBrookRoberge/received_events","type":"User","site_admin":false},"labels":[{"id":28979678,"url":"https://api.github.com/repos/sequelize/sequelize/labels/bug","name":"bug","color":"d7e102","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2017-10-27T18:17:55Z","updated_at":"2017-11-03T05:00:31Z","closed_at":null,"author_association":"NONE","body":"<!--\r\n\r\nPlease note this is an issue tracker, not a support forum.\r\nFor general questions, please use StackOverflow or Slack.\r\n\r\nFor bugs, please fill out the template below.\r\n\r\n-->\r\n\r\n## What are you doing?\r\nI am using `upsert` to update models that have both a primary key and a composite unique constraint which is `DEFERRABLE INITIALLY DEFERRED`. When I try to change the members of the unique constraint I get a key uniqueness error.\r\n\r\nModel:\r\n```js\r\nmodule.exports = (sequelize, DataTypes) => {\r\n  return sequelize.define(\r\n    'rule',\r\n    {\r\n      id: {\r\n        type: DataTypes.UUID,\r\n        primaryKey: true,\r\n        defaultValue: DataTypes.UUIDV4,\r\n      },\r\n      priority: {\r\n        type: DataTypes.INTEGER,\r\n        allowNull: false,\r\n        defaultValue: 0,\r\n        unique: 'rules_priority_parent_id'\r\n      },\r\n      parent_id: {\r\n        type: DataTypes.UUID,\r\n        allowNull: false,\r\n        unique: 'rules_priority_parent_id'\r\n      }\r\n    },\r\n    {\r\n      tableName: 'rules',\r\n      timestamps: true\r\n    }\r\n  )\r\n}\r\n```\r\n\r\nThen do:\r\n```js\r\nmodels.rule.create({\r\n  id: 5,\r\n  priority: 0,\r\n  parent_id: 20\r\n})\r\n\r\nmodels.rule.create({\r\n  id: 10,\r\n  priority: 1,\r\n  parent_id: 20\r\n})\r\n\r\nmodels.sequelize.transaction(() => (\r\n  models.rule.upsert({\r\n    id: 5,\r\n    priority: 1,\r\n    parent_id: 20\r\n  })\r\n    .then(() => models.rule.upsert({\r\n      id: 10,\r\n      priority: 0,\r\n      parent_id: 20\r\n    }))\r\n))\r\n```\r\n\r\n## What do you expect to happen?\r\n_The `priority` of the two rows to be swapped_\r\n\r\n## What is actually happening?\r\n_Uniqueness constraint error_\r\n\r\nI get a constraint error on the uniqueness of the primary key.\r\n\r\n_Postgres output_:\r\n```\r\nERROR:  duplicate key value violates unique constraint \"rules_pkey\"\r\nDETAIL:  Key (id)=(5) already exists.\r\nCONTEXT:  SQL statement \"UPDATE \"rules\" SET \"priority\"=1, \"parent_id\"=20, \"id\"=5, \"updated_at\"='2017-10-27 17:37:23.120 +00:00' WHERE (\"id\" = 5 OR (\"priority\" = 1 AND \"parent_id\" = 20))\"\r\nPL/pgSQL function pg_temp_3.sequelize_upsert() line 1 at SQL statement\r\n```\r\n\r\nI see a fix for sequelize@3 for the same problem (issue #6240, PR #6243), but it appears to still be a problem in sequelize@4.\r\n\r\n__Dialect:__ postgres\r\n__Dialect version:__ pg@6.4.2\r\n__Database version:__ 9.6.3\r\n__Sequelize version:__ 4.19.0\r\n__Tested with master branch:__ Yes\r\n\r\n**Note :** _Your issue may be ignored by maintainers if it's not tested against `master` branch OR does not follow issue template._\r\n"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/341622962","html_url":"https://github.com/sequelize/sequelize/issues/8552#issuecomment-341622962","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/8552","id":341622962,"user":{"login":"sushantdhiman","id":9989487,"avatar_url":"https://avatars1.githubusercontent.com/u/9989487?v=4","gravatar_id":"","url":"https://api.github.com/users/sushantdhiman","html_url":"https://github.com/sushantdhiman","followers_url":"https://api.github.com/users/sushantdhiman/followers","following_url":"https://api.github.com/users/sushantdhiman/following{/other_user}","gists_url":"https://api.github.com/users/sushantdhiman/gists{/gist_id}","starred_url":"https://api.github.com/users/sushantdhiman/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sushantdhiman/subscriptions","organizations_url":"https://api.github.com/users/sushantdhiman/orgs","repos_url":"https://api.github.com/users/sushantdhiman/repos","events_url":"https://api.github.com/users/sushantdhiman/events{/privacy}","received_events_url":"https://api.github.com/users/sushantdhiman/received_events","type":"User","site_admin":false},"created_at":"2017-11-03T05:00:31Z","updated_at":"2017-11-03T05:00:31Z","author_association":"OWNER","body":"@iamjochem No return is required as that arrow function is using implicit return format"}}
{"actor":{"display_login":"sushantdhiman"}}