PostgreSQL 9.5引入了一项新功能，即UPSERT（insert on conflict do）。当插入遇到约束错误时，直接返回或者改为执行UPDATE。
UPSERT的语法如下所示。PostgreSQL 9.5以前的版本，可以通过函数或者with语法来实现与UPSERT类似的功能。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | <ol class="linenums"> <li class="L0"><span class="typ">Command</span><span class="pun">:</span><span class="pln"> INSERT </span></li> <li class="L1"><span class="typ">Description</span><span class="pun">:</span><span class="pln"> create </span><span class="kwd">new</span><span class="pln"> rows </span><span class="kwd">in</span><span class="pln"> a table </span></li> <li class="L2"><span class="typ">Syntax</span><span class="pun">:</span><span class="pln"> </span></li> <li class="L3"><span class="pun">[</span><span class="pln"> WITH </span><span class="pun">[</span><span class="pln"> RECURSIVE </span><span class="pun">]</span><span class="pln"> with_query </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">]</span><span class="pln"> </span></li> <li class="L4"><span class="pln">INSERT INTO table_name </span><span class="pun">[</span><span class="pln"> AS </span><span class="kwd">alias</span><span class="pln"> </span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> column_name </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="pun">]</span><span class="pln"> </span></li> <li class="L5"><span class="pln"> </span><span class="pun">{</span><span class="pln"> DEFAULT VALUES </span><span class="pun">|</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> expression </span><span class="pun">|</span><span class="pln"> DEFAULT </span><span class="pun">}</span><span class="pln"> </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> query </span><span class="pun">}</span><span class="pln"> </span></li> <li class="L6"><span class="pln"> </span><span class="pun">[</span><span class="pln"> ON CONFLICT </span><span class="pun">[</span><span class="pln"> conflict_target </span><span class="pun">]</span><span class="pln"> conflict_action </span><span class="pun">]</span><span class="pln"> </span></li> <li class="L7"><span class="pln"> </span><span class="pun">[</span><span class="pln"> RETURNING </span><span class="pun">*</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> output_expression </span><span class="pun">[</span><span class="pln"> </span><span class="pun">[</span><span class="pln"> AS </span><span class="pun">]</span><span class="pln"> output_name </span><span class="pun">]</span><span class="pln"> </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">]</span><span class="pln"> </span></li> <li class="L8"></li> <li class="L9"><span class="kwd">where</span><span class="pln"> conflict_target can be one of</span><span class="pun">:</span><span class="pln"> </span></li> <li class="L0"></li> <li class="L1"><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> index_column_name </span><span class="pun">|</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> index_expression </span><span class="pun">)</span><span class="pln"> </span><span class="pun">}</span><span class="pln"> </span><span class="pun">[</span><span class="pln"> COLLATE collation </span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln"> opclass </span><span class="pun">]</span><span class="pln"> </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="pun">[</span><span class="pln"> WHERE index_predicate </span><span class="pun">]</span><span class="pln"> </span></li> <li class="L2"><span class="pln"> ON CONSTRAINT constraint_name </span></li> <li class="L3"></li> <li class="L4"><span class="kwd">and</span><span class="pln"> conflict_action </span><span class="kwd">is</span><span class="pln"> one of</span><span class="pun">:</span><span class="pln"> </span></li> <li class="L5"></li> <li class="L6"><span class="pln"> DO NOTHING </span></li> <li class="L7"><span class="pln"> DO UPDATE SET </span><span class="pun">{</span><span class="pln"> column_name </span><span class="pun">=</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> expression </span><span class="pun">|</span><span class="pln"> DEFAULT </span><span class="pun">}</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> </span></li> <li class="L8"><span class="pln"> </span><span class="pun">(</span><span class="pln"> column_name </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="pun">{</span><span class="pln"> expression </span><span class="pun">|</span><span class="pln"> DEFAULT </span><span class="pun">}</span><span class="pln"> </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> </span></li> <li class="L9"><span class="pln"> </span><span class="pun">(</span><span class="pln"> column_name </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="kwd">sub</span><span class="pun">-</span><span class="pln">SELECT </span><span class="pun">)</span><span class="pln"> </span></li> <li class="L0"><span class="pln"> </span><span class="pun">}</span><span class="pln"> </span><span class="pun">[,</span><span class="pln"> </span><span class="pun">...]</span><span class="pln"> </span></li> <li class="L1"><span class="pln"> </span><span class="pun">[</span><span class="pln"> WHERE condition </span><span class="pun">]</span></li></ol> |
执行如下命令，创建一张测试表，其中一个字段为唯一键或者主键。
1 2 3 4 5 | <ol class="linenums"> <li class="L0"><span class="pln">create table test</span><span class="pun">(</span><span class="pln">id </span><span class="kwd">int</span><span class="pln"> primary key</span><span class="pun">,</span><span class="pln"> info text</span><span class="pun">,</span><span class="pln"> crt_time timestamp</span><span class="pun">);</span></li></ol> |
执行如下任一命令，选择插入数据时，若数据存在是进行更新还是直接返回。
不存在则插入，存在则更新，其命令如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <ol class="linenums"> <li class="L0"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'test'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">())</span><span class="pln"> on conflict </span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">do</span><span class="pln"> update </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="pln">excluded</span><span class="pun">.</span><span class="pln">info</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">excluded</span><span class="pun">.</span><span class="pln">crt_time</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L1"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span></li> <li class="L2"></li> <li class="L3"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L4"><span class="pln">id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L5"><span class="pun">----+------+----------------------------</span><span class="pln"> </span></li> <li class="L6"><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> test </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">27</span><span class="pun">:</span><span class="lit">25.393948</span><span class="pln"> </span></li> <li class="L7"><span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span><span class="pln"> </span></li> <li class="L8"></li> <li class="L9"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'hello digoal'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">())</span><span class="pln"> on conflict </span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">do</span><span class="pln"> update </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="pln">excluded</span><span class="pun">.</span><span class="pln">info</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">excluded</span><span class="pun">.</span><span class="pln">crt_time</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L0"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span></li> <li class="L1"></li> <li class="L2"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L3"><span class="pln">id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L4"><span class="pun">----+--------------+----------------------------</span><span class="pln"> </span></li> <li class="L5"><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> hello digoal </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">27</span><span class="pun">:</span><span class="lit">39.140877</span><span class="pln"> </span></li> <li class="L6"><span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span></li></ol> |
不存在则插入，存在则直接返回，即不做任何处理，其命令如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | <ol class="linenums"> <li class="L0"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'hello digoal'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">())</span><span class="pln"> on conflict </span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">do</span><span class="pln"> nothing</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L1"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span></li> <li class="L2"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'pu'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">())</span><span class="pln"> on conflict </span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">do</span><span class="pln"> nothing</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L3"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span></li> <li class="L4"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'pu'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">())</span><span class="pln"> on conflict </span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">do</span><span class="pln"> nothing</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L5"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span></li> <li class="L6"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L7"><span class="pln">id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L8"><span class="pun">----+--------------+----------------------------</span><span class="pln"> </span></li> <li class="L9"><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> hello digoal </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">27</span><span class="pun">:</span><span class="lit">39.140877</span><span class="pln"> </span></li> <li class="L0"><span class="lit">2</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> pu </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">28</span><span class="pun">:</span><span class="lit">20.37392</span><span class="pln"> </span></li> <li class="L1"><span class="pun">(</span><span class="lit">2</span><span class="pln"> rows</span><span class="pun">)</span></li></ol> |
您可以根据需求，通过如下三种方法实现类似UPSERT的功能：
通过函数实现。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | <ol class="linenums"> <li class="L0"><span class="pln"> test03</span><span class="pun">=#</span><span class="pln"> create </span><span class="kwd">or</span><span class="pln"> replace </span><span class="kwd">function</span><span class="pln"> f_upsert</span><span class="pun">(</span><span class="kwd">int</span><span class="pun">,</span><span class="pln">text</span><span class="pun">,</span><span class="pln">timestamp</span><span class="pun">)</span><span class="pln"> returns </span><span class="kwd">void</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> $$ </span></li> <li class="L1"><span class="pln"> declare </span></li> <li class="L2"><span class="pln"> res </span><span class="kwd">int</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L3"><span class="pln"> </span><span class="kwd">begin</span><span class="pln"> </span></li> <li class="L4"><span class="pln"> update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="pln">$2</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">$3 </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="pln">$1</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L5"><span class="pln"> </span><span class="kwd">if</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> found </span><span class="kwd">then</span><span class="pln"> </span></li> <li class="L6"><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="pun">(</span><span class="pln">id</span><span class="pun">,</span><span class="pln">info</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">)</span><span class="pln"> values </span><span class="pun">(</span><span class="pln">$1</span><span class="pun">,</span><span class="pln">$2</span><span class="pun">,</span><span class="pln">$3</span><span class="pun">);</span><span class="pln"> </span></li> <li class="L7"><span class="pln"> </span><span class="kwd">end</span><span class="pln"> </span><span class="kwd">if</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L8"><span class="pln"> exception </span><span class="kwd">when</span><span class="pln"> others </span><span class="kwd">then</span><span class="pln"> </span></li> <li class="L9"><span class="pln"> </span><span class="kwd">return</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L0"><span class="pln"> </span><span class="kwd">end</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L1"><span class="pln"> $$ language plpgsql strict</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L2"><span class="pln"> CREATE FUNCTION </span></li> <li class="L3"></li> <li class="L4"><span class="pln"> test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> f_upsert</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'digoal'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">()::</span><span class="pln">timestamp</span><span class="pun">);</span><span class="pln"> </span></li> <li class="L5"><span class="pln"> f_upsert </span></li> <li class="L6"><span class="pln"> </span><span class="pun">----------</span><span class="pln"> </span></li> <li class="L7"></li> <li class="L8"><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span><span class="pln"> </span></li> <li class="L9"></li> <li class="L0"><span class="pln"> test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L1"><span class="pln"> id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L2"><span class="pln"> </span><span class="pun">----+--------+----------------------------</span><span class="pln"> </span></li> <li class="L3"><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> pu </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">28</span><span class="pun">:</span><span class="lit">20.37392</span><span class="pln"> </span></li> <li class="L4"><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> digoal </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">29.254325</span><span class="pln"> </span></li> <li class="L5"><span class="pln"> </span><span class="pun">(</span><span class="lit">2</span><span class="pln"> rows</span><span class="pun">)</span><span class="pln"> </span></li> <li class="L6"></li> <li class="L7"><span class="pln"> test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> f_upsert</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'digoal001'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">()::</span><span class="pln">timestamp</span><span class="pun">);</span><span class="pln"> </span></li> <li class="L8"><span class="pln"> f_upsert </span></li> <li class="L9"><span class="pln"> </span><span class="pun">----------</span><span class="pln"> </span></li> <li class="L0"></li> <li class="L1"><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span><span class="pln"> </span></li> <li class="L2"></li> <li class="L3"><span class="pln"> test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L4"><span class="pln"> id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L5"><span class="pln"> </span><span class="pun">----+-----------+---------------------------</span><span class="pln"> </span></li> <li class="L6"><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> pu </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">28</span><span class="pun">:</span><span class="lit">20.37392</span><span class="pln"> </span></li> <li class="L7"><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> digoal001 </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">38.0529</span><span class="pln"> </span></li> <li class="L8"><span class="pln"> </span><span class="pun">(</span><span class="lit">2</span><span class="pln"> rows</span><span class="pun">)</span><span class="pln"> </span></li> <li class="L9"></li> <li class="L0"><span class="pln"> test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> f_upsert</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'hello'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">()::</span><span class="pln">timestamp</span><span class="pun">);</span><span class="pln"> </span></li> <li class="L1"><span class="pln"> f_upsert </span></li> <li class="L2"><span class="pln"> </span><span class="pun">----------</span><span class="pln"> </span></li> <li class="L3"></li> <li class="L4"><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span><span class="pln"> </span></li> <li class="L5"></li> <li class="L6"><span class="pln"> test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L7"><span class="pln"> id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L8"><span class="pln"> </span><span class="pun">----+-----------+---------------------------</span><span class="pln"> </span></li> <li class="L9"><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> pu </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">28</span><span class="pun">:</span><span class="lit">20.37392</span><span class="pln"> </span></li> <li class="L0"><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> digoal001 </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">38.0529</span><span class="pln"> </span></li> <li class="L1"><span class="pln"> </span><span class="lit">3</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> hello </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">49.14291</span><span class="pln"> </span></li> <li class="L2"><span class="pln"> </span><span class="pun">(</span><span class="lit">3</span><span class="pln"> rows</span><span class="pun">)</span></li></ol> |
通过WITH语法，用法1，操作步骤如下：
执行如下命令，创建一张测试表，其中一个字段为唯一键或者主键。
1 2 3 4 5 | <ol class="linenums"> <li class="L0"><span class="pln">create table test</span><span class="pun">(</span><span class="pln">id </span><span class="kwd">int</span><span class="pln"> primary key</span><span class="pun">,</span><span class="pln"> info text</span><span class="pun">,</span><span class="pln"> crt_time timestamp</span><span class="pun">);</span></li></ol> |
执行如下命令，若数据存在则更新，不存在则插入。
1 2 3 4 5 | <ol class="linenums"> <li class="L0"><span class="kwd">with</span><span class="pln"> upsert </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">(</span><span class="pln">update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="pln">$info</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">$crt_time </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="pln">$id returning </span><span class="pun">*)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="kwd">select</span><span class="pln"> $id</span><span class="pun">,</span><span class="pln">$info</span><span class="pun">,</span><span class="pln">$crt_time </span><span class="kwd">where</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> exists </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> upsert </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="pln">$id</span><span class="pun">);</span></li></ol> |
执行如下命令，替换变量，进行测试。同时插入一条不存在的值，只有一个会话成功，另一个会话会报主键约束错误。
1 2 3 4 5 | <ol class="linenums"> <li class="L0"><span class="kwd">with</span><span class="pln"> upsert </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">(</span><span class="pln">update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="str">'test'</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">1</span><span class="pln"> returning </span><span class="pun">*)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="str">'test'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> exists </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> upsert </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">1</span><span class="pun">);</span></li></ol> |
通过WITH语法，用法2，操作步骤如下：
执行如下命令，创建一张数据表，即使表没有主键或者唯一约束，也能保证并发。
1 2 3 4 5 | <ol class="linenums"> <li class="L0"><span class="pln">create table test</span><span class="pun">(</span><span class="pln">id </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> info text</span><span class="pun">,</span><span class="pln"> crt_time timestamp</span><span class="pun">);</span></li></ol> |
进行如下任一操作步骤，选择记录不存在时，对于同一条数据更新的处理结果。
对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会lock记录，后来的session会wait。操作步骤如下所示：
执行如下命令，确定对数据更新的处理结果。
1 2 3 4 5 6 7 8 9 10 11 12 13 | <ol class="linenums"> <li class="L0"><span class="kwd">with</span><span class="pln"> </span></li> <li class="L1"><span class="pln">w1 </span><span class="kwd">as</span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">(</span><span class="str">'x'</span><span class="pun">||</span><span class="pln">substr</span><span class="pun">(</span><span class="pln">md5</span><span class="pun">(</span><span class="str">'$id'</span><span class="pun">),</span><span class="lit">1</span><span class="pun">,</span><span class="lit">16</span><span class="pun">))::</span><span class="pln">bit</span><span class="pun">(</span><span class="lit">64</span><span class="pun">)::</span><span class="pln">bigint </span><span class="kwd">as</span><span class="pln"> tra_id</span><span class="pun">),</span><span class="pln"> </span></li> <li class="L2"><span class="pln">upsert </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">(</span><span class="pln">update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="pln">$info</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">$crt_time </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="pln">$id returning </span><span class="pun">*)</span><span class="pln"> </span></li> <li class="L3"><span class="pln">insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="kwd">select</span><span class="pln"> $id</span><span class="pun">,</span><span class="pln"> $info</span><span class="pun">,</span><span class="pln"> $crt_time </span><span class="kwd">from</span><span class="pln"> w1 </span></li> <li class="L4"><span class="kwd">where</span><span class="pln"> pg_try_advisory_xact_lock</span><span class="pun">(</span><span class="pln">tra_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> exists </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> upsert </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="pln">$id</span><span class="pun">);</span></li></ol> |
替换变量，进行测试。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | <ol class="linenums"> <li class="L0"><span class="kwd">with</span><span class="pln"> </span></li> <li class="L1"><span class="pln">w1 </span><span class="kwd">as</span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">(</span><span class="str">'x'</span><span class="pun">||</span><span class="pln">substr</span><span class="pun">(</span><span class="pln">md5</span><span class="pun">(</span><span class="str">'1'</span><span class="pun">),</span><span class="lit">1</span><span class="pun">,</span><span class="lit">16</span><span class="pun">))::</span><span class="pln">bit</span><span class="pun">(</span><span class="lit">64</span><span class="pun">)::</span><span class="pln">bigint </span><span class="kwd">as</span><span class="pln"> tra_id</span><span class="pun">),</span><span class="pln"> </span></li> <li class="L2"><span class="pln">upsert </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">(</span><span class="pln">update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="str">'digoal0123'</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">1</span><span class="pln"> returning </span><span class="pun">*)</span><span class="pln"> </span></li> <li class="L3"><span class="pln">insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'digoal0123'</span><span class="pun">,</span><span class="pln"> now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> w1 </span></li> <li class="L4"><span class="pln"> </span><span class="kwd">where</span><span class="pln"> pg_try_advisory_xact_lock</span><span class="pun">(</span><span class="pln">tra_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> exists </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> upsert </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">1</span><span class="pun">);</span><span class="pln"> </span></li> <li class="L5"></li> <li class="L6"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span></li> <li class="L7"></li> <li class="L8"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L9"><span class="pln">id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L0"><span class="pun">----+------------+---------------------------</span><span class="pln"> </span></li> <li class="L1"><span class="lit">2</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> pu </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">28</span><span class="pun">:</span><span class="lit">20.37392</span><span class="pln"> </span></li> <li class="L2"><span class="lit">3</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> hello </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">49.14291</span><span class="pln"> </span></li> <li class="L3"><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> digoal0123 </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">38.0529</span><span class="pln"> </span></li> <li class="L4"><span class="pun">(</span><span class="lit">3</span><span class="pln"> rows</span><span class="pun">)</span><span class="pln"> </span></li> <li class="L5"></li> <li class="L6"><span class="kwd">with</span><span class="pln"> </span></li> <li class="L7"><span class="pln">w1 </span><span class="kwd">as</span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">(</span><span class="str">'x'</span><span class="pun">||</span><span class="pln">substr</span><span class="pun">(</span><span class="pln">md5</span><span class="pun">(</span><span class="str">'4'</span><span class="pun">),</span><span class="lit">1</span><span class="pun">,</span><span class="lit">16</span><span class="pun">))::</span><span class="pln">bit</span><span class="pun">(</span><span class="lit">64</span><span class="pun">)::</span><span class="pln">bigint </span><span class="kwd">as</span><span class="pln"> tra_id</span><span class="pun">),</span><span class="pln"> </span></li> <li class="L8"><span class="pln">upsert </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">(</span><span class="pln">update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="str">'digoal0123'</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">4</span><span class="pln"> returning </span><span class="pun">*)</span><span class="pln"> </span></li> <li class="L9"><span class="pln">insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="kwd">select</span><span class="pln"> </span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'digoal0123'</span><span class="pun">,</span><span class="pln"> now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> w1 </span></li> <li class="L0"><span class="pln"> </span><span class="kwd">where</span><span class="pln"> pg_try_advisory_xact_lock</span><span class="pun">(</span><span class="pln">tra_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> exists </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> upsert </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">4</span><span class="pun">);</span><span class="pln"> </span></li> <li class="L1"></li> <li class="L2"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span></li> <li class="L3"></li> <li class="L4"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L5"><span class="pln">id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L6"><span class="pun">----+------------+----------------------------</span><span class="pln"> </span></li> <li class="L7"><span class="lit">2</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> pu </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">28</span><span class="pun">:</span><span class="lit">20.37392</span><span class="pln"> </span></li> <li class="L8"><span class="lit">3</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> hello </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">49.14291</span><span class="pln"> </span></li> <li class="L9"><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> digoal0123 </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">38.0529</span><span class="pln"> </span></li> <li class="L0"><span class="lit">4</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> digoal0123 </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">38</span><span class="pun">:</span><span class="lit">39.801908</span><span class="pln"> </span></li> <li class="L1"><span class="pun">(</span><span class="lit">4</span><span class="pln"> rows</span><span class="pun">)</span></li></ol> |
对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会更新数据，后来的session不等待，直接失败。操作步骤如下所示：
执行如下命令，确定对数据更新的处理结果。
1 2 3 4 5 6 7 8 9 10 11 | <ol class="linenums"> <li class="L0"><span class="kwd">with</span><span class="pln"> w1 </span><span class="kwd">as</span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">(</span><span class="str">'x'</span><span class="pun">||</span><span class="pln">substr</span><span class="pun">(</span><span class="pln">md5</span><span class="pun">(</span><span class="str">'$id'</span><span class="pun">),</span><span class="lit">1</span><span class="pun">,</span><span class="lit">16</span><span class="pun">))::</span><span class="pln">bit</span><span class="pun">(</span><span class="lit">64</span><span class="pun">)::</span><span class="pln">bigint </span><span class="kwd">as</span><span class="pln"> tra_id</span><span class="pun">),</span><span class="pln"> </span></li> <li class="L1"><span class="pln">upsert </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">(</span><span class="pln">update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="pln">$info</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">$crt_time </span><span class="kwd">from</span><span class="pln"> w1 </span><span class="kwd">where</span><span class="pln"> pg_try_advisory_xact_lock</span><span class="pun">(</span><span class="pln">tra_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> id</span><span class="pun">=</span><span class="pln">$id returning </span><span class="pun">*)</span><span class="pln"> </span></li> <li class="L2"><span class="pln">insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="kwd">select</span><span class="pln"> $id</span><span class="pun">,</span><span class="pln">$info</span><span class="pun">,</span><span class="pln">$crt_time </span><span class="kwd">from</span><span class="pln"> w1 </span></li> <li class="L3"><span class="kwd">where</span><span class="pln"> pg_try_advisory_xact_lock</span><span class="pun">(</span><span class="pln">tra_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> exists </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> upsert </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="pln">$id</span><span class="pun">);</span></li></ol> |
替换变量，进行测试。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | <ol class="linenums"> <li class="L0"><span class="kwd">with</span><span class="pln"> w1 </span><span class="kwd">as</span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">(</span><span class="str">'x'</span><span class="pun">||</span><span class="pln">substr</span><span class="pun">(</span><span class="pln">md5</span><span class="pun">(</span><span class="str">'1'</span><span class="pun">),</span><span class="lit">1</span><span class="pun">,</span><span class="lit">16</span><span class="pun">))::</span><span class="pln">bit</span><span class="pun">(</span><span class="lit">64</span><span class="pun">)::</span><span class="pln">bigint </span><span class="kwd">as</span><span class="pln"> tra_id</span><span class="pun">),</span><span class="pln"> </span></li> <li class="L1"><span class="pln">upsert </span><span class="kwd">as</span><span class="pln"> </span><span class="pun">(</span><span class="pln">update test </span><span class="kwd">set</span><span class="pln"> info</span><span class="pun">=</span><span class="str">'test'</span><span class="pun">,</span><span class="pln">crt_time</span><span class="pun">=</span><span class="pln">now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> w1 </span><span class="kwd">where</span><span class="pln"> pg_try_advisory_xact_lock</span><span class="pun">(</span><span class="pln">tra_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">1</span><span class="pln"> returning </span><span class="pun">*)</span><span class="pln"> </span></li> <li class="L2"><span class="pln">insert </span><span class="kwd">into</span><span class="pln"> test </span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="str">'test'</span><span class="pun">,</span><span class="pln">now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> w1 </span></li> <li class="L3"><span class="pln"> </span><span class="kwd">where</span><span class="pln"> pg_try_advisory_xact_lock</span><span class="pun">(</span><span class="pln">tra_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> exists </span><span class="pun">(</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> upsert </span><span class="kwd">where</span><span class="pln"> id</span><span class="pun">=</span><span class="lit">1</span><span class="pun">);</span><span class="pln"> </span></li> <li class="L4"></li> <li class="L5"><span class="pln">INSERT </span><span class="lit">0</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span></li> <li class="L6"></li> <li class="L7"><span class="pln">test03</span><span class="pun">=#</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> test</span><span class="pun">;</span><span class="pln"> </span></li> <li class="L8"><span class="pln">id </span><span class="pun">|</span><span class="pln"> info </span><span class="pun">|</span><span class="pln"> crt_time </span></li> <li class="L9"><span class="pun">----+------------+----------------------------</span><span class="pln"> </span></li> <li class="L0"><span class="lit">2</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> pu </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">28</span><span class="pun">:</span><span class="lit">20.37392</span><span class="pln"> </span></li> <li class="L1"><span class="lit">3</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> hello </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">31</span><span class="pun">:</span><span class="lit">49.14291</span><span class="pln"> </span></li> <li class="L2"><span class="lit">4</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> digoal0123 </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">42</span><span class="pun">:</span><span class="lit">50.912887</span><span class="pln"> </span></li> <li class="L3"><span class="lit">1</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> test </span><span class="pun">|</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">04</span><span class="pun">-</span><span class="lit">24</span><span class="pln"> </span><span class="lit">15</span><span class="pun">:</span><span class="lit">44</span><span class="pun">:</span><span class="lit">44.245167</span><span class="pln"> </span></li> <li class="L4"><span class="pun">(</span><span class="lit">4</span><span class="pln"> rows</span><span class="pun">)</span></li></ol> |