{"id":1062,"date":"2023-03-22T15:50:23","date_gmt":"2023-03-22T07:50:23","guid":{"rendered":"https:\/\/blog.frost-s.com\/?p=1062"},"modified":"2023-03-22T15:50:23","modified_gmt":"2023-03-22T07:50:23","slug":"postgresql-on-duplicate-key-update","status":"publish","type":"post","link":"https:\/\/blog.frost-s.com\/index.php\/2023\/03\/22\/postgresql-on-duplicate-key-update\/","title":{"rendered":"PostGreSQL ON DUPLICATE KEY UPDATE"},"content":{"rendered":"\n
\n

PGsql \u4e5f\u6709\u7c7b\u4f3c\u4e8emysql ON DUPLICATE KEY UPDATE \u7684\u8bed\u6cd5<\/p>\n\n\n\n

\u6ce8\u610f\uff0cON CONFLICT \u53ea\u5728 PostgreSQL 9.5 \u4ee5\u4e0a\u53ef\u7528\u3002<\/p>\n<\/blockquote>\n\n\n\n

\n

PostgreSQL \u7684 upsert \u529f\u80fd\uff1a\u5f53\u8bb0\u5f55\u4e0d\u5b58\u5728\u65f6\uff0c\u6267\u884c\u63d2\u5165\uff1b\u5426\u5219\uff0c\u8fdb\u884c\u66f4\u65b0\u3002<\/p>\n<\/blockquote>\n\n\n\n

<\/a><\/a>PostgreSQL \u7684 upsert \u7b80\u4ecb<\/h1>\n\n\n\n

\u5728\u5173\u7cfb\u6570\u636e\u5e93<\/a>\u4e2d\uff0c\u672f\u8bed upsert \u88ab\u79f0\u4e3a\u5408\u5e76(merge)\u3002\u610f\u601d\u662f\uff0c\u5f53\u6267\u884c INSERT \u64cd\u4f5c\u65f6\uff0c\u5982\u679c\u6570\u636e\u8868\u4e2d\u4e0d\u5b58\u5728\u5bf9\u5e94\u7684\u8bb0\u5f55\uff0cPostgreSQL \u6267\u884c\u63d2\u5165\u64cd\u4f5c\uff1b\u5982\u679c\u6570\u636e\u8868\u4e2d\u5b58\u5728\u5bf9\u5e94\u7684\u8bb0\u5f55\uff0c\u5219\u6267\u884c\u66f4\u65b0\u64cd\u4f5c\u3002\u8fd9\u5c31\u662f\u4e3a\u4ec0\u4e48\u5c06\u5176\u79f0\u4e3a upsert\uff08update or insert\uff09\u7684\u539f\u56e0\u3002<\/p>\n\n\n\n

\u901a\u8fc7 INSERT<\/a> ON CONFLICT \u6765\u4f7f\u7528 upsert \u529f\u80fd\uff1a<\/p>\n\n\n\n

INSERT<\/strong> INTO<\/strong> table_name(column_list) VALUES<\/strong>(value_list)\nON<\/strong> CONFLICT<\/strong> target action;\n<\/code><\/pre>\n\n\n\n
target \u53ef\u4ee5\u662f\uff1a<\/p>\n\n\n\n
    \n
  • (column_name)\uff1a\u4e00\u4e2a\u5b57\u6bb5\u540d<\/li>\n\n\n\n
  • ON CONSTRAINT constraint_name\uff1a\u5176\u4e2d\u7684 constraint_name \u53ef\u4ee5\u662f\u4e00\u4e2a\u552f\u4e00\u7ea6\u675f\u7684\u540d\u5b57<\/li>\n\n\n\n
  • WHERE predicate\uff1a\u5e26\u8c13\u8bed\u7684 WHERE \u5b50\u53e5<\/li>\n<\/ul>\n\n\n\n
    action \u53ef\u4ee5\u662f\uff1a<\/p>\n\n\n\n
      \n
    • DO NOTHING\uff1a\u5f53\u8bb0\u5f55\u5b58\u5728\u65f6\uff0c\u4ec0\u4e48\u90fd\u4e0d\u505a<\/li>\n\n\n\n
    • DO UPDATE SET column_1 = value_1, \u2026 WHERE condition\uff1a\u5f53\u8bb0\u5f55\u5b58\u5728\u65f6\uff0c\u66f4\u65b0\u8868\u4e2d\u7684\u4e00\u4e9b\u5b57\u6bb5<\/li>\n<\/ul>\n\n\n\n
      PostgreSQL \u7684 upsert \u793a\u4f8b
      \u6211\u4eec\u65b0\u5efa\u4e00\u4e2a customers \u8868\u6765\u8fdb\u884c\u6f14\u793a\uff1a<\/p>\n\n\n\n
      CREATE<\/strong> TABLE<\/strong> customers (\n customer_id serial PRIMARY KEY<\/strong>,\n name VARCHAR UNIQUE<\/strong>,\n email VARCHAR NOT<\/strong> NULL<\/strong>,\n active bool NOT<\/strong> NULL<\/strong> DEFAULT<\/strong> TRUE<\/strong>\n);\n<\/code><\/pre>\n\n\n\n
      customers \u8868\u67094\u4e2a\u5b57\u6bb5\uff1acustomer_id\u3001name\u3001email \u548c active\u3002\u5176\u4e2d\uff0cname \u5b57\u6bb5\u6709\u552f\u4e00\u7ea6\u675f\uff0c\u7528\u4e8e\u786e\u4fdd\u5ba2\u6237\u7684\u552f\u4e00\u6027\u3002<\/p>\n\n\n\n
      upsert<\/p>\n\n\n\n
      \u4e0b\u9762\uff0c\u5f80 customers \u8868\u91cc\u63d2\u5165\u51e0\u884c\uff1a<\/p>\n\n\n\n
      INSERT<\/strong> INTO customers (NAME, email)\nVALUES\n ('IBM', 'contact@ibm.com'),\n (\n 'Microsoft',\n 'contact@microsoft.com'\n ),\n (\n 'Intel',\n 'contact@intel.com'\n );\n<\/code><\/pre>\n\n\n\n
      #SELECT * FROM customers;\ncustomer_id |   name    |         email         | active\n-------------+-----------+-----------------------+--------\n          1 | IBM       | contact@ibm.com       | t\n          2 | Microsoft | contact@microsoft.com | t\n          3 | Intel     | contact@intel.com     | t\n(3 rows)\n<\/code><\/pre>\n\n\n\n
      \u5047\u8bbe Microsoft \u66f4\u6362\u4e86\u8054\u7cfb\u65b9\u5f0f email\uff1a\u7531 contact@microsoft.com \u53d8\u6210\u4e86 hotline@microsoft.com\uff0c\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528 UPDATE \u8bed\u53e5\u8fdb\u884c\u4fee\u6539\u3002\u7136\u800c\uff0c\u4e3a\u4e86\u6f14\u793a upsert \u529f\u80fd\uff0c\u6211\u4eec\u4f7f\u7528 INSERT ON CONFLICT \u8bed\u53e5\uff1a<\/p>\n\n\n\n
      INSERT<\/strong> INTO<\/strong> customers (NAME, email)\nVALUES<\/strong>\n (\n 'Microsoft',\n 'hotline@microsoft.com'\n ) \nON<\/strong> CONFLICT<\/strong> ON<\/strong> CONSTRAINT<\/strong> customers_name_key \nDO<\/strong> NOTHING<\/strong>;\n<\/code><\/pre>\n\n\n\n
      \u8fd9\u4e2a\u8bed\u53e5\u6307\u660e\u4e86\uff0c\u5f53\u6570\u636e\u5b58\u5728\u65f6\uff0c\u4ec0\u4e48\u90fd\u4e0d\u505a(DO NOTING)\u3002\u4e0b\u9762\u7684\u8bed\u53e5\u6709\u4e00\u6837\u7684\u6548\u679c\uff0c\u533a\u522b\u5728\u4e8e\u4f7f\u7528\u7684\u662f name \u5b57\u6bb5\uff0c\u800c\u4e0d\u662f\u7ea6\u675f\u7684\u540d\u5b57\uff1a<\/p>\n\n\n\n
      INSERT<\/strong> INTO<\/strong> customers (name, email)\nVALUES<\/strong>\n (\n 'Microsoft',\n 'hotline@microsoft.com'\n ) \nON<\/strong> CONFLICT<\/strong> (name) \nDO<\/strong> NOTHING<\/strong>;\n<\/code><\/pre>\n\n\n\n
      \u6211\u4eec\u7684\u76ee\u6807\u662f\u4fee\u6539\u5ba2\u6237\u7684 email\uff0c\u6240\u4ee5\u5e94\u8be5\u7528\u8fd9\u6761\u8bed\u53e5\uff1a<\/p>\n\n\n\n
      INSERT<\/strong> INTO<\/strong> customers (name, email)\nVALUES<\/strong>\n (\n 'Microsoft',\n 'hotline@microsoft.com'\n ) \nON<\/strong> CONFLICT<\/strong> (name) \nDO<\/strong>\n UPDATE<\/strong>\n   SET<\/strong> email = EXCLUDED.email;\nupsert<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
      PGsql \u4e5f\u6709\u7c7b\u4f3c\u4e8emysql ON DUPLICATE KEY UPDATE \u7684\u8bed\u6cd5 \u6ce8\u610f\uff0cON CONF […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[43],"_links":{"self":[{"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/posts\/1062"}],"collection":[{"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/comments?post=1062"}],"version-history":[{"count":2,"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/posts\/1062\/revisions"}],"predecessor-version":[{"id":1073,"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/posts\/1062\/revisions\/1073"}],"wp:attachment":[{"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/media?parent=1062"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/categories?post=1062"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.frost-s.com\/index.php\/wp-json\/wp\/v2\/tags?post=1062"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}