brianc · brianc · Oct 19, 2016 · Oct 11, 2016 · Oct 11, 2016 · Oct 11, 2016
diff --git a/lib/dialect/mssql.js b/lib/dialect/mssql.js
@@ -381,6 +381,10 @@ Mssql.prototype.visitOnDuplicate = function(onDuplicate) {
   throw new Error('MSSQL does not allow onDuplicate clause.');
 };
 
+Mssql.prototype.visitOnConflict = function(onConflict) {
+  throw new Error('MSSQL does not allow onConflict clause.');
+};
+
 Mssql.prototype.visitReturning = function() {
   // TODO: need to add some code to the INSERT clause to support this since its the equivalent of the OUTPUT clause
   // in MS SQL which appears before the values, not at the end of the statement.

diff --git a/lib/dialect/mysql.js b/lib/dialect/mysql.js
@@ -46,6 +46,10 @@ Mysql.prototype.visitOnDuplicate = function(onDuplicate) {
   return result;
 };
 
+Mysql.prototype.visitOnConflict = function(onConflict) {
+  throw new Error('Mysql does not allow onConflict clause.');
+};
+
 Mysql.prototype.visitReturning = function() {
   throw new Error('MySQL does not allow returning clause.');
 };

diff --git a/lib/dialect/oracle.js b/lib/dialect/oracle.js
@@ -224,6 +224,9 @@ Oracle.prototype.visitCase = function(caseExp) {
   return Mssql.prototype.visitCase.call(this, caseExp);
 };
 
+Oracle.prototype.visitOnConflict = function(onConflict) {
+  throw new Error('Oracle does not allow onConflict clause.');
+};
 
 function isCreateIfNotExists(create){
   if (create.nodes.length===0) return false;
@@ -253,4 +256,5 @@ function isCountStarExpression(columnNode){
   return true;
 }
 
+
 module.exports = Oracle;
diff --git a/lib/dialect/postgres.js b/lib/dialect/postgres.js
@@ -149,6 +149,7 @@ Postgres.prototype.visit = function(node) {
     case 'HAVING'          : return this.visitHaving(node);
     case 'RETURNING'       : return this.visitReturning(node);
     case 'ONDUPLICATE'     : return this.visitOnDuplicate(node);
+    case 'ONCONFLICT'      : return this.visitOnConflict(node);
     case 'FOR UPDATE'      : return this.visitForUpdate();
     case 'FOR SHARE'       : return this.visitForShare();
     case 'TABLE'           : return this.visitTable(node);
@@ -1037,6 +1038,36 @@ Postgres.prototype.visitOnDuplicate = function(onDuplicate) {
   throw new Error('PostgreSQL does not allow onDuplicate clause.');
 };
 
+Postgres.prototype.visitOnConflict = function(onConflict) {
+  var result = ['ON CONFLICT'];
+  var columns = [];
+  var updateClause = [], i;
+
+  if(onConflict.constraint)
+    result.push(['ON CONSTRAINT', this.quote(onConflict.constraint)].join(' '));
+  else if(onConflict.columns) {
+    for(i=0; i < onConflict.columns.length; i++) {
+      columns.push(this.quote(onConflict.columns[i]));
+    }
+    result.push( '(' + columns.join(', ') + ')' );
+  }
+
+  if(onConflict.update){
+    updateClause.push("DO UPDATE SET");
+    var update = onConflict.update;
+    var setClause = [];
+    for(i=0; i<update.length; i++) {
+      setClause.push(this.quote(update[i]) + ' = EXCLUDED.' +this.quote(update[i]));
+    }
+    updateClause.push(setClause.join(', '));
+  }
+  else 
+    updateClause.push('DO NOTHING');
+
+  result.push(updateClause.join(' '));
+  return result; 
+};
+
 Postgres.prototype.visitModifier = function(node) {
   return [node.type, node.count.type ? this.visit(node.count) : node.count];
 };

diff --git a/lib/dialect/sqlite.js b/lib/dialect/sqlite.js
@@ -119,6 +119,10 @@ Sqlite.prototype.visitOnDuplicate = function() {
   throw new Error('SQLite does not allow onDuplicate clause.');
 };
 
+Sqlite.prototype.visitOnConflict = function(onConflict) {
+  throw new Error('Sqlite does not allow onConflict clause.');
+};
+
 Sqlite.prototype.visitReturning = function() {
   throw new Error('SQLite does not allow returning clause.');
 };

diff --git a/lib/node/onConflict.js b/lib/node/onConflict.js
@@ -0,0 +1,7 @@
+'use strict';
+
+var Node = require(__dirname);
+
+module.exports = Node.define({
+  type: 'ONCONFLICT'
+});
diff --git a/lib/node/query.js b/lib/node/query.js
@@ -19,6 +19,7 @@ var Update          = require('./update');
 var Delete          = require('./delete');
 var Returning       = require('./returning');
 var OnDuplicate     = require('./onDuplicate');
+var OnConflict      = require('./onConflict');
 var ForUpdate       = require('./forUpdate');
 var ForShare        = require('./forShare');
 var Create          = require('./create');
@@ -289,6 +290,15 @@ var Query = Node.define({
     return self.add(onDuplicate);
   },
 
+  onConflict: function(o) {
+    var self = this;
+
+    var onConflict = new OnConflict();
+    Object.keys(o).forEach(function(key) {
+        onConflict[key] = o[key];
+    });
+    return self.add(onConflict);
+  },
 
   forUpdate: function() {
     assert(typeof this._select !== 'undefined', 'FOR UPDATE can be used only in a select statement');

diff --git a/test/dialects/insert-tests.js b/test/dialects/insert-tests.js
@@ -626,6 +626,166 @@ Harness.test({
   params: ['test', 2, 'testupdate']
 });
 
+Harness.test({
+  query: post.insert({
+    content: 'test',
+    userId: 2
+  }).onConflict({
+    columns: ['userId'],
+    update: ['content']
+  }),
+  mysql: {
+    throws: true
+  },
+  sqlite: {
+    throws: true
+  },
+  pg: {
+    text  : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2) ON CONFLICT ("userId") DO UPDATE SET "content" = EXCLUDED."content"',
+    string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2) ON CONFLICT ("userId") DO UPDATE SET "content" = EXCLUDED."content"'
+  },
+  mssql: {
+    throws: true
+  },
+  oracle: {
+    throws: true
+  },
+  params: ['test', 2]
+});
+
+Harness.test({
+  query: post.insert({
+    content: 'test',
+    userId: 2
+  }).onConflict({
+    columns: ['userId','content'],
+    update: ['content','userId']
+  }),
+  mysql: {
+    throws: true
+  },
+  sqlite: {
+    throws: true
+  },
+  pg: {
+    text  : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2) ON CONFLICT ("userId", "content") DO UPDATE SET "content" = EXCLUDED."content", "userId" = EXCLUDED."userId"',
+    string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2) ON CONFLICT ("userId", "content") DO UPDATE SET "content" = EXCLUDED."content", "userId" = EXCLUDED."userId"'
+  },
+  mssql: {
+    throws: true
+  },
+  oracle: {
+    throws: true
+  },
+  params: ['test', 2]
+});
+
+Harness.test({
+  query: post.insert({
+    content: 'test',
+    userId: 2
+  }).onConflict({
+    columns: ['userId'],
+    update: ['content']
+  }).where(post.userId.equals(2)),
+  mysql: {
+    throws: true
+  },
+  sqlite: {
+    throws: true
+  },
+  pg: {
+    text  : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2) ON CONFLICT ("userId") DO UPDATE SET "content" = EXCLUDED."content" WHERE ("post"."userId" = $3)',
+    string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2) ON CONFLICT ("userId") DO UPDATE SET "content" = EXCLUDED."content" WHERE ("post"."userId" = 2)'
+  },
+  mssql: {
+    throws: true
+  },
+  oracle: {
+    throws: true
+  },
+  params: ['test', 2, 2]
+});
+
+Harness.test({
+  query: post.insert({
+    content: 'test',
+    userId: 2
+  }).onConflict({
+    constraint: 'conc_userId',
+    update: ['content']
+  }).where(post.userId.equals(2)),
+  mysql: {
+    throws: true
+  },
+  sqlite: {
+    throws: true
+  },
+  pg: {
+    text  : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2) ON CONFLICT ON CONSTRAINT "conc_userId" DO UPDATE SET "content" = EXCLUDED."content" WHERE ("post"."userId" = $3)',
+    string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2) ON CONFLICT ON CONSTRAINT "conc_userId" DO UPDATE SET "content" = EXCLUDED."content" WHERE ("post"."userId" = 2)'
+  },
+  mssql: {
+    throws: true
+  },
+  oracle: {
+    throws: true
+  },
+  params: ['test', 2, 2]
+});
+
+Harness.test({
+  query: post.insert({
+    content: 'test',
+    userId: 2
+  }).onConflict({
+    columns: ['userId'],
+  }),
+  mysql: {
+    throws: true
+  },
+  sqlite: {
+    throws: true
+  },
+  pg: {
+    text  : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2) ON CONFLICT ("userId") DO NOTHING',
+    string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2) ON CONFLICT ("userId") DO NOTHING'
+  },
+  mssql: {
+    throws: true
+  },
+  oracle: {
+    throws: true
+  },
+  params: ['test', 2]
+});
+
+Harness.test({
+  query: post.insert({
+    content: 'test',
+    userId: 2
+  }).onConflict({
+    constraint: 'conc_userId',
+  }),
+  mysql: {
+    throws: true
+  },
+  sqlite: {
+    throws: true
+  },
+  pg: {
+    text  : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2) ON CONFLICT ON CONSTRAINT "conc_userId" DO NOTHING',
+    string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2) ON CONFLICT ON CONSTRAINT "conc_userId" DO NOTHING'
+  },
+  mssql: {
+    throws: true
+  },
+  oracle: {
+    throws: true
+  },
+  params: ['test', 2]
+});
+
 Harness.test({
   query: post.insert([]),
 
@@ -701,6 +861,30 @@ Harness.test({
   params: []
 });
 
+Harness.test({
+  query: post.insert(post.userId).select(user.id).from(user).onConflict({
+    columns: ['userId'],
+    update: ['content']
+  }),
+  pg: {
+    text  : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ON CONFLICT ("userId") DO UPDATE SET "content" = EXCLUDED."content"',
+    string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ON CONFLICT ("userId") DO UPDATE SET "content" = EXCLUDED."content"'
+  },
+  sqlite: {
+    throws: true
+  },
+  mysql: {
+    throws: true
+  },
+  mssql: {
+    throws: true
+  },
+  oracle: {
+    throws: true
+  },
+  params: []
+});
+
 Harness.test({
   query: post.insert(post.userId).add(user.select(user.id)),
   pg: {