{"action":"closed","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/8396","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/8396/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/8396/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/8396/events","html_url":"https://github.com/sequelize/sequelize/issues/8396","id":261676828,"number":8396,"title":"Upsert error when Infinity Date is part of primary key","user":{"login":"gabegorelick","id":610129,"avatar_url":"https://avatars1.githubusercontent.com/u/610129?v=4","gravatar_id":"","url":"https://api.github.com/users/gabegorelick","html_url":"https://github.com/gabegorelick","followers_url":"https://api.github.com/users/gabegorelick/followers","following_url":"https://api.github.com/users/gabegorelick/following{/other_user}","gists_url":"https://api.github.com/users/gabegorelick/gists{/gist_id}","starred_url":"https://api.github.com/users/gabegorelick/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gabegorelick/subscriptions","organizations_url":"https://api.github.com/users/gabegorelick/orgs","repos_url":"https://api.github.com/users/gabegorelick/repos","events_url":"https://api.github.com/users/gabegorelick/events{/privacy}","received_events_url":"https://api.github.com/users/gabegorelick/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":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-09-29T15:20:34Z","updated_at":"2017-10-01T07:17:07Z","closed_at":"2017-10-01T07:17:07Z","author_association":"CONTRIBUTOR","body":"## What are you doing?\r\n\r\nI want to add `deletedAt` to my composite primary keys so I can soft delete and reuse identifiers. For example, if you soft delete a user you should be able to register a new user with the same name. Since primary key attributes can't be NULL in Postgres, that means we need to use a different sentinel value like `Infinity` for `deletedAt`.\r\n\r\nTest case added to `upsert.test.js`:\r\n```js\r\nif (dialect.match(/^postgres/)) {\r\n  it('works when deletedAt is Infinity and part of primary key', function () {\r\n    const User = this.sequelize.define('User', {\r\n      name: {\r\n        type: DataTypes.STRING,\r\n        primaryKey: true\r\n      },\r\n      address: DataTypes.STRING,\r\n      deletedAt: {\r\n        type: DataTypes.DATE,\r\n        primaryKey: true,\r\n        allowNull: false,\r\n        defaultValue: Infinity\r\n      }\r\n    }, {\r\n      paranoid: true\r\n    });\r\n\r\n    return User.sync({force: true}).then(() => {\r\n      return User.build({name: 'user1'})\r\n        .save()\r\n        .then(() => {\r\n          return User.upsert({name: 'user1', address: 'address'});\r\n        });\r\n    });\r\n  });\r\n}\r\n```\r\n\r\n## What do you expect to happen?\r\nSequelize should generate SQL that looks something like:\r\n```sql\r\nCREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert() RETURNS integer AS $func$ BEGIN INSERT INTO \"Users\" (\"name\",\"address\",\"deletedAt\",\"createdAt\",\"updatedAt\") VALUES ('user1','address','Infinity','1970-01-01 00:00:00.000 +00:00','1970-01-01 00:00:00.000 +00:00'); RETURN 1; EXCEPTION WHEN unique_violation THEN UPDATE \"Users\" SET \"name\"='user1',\"address\"='address',\"updatedAt\"='1970-01-01 00:00:00.000 +00:00' WHERE ((\"name\" = 'user1' AND \"deletedAt\" = 'Infinity')); RETURN 2; END; $func$ LANGUAGE plpgsql; SELECT * FROM pg_temp.sequelize_upsert()\r\n```\r\nNote the `'Infinity'` in quotes.\r\n\r\n## What is actually happening?\r\nSequelize generates the following SQL:\r\n```sql\r\nCREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert() RETURNS integer AS $func$ BEGIN INSERT INTO \"Users\" (\"name\",\"address\",\"deletedAt\",\"createdAt\",\"updatedAt\") VALUES ('user1','address','Infinity','1970-01-01 00:00:00.000 +00:00','1970-01-01 00:00:00.000 +00:00'); RETURN 1; EXCEPTION WHEN unique_violation THEN UPDATE \"Users\" SET \"name\"='user1',\"address\"='address',\"updatedAt\"='1970-01-01 00:00:00.000 +00:00' WHERE ((\"name\" = 'user1' AND \"deletedAt\" = Infinity)); RETURN 2; END; $func$ LANGUAGE plpgsql; SELECT * FROM pg_temp.sequelize_upsert()\r\n```\r\n\r\nThis causes a `SequelizeDatabaseError: column \"infinity\" does not exist`.\r\n\r\n\r\n__Dialect:__ postgres\r\n__Dialect version:__ XXX\r\n__Database version:__ any\r\n__Sequelize version:__ master\r\n__Tested with master branch:__ Yes\r\n"}}
{"actor":{"display_login":"sushantdhiman"}}