{"id":13297,"date":"2024-03-23T16:23:45","date_gmt":"2024-03-23T15:23:45","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=13297"},"modified":"2024-06-17T11:04:27","modified_gmt":"2024-06-17T09:04:27","slug":"islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/","title":{"rendered":"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p>In the <a href=\"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/\">last episode<\/a>, we covered DML statements in SQL*Plus\/SQLcl scripts for the Oracle Database 23c. The <a href=\"https:\/\/github.com\/IslandSQL\/IslandSQL\">IslandSQL<\/a> grammar can now also handle PostgreSQL 16 DML statements in psql scripts.<\/p>\n\n\n\n<p>In this blog post, we will look at some features in PostgreSQL 16 which I miss in the Oracle Database 23c. <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"#returning_clause\">Returning Clause<\/a><\/li>\n\n\n\n<li><a href=\"#insert_update_and_delete_in_the_with_clause\">Insert, Update and Delete in the With Clause<\/a><\/li>\n\n\n\n<li><a href=\"#deleting_rows_with_merge\">Deleting Rows With Merge<\/a><\/li>\n\n\n\n<li><a href=\"#select_without_select_list\">Select Without Select List<\/a><\/li>\n<\/ul>\n\n\n\n<p>Since we now have the Table Value Constructor, Booleans and IF [NOT] EXISTS syntax support I truly hope that some features, if not all, will make it into a future release of the Oracle Database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"returning_clause\">Returning Clause<\/h2>\n\n\n\n<p>Let&#8217;s create a table <code>t1<\/code> with some test data. The script works in PostgreSQL 16 and Oracle Database 23c.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">1) Setup (PostgreSQL &amp; OracleDB)<\/span><span role=\"button\" tabindex=\"0\" data-code=\"create table t1 as\nselect * \n  from (values\n          (7839, 'KING',   'PRESIDENT', null, date '1981-11-17', 5000, null, 10),\n          (7566, 'JONES',  'MANAGER',   7839, date '1981-04-02', 2975, null, 20),\n          (7698, 'BLAKE',  'MANAGER',   7839, date '1981-05-01', 2850, null, 30),\n          (7782, 'CLARK',  'MANAGER',   7839, date '1981-06-09', 2450, null, 10),\n          (7788, 'SCOTT',  'ANALYST',   7566, date '1987-04-19', 3000, null, 20),\n          (7902, 'FORD',   'ANALYST',   7566, date '1981-12-03', 3000, null, 20),\n          (7499, 'ALLEN',  'SALESMAN',  7698, date '1981-02-20', 1600,  300, 30),\n          (7521, 'WARD',   'SALESMAN',  7698, date '1981-02-22', 1250,  500, 30),\n          (7654, 'MARTIN', 'SALESMAN',  7698, date '1981-09-28', 1250, 1400, 30),\n          (7844, 'TURNER', 'SALESMAN',  7698, date '1981-09-08', 1500,    0, 30),\n          (7900, 'JAMES',  'CLERK',     7698, date '1981-12-03',  950, null, 30),\n          (7934, 'MILLER', 'CLERK',     7782, date '1982-01-23', 1300, null, 10),\n          (7369, 'SMITH',  'CLERK',     7902, date '1980-12-17',  800, null, 20),\n          (7876, 'ADAMS',  'CLERK',     7788, date '1987-05-23', 1100, null, 20)                        \n       ) s (empno, ename, job, mgr, hiredate, sal, comm, deptno);\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">create<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><span style=\"color: #569CD6\">as<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> * <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">values<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7839<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;KING&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #CE9178\">&#39;PRESIDENT&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-11-17&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">5000<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7566<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;JONES&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;MANAGER&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #B5CEA8\">7839<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-04-02&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">2975<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7698<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;BLAKE&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;MANAGER&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #B5CEA8\">7839<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-05-01&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">2850<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7782<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;CLARK&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;MANAGER&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #B5CEA8\">7839<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-06-09&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">2450<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7788<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;SCOTT&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;ANALYST&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #B5CEA8\">7566<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1987-04-19&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">3000<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7902<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;FORD&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #CE9178\">&#39;ANALYST&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #B5CEA8\">7566<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-12-03&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">3000<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7499<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;ALLEN&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;SALESMAN&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">7698<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-02-20&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">1600<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">300<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7521<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;WARD&#39;<\/span><span style=\"color: #D4D4D4\">,   <\/span><span style=\"color: #CE9178\">&#39;SALESMAN&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">7698<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-02-22&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">1250<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">500<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7654<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;MARTIN&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;SALESMAN&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">7698<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-09-28&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">1250<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">1400<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7844<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;TURNER&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;SALESMAN&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">7698<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-09-08&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">1500<\/span><span style=\"color: #D4D4D4\">,    <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7900<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;JAMES&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;CLERK&#39;<\/span><span style=\"color: #D4D4D4\">,     <\/span><span style=\"color: #B5CEA8\">7698<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1981-12-03&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">950<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7934<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;MILLER&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;CLERK&#39;<\/span><span style=\"color: #D4D4D4\">,     <\/span><span style=\"color: #B5CEA8\">7782<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1982-01-23&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">1300<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7369<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;SMITH&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;CLERK&#39;<\/span><span style=\"color: #D4D4D4\">,     <\/span><span style=\"color: #B5CEA8\">7902<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1980-12-17&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #B5CEA8\">800<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          (<\/span><span style=\"color: #B5CEA8\">7876<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;ADAMS&#39;<\/span><span style=\"color: #D4D4D4\">,  <\/span><span style=\"color: #CE9178\">&#39;CLERK&#39;<\/span><span style=\"color: #D4D4D4\">,     <\/span><span style=\"color: #B5CEA8\">7788<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">date<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1987-05-23&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">1100<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">)                        <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       ) s (empno, ename, job, mgr, hiredate, sal, comm, deptno);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Now we want to increase the salary by 20 per cent for all employees that earn less than 2000 and we want to get the changed rows with the new values as a result. <\/p>\n\n\n\n<p>In PostgreSQL we can do the following:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">2a) PostgreSQL: update with returning clause<\/span><span role=\"button\" tabindex=\"0\" data-code=\"begin;\nupdate t1 \n   set sal = sal * 1.2\n where sal + coalesce(comm, 0) &lt; 2000\nreturning empno, ename, cast(sal \/ 1.2 as int) as old_sal, sal as new_sal;\nrollback;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">begin<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">update<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> sal = sal * <\/span><span style=\"color: #B5CEA8\">1.2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> sal + <\/span><span style=\"color: #DCDCAA\">coalesce<\/span><span style=\"color: #D4D4D4\">(comm, <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">) &lt; <\/span><span style=\"color: #B5CEA8\">2000<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #569CD6\">returning<\/span><span style=\"color: #D4D4D4\"> empno, ename, <\/span><span style=\"color: #DCDCAA\">cast<\/span><span style=\"color: #D4D4D4\">(sal \/ <\/span><span style=\"color: #B5CEA8\">1.2<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> int) <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> old_sal, sal <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> new_sal;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #DCDCAA\">rollback<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"BEGIN\n empno | ename  | old_sal | new_sal \n-------+--------+---------+---------\n  7499 | ALLEN  |    1600 |    1920\n  7521 | WARD   |    1250 |    1500\n  7844 | TURNER |    1500 |    1800\n  7900 | JAMES  |     950 |    1140\n  7934 | MILLER |    1300 |    1560\n  7369 | SMITH  |     800 |     960\n  7876 | ADAMS  |    1100 |    1320\n(7 rows)\n\nUPDATE 7\nROLLBACK\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> empno | ename  | old_sal | new_sal <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">-------+--------+---------+---------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7499 | ALLEN  |    1600 |    1920<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7521 | WARD   |    1250 |    1500<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7844 | TURNER |    1500 |    1800<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7900 | JAMES  |     950 |    1140<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7934 | MILLER |    1300 |    1560<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7369 | SMITH  |     800 |     960<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7876 | ADAMS  |    1100 |    1320<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">(7 rows)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">UPDATE 7<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">ROLLBACK<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>The returning clause allows us to define a list of expressions to be returned for each changed row. So, an <code>update<\/code>, <code>insert<\/code> and <code>delete<\/code> statement in PostgreSQL can return a result similar to a <code>select<\/code> statement.<\/p>\n\n\n\n<p>Doing the same in the Oracle Database 23c requires some PL\/SQL code. For example something like this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">2b) OracleDB: update with returning clause<\/span><span role=\"button\" tabindex=\"0\" data-code=\"set serveroutput on size unlimited\ndeclare\n   cursor c1 is select empno, ename, sal as old_sal, sal as new_sal from t1;\n   type t_row_type is table of c1%rowtype;\n   t_row t_row_type;\nbegin\n   update t1\n      set sal = sal * 1.2\n    where sal + coalesce(comm, 0) &lt; 2000\n   return empno, ename, old sal, new sal\n     bulk collect into t_row;\n   dbms_output.put_line(sql%rowcount || ' rows updated.');\n   dbms_output.put_line(null);\n   dbms_output.put_line('EMPNO ENAME  OLD_SAL NEW_SAL');\n   dbms_output.put_line('----- ------ ------- -------');\n   for i in t_row.first..t_row.last\n   loop\n      dbms_output.put_line(rpad(t_row(i).empno, 6)\n         || rpad(t_row(i).ename, 7)\n         || lpad(t_row(i).old_sal, 7)\n         || ' '\n         || lpad(t_row(i).new_sal, 7));\n   end loop;\n   rollback;\nend;\n\/\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> serveroutput <\/span><span style=\"color: #569CD6\">on<\/span><span style=\"color: #D4D4D4\"> size unlimited<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">declare<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">cursor<\/span><span style=\"color: #D4D4D4\"> c1 <\/span><span style=\"color: #569CD6\">is<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> empno, ename, sal <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> old_sal, sal <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> new_sal <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">type<\/span><span style=\"color: #D4D4D4\"> t_row_type <\/span><span style=\"color: #569CD6\">is<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">of<\/span><span style=\"color: #D4D4D4\"> c1%<\/span><span style=\"color: #569CD6\">rowtype<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   t_row t_row_type;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">begin<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">update<\/span><span style=\"color: #D4D4D4\"> t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> sal = sal * <\/span><span style=\"color: #B5CEA8\">1.2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> sal + <\/span><span style=\"color: #DCDCAA\">coalesce<\/span><span style=\"color: #D4D4D4\">(comm, <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">) &lt; <\/span><span style=\"color: #B5CEA8\">2000<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #C586C0\">return<\/span><span style=\"color: #D4D4D4\"> empno, ename, old sal, new sal<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #DCDCAA\">bulk collect<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">into<\/span><span style=\"color: #D4D4D4\"> t_row;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #DCDCAA\">dbms_output.<\/span><span style=\"color: #4EC9B0\">put_line<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #569CD6\">sql<\/span><span style=\"color: #DCDCAA\">%rowcount<\/span><span style=\"color: #D4D4D4\"> || <\/span><span style=\"color: #CE9178\">&#39; rows updated.&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #DCDCAA\">dbms_output.<\/span><span style=\"color: #4EC9B0\">put_line<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #DCDCAA\">dbms_output.<\/span><span style=\"color: #4EC9B0\">put_line<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #CE9178\">&#39;EMPNO ENAME  OLD_SAL NEW_SAL&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #DCDCAA\">dbms_output.<\/span><span style=\"color: #4EC9B0\">put_line<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #CE9178\">&#39;----- ------ ------- -------&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #C586C0\">for<\/span><span style=\"color: #D4D4D4\"> i <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> t_row<\/span><span style=\"color: #DCDCAA\">.first<\/span><span style=\"color: #D4D4D4\">..t_row<\/span><span style=\"color: #DCDCAA\">.last<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #C586C0\">loop<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #DCDCAA\">dbms_output.<\/span><span style=\"color: #4EC9B0\">put_line<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #DCDCAA\">rpad<\/span><span style=\"color: #D4D4D4\">(t_row(i).empno, <\/span><span style=\"color: #B5CEA8\">6<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         || <\/span><span style=\"color: #DCDCAA\">rpad<\/span><span style=\"color: #D4D4D4\">(t_row(i).ename, <\/span><span style=\"color: #B5CEA8\">7<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         || <\/span><span style=\"color: #DCDCAA\">lpad<\/span><span style=\"color: #D4D4D4\">(t_row(i).old_sal, <\/span><span style=\"color: #B5CEA8\">7<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         || <\/span><span style=\"color: #CE9178\">&#39; &#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         || <\/span><span style=\"color: #DCDCAA\">lpad<\/span><span style=\"color: #D4D4D4\">(t_row(i).new_sal, <\/span><span style=\"color: #B5CEA8\">7<\/span><span style=\"color: #D4D4D4\">));<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #C586C0\">end loop<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #DCDCAA\">rollback<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">end<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"7 rows updated.\n\nEMPNO ENAME  OLD_SAL NEW_SAL\n----- ------ ------- -------\n7499  ALLEN     1600    1920\n7521  WARD      1250    1500\n7844  TURNER    1500    1800\n7900  JAMES      950    1140\n7934  MILLER    1300    1560\n7369  SMITH      800     960\n7876  ADAMS     1100    1320\n\n\nPL\/SQL procedure successfully completed.\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">7 rows updated.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">EMPNO ENAME  OLD_SAL NEW_SAL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">----- ------ ------- -------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7499  ALLEN     1600    1920<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7521  WARD      1250    1500<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7844  TURNER    1500    1800<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7900  JAMES      950    1140<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7934  MILLER    1300    1560<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7369  SMITH      800     960<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7876  ADAMS     1100    1320<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">PL\/SQL procedure successfully completed.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>There is one thing I like about this solution. On line 10 we refer to the <code>old<\/code> value of the column <code>sal<\/code>. We do not need to reverse the logic used in the update to get the old value. <\/p>\n\n\n\n<p>Of course, there are other solutions. Like the next one:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">2c) OracleDB: update and select<\/span><span role=\"button\" tabindex=\"0\" data-code=\"set verify off\ncolumn start_scn new_value scn noprint\nlock table t1 in share row exclusive mode;\nselect dbms_flashback.get_system_change_number as start_scn;\nupdate t1\n   set sal = sal * 1.2\n where sal + coalesce(comm, 0) &lt; 2000;\nselect empno, ename, cast(sal \/ 1.2 as int) as old_sal, sal as new_sal\n  from t1\n where (empno, sal) not in (select empno, sal from t1 as of scn &amp;&amp;scn)\n order by rowid;\nrollback;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> verify off<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">column<\/span><span style=\"color: #D4D4D4\"> start_scn new_value scn noprint<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #569CD6\">lock<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> share <\/span><span style=\"color: #569CD6\">row<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">exclusive<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">mode<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">dbms_flashback.<\/span><span style=\"color: #4EC9B0\">get_system_change_number<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> start_scn;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">update<\/span><span style=\"color: #D4D4D4\"> t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> sal = sal * <\/span><span style=\"color: #B5CEA8\">1.2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> sal + <\/span><span style=\"color: #DCDCAA\">coalesce<\/span><span style=\"color: #D4D4D4\">(comm, <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">) &lt; <\/span><span style=\"color: #B5CEA8\">2000<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> empno, ename, <\/span><span style=\"color: #DCDCAA\">cast<\/span><span style=\"color: #D4D4D4\">(sal \/ <\/span><span style=\"color: #B5CEA8\">1.2<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> int) <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> old_sal, sal <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> new_sal<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> (empno, sal) <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> empno, sal <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">of<\/span><span style=\"color: #D4D4D4\"> scn &amp;&amp;scn)<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">order by<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">rowid<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #DCDCAA\">rollback<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"Lock succeeded.\n\n\n7 rows updated.\n\n\n     EMPNO ENAME     OLD_SAL    NEW_SAL\n---------- ------ ---------- ----------\n      7499 ALLEN        1600       1920\n      7521 WARD         1250       1500\n      7844 TURNER       1500       1800\n      7900 JAMES         950       1140\n      7934 MILLER       1300       1560\n      7369 SMITH         800        960\n      7876 ADAMS        1100       1320\n\n7 rows selected. \n\n\nRollback complete.\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">Lock succeeded.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7 rows updated.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">     EMPNO ENAME     OLD_SAL    NEW_SAL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">---------- ------ ---------- ----------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      7499 ALLEN        1600       1920<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      7521 WARD         1250       1500<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      7844 TURNER       1500       1800<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      7900 JAMES         950       1140<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      7934 MILLER       1300       1560<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      7369 SMITH         800        960<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      7876 ADAMS        1100       1320<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">7 rows selected. <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Rollback complete.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>This solution does not use the <code>returning clause<\/code>. Instead, after the <code>update<\/code> statement, a rather costly query produces the result. However, it&#8217;s important to note that the <code>lock table<\/code> statement on line 3 is necessary to ensure that another session cannot change the table <code>t1<\/code> after querying the current SCN and before the start of the <code>update<\/code> statement. In other words, this guarantees that we get only the rows changed by the <code>update<\/code> statement.<\/p>\n\n\n\n<p>The cool thing about this solution is that it allows us to sort the result (this was necessary to override the default order produced by the optimizer). Can we sort the result set of the <code>returning clause<\/code> in PostgreSQL?<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"insert_update_and_delete_in_the_with_clause\">Insert, Update and Delete in the With Clause<\/h2>\n\n\n\n<p>Yes, we can. In PostgreSQL we sort the result of an <code>update<\/code> statement like this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">2d) PostgreSQL: sort result of an update<\/span><span role=\"button\" tabindex=\"0\" data-code=\"begin;\nwith\n   upd as (\n      update t1 \n         set sal = sal * 1.2\n       where sal + coalesce(comm, 0) &lt; 2000\n      returning empno, ename, cast(sal \/ 1.2 as int) as old_sal, sal as new_sal\n   )\nselect *\n  from upd\n order by new_sal desc;\nrollback;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">begin<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">with<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   upd <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">update<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> sal = sal * <\/span><span style=\"color: #B5CEA8\">1.2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> sal + <\/span><span style=\"color: #DCDCAA\">coalesce<\/span><span style=\"color: #D4D4D4\">(comm, <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">) &lt; <\/span><span style=\"color: #B5CEA8\">2000<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">returning<\/span><span style=\"color: #D4D4D4\"> empno, ename, <\/span><span style=\"color: #DCDCAA\">cast<\/span><span style=\"color: #D4D4D4\">(sal \/ <\/span><span style=\"color: #B5CEA8\">1.2<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> int) <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> old_sal, sal <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> new_sal<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> upd<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">order by<\/span><span style=\"color: #D4D4D4\"> new_sal <\/span><span style=\"color: #569CD6\">desc<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #DCDCAA\">rollback<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"BEGIN\n empno | ename  | old_sal | new_sal \n-------+--------+---------+---------\n  7499 | ALLEN  |    1600 |    1920\n  7844 | TURNER |    1500 |    1800\n  7934 | MILLER |    1300 |    1560\n  7521 | WARD   |    1250 |    1500\n  7876 | ADAMS  |    1100 |    1320\n  7900 | JAMES  |     950 |    1140\n  7369 | SMITH  |     800 |     960\n(7 rows)\n\nROLLBACK\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> empno | ename  | old_sal | new_sal <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">-------+--------+---------+---------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7499 | ALLEN  |    1600 |    1920<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7844 | TURNER |    1500 |    1800<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7934 | MILLER |    1300 |    1560<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7521 | WARD   |    1250 |    1500<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7876 | ADAMS  |    1100 |    1320<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7900 | JAMES  |     950 |    1140<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7369 | SMITH  |     800 |     960<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">(7 rows)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">ROLLBACK<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>We could use this feature to implement an archiving process. Let&#8217;s say we want to move the employees of the departments <code>10<\/code> and <code>20<\/code> to a new table <code>t2<\/code>. In PostgreSQL we can do that as follows:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">3a) PostgreSQL: move rows<\/span><span role=\"button\" tabindex=\"0\" data-code=\"begin;\ndrop table if exists t2;\ncreate table t2 as select * from t1 where false;\nwith\n   del as (\n      delete from t1\n       where deptno in (10, 20)\n      returning *\n   )\ninsert into t2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)\nselect empno, ename, job, mgr, hiredate, sal, comm, deptno\n  from del\nrollback;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">begin<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">drop<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">if<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">exists<\/span><span style=\"color: #D4D4D4\"> t2;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">create<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> t2 <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">false<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">with<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   del <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">delete<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> deptno <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">returning<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">insert<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">into<\/span><span style=\"color: #D4D4D4\"> t2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> empno, ename, job, mgr, hiredate, sal, comm, deptno<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> del<\/span><\/span>\n<span class=\"line\"><span style=\"color: #DCDCAA\">rollback<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"BEGIN\nDROP TABLE\nSELECT 0\nINSERT 0 8\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">DROP TABLE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">SELECT 0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">INSERT 0 8<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>The move is implemented as a single <code>insert<\/code> statement.<\/p>\n\n\n\n<p>In the Oracle Database 23c you can use the Partitioning option to implement archiving logic efficiently. However, without this option, you could do something like this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">3b) OracleDB: move rows<\/span><span role=\"button\" tabindex=\"0\" data-code=\"drop table if exists t2;\ncreate table t2 as select * from t1 where false;\nlock table t1, t2 in share row exclusive mode;\ninsert into t2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)\nselect empno, ename, job, mgr, hiredate, sal, comm, deptno\n  from t1\n where deptno in (10, 20);\ndelete from t1\n where deptno in (10, 20);\nrollback;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">drop<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">if<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">exists<\/span><span style=\"color: #D4D4D4\"> t2;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">create<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> t2 <\/span><span style=\"color: #569CD6\">as<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">false<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #569CD6\">lock<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> t1, t2 <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> share <\/span><span style=\"color: #569CD6\">row<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">exclusive<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">mode<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #569CD6\">insert<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">into<\/span><span style=\"color: #D4D4D4\"> t2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> empno, ename, job, mgr, hiredate, sal, comm, deptno<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> deptno <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #569CD6\">delete<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> deptno <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #DCDCAA\">rollback<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"Table T2 dropped.\n\n\nTable T2 created.\n\n\nLock succeeded.\n\n\n8 rows inserted.\n\n\n8 rows deleted.\n\n\nRollback complete.\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">Table T2 dropped.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Table T2 created.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Lock succeeded.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">8 rows inserted.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">8 rows deleted.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Rollback complete.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Please note that it is not possible in the Oracle Database 23c to use a <code>returning clause<\/code> in an <code>insert<\/code> statement together with a <code>subquery<\/code>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"134\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image-1024x134.png\" alt=\"single_table_insert in Oracle Database 23c\" class=\"wp-image-13319\" style=\"width:561px\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image-1024x134.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image-300x39.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image-768x101.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image-150x20.png 150w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image-480x63.png 480w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/image.png 1123w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Supporting the <code>with clause<\/code> in <code>select<\/code>, <code>insert<\/code>, <code>update<\/code> and <code>delete<\/code> statements and allowing <code>select<\/code>, <code>insert<\/code>, <code>update<\/code> and <code>delete<\/code> in named queries is a great PostgreSQL feature thanks to the powerful <code>returning clause<\/code>. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"deleting_rows_with_merge\">Deleting Rows With Merge<\/h2>\n\n\n\n<p>In <a href=\"https:\/\/www.salvis.com\/blog\/2022\/07\/31\/deleting-rows-with-merge\/\">this blog post<\/a>, I explained that we must first update a row before we can delete it when we use a&nbsp;<code>merge<\/code>&nbsp;statement.&nbsp;This is still true for Oracle Database 23c. Let&#8217;s see if this limitation also exists in PostgreSQL.<\/p>\n\n\n\n<p>Here&#8217;s the setup script that works in PostgreSQL 16 and Oracle Database 23c. A table&nbsp;<code>t<\/code>&nbsp;(target) with three rows and a table&nbsp;<code>s<\/code>&nbsp;(source) with 4 rows.&nbsp;<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">4) Setup (PostgreSQL &amp; OracleDB)<\/span><span role=\"button\" tabindex=\"0\" data-code=\"drop table if exists t;\ncreate table t (\n   id integer     not null primary key,\n   c1 varchar(20) not null\n);\ninsert into t \nvalues (1, 'original 1'), \n       (2, 'original 2'), \n       (3, 'original 3');\ndrop table if exists s;\ncreate table s (\n   id integer     not null,\n   op varchar(1)  not null check (op in ('I', 'U', 'D')),\n   c1 varchar(20) not null\n);\ninsert into s \nvalues (1, 'U', 'original 1'), \n       (2, 'U', 'changed 2'), \n       (3, 'D', 'deleted 3'),\n       (4, 'I', 'new 4');\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">drop<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">if<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">exists<\/span><span style=\"color: #D4D4D4\"> t;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">create<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> t (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   id <\/span><span style=\"color: #569CD6\">integer<\/span><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">primary key<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   c1 <\/span><span style=\"color: #569CD6\">varchar<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">null<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">insert<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">into<\/span><span style=\"color: #D4D4D4\"> t <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">values<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;original 1&#39;<\/span><span style=\"color: #D4D4D4\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       (<\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;original 2&#39;<\/span><span style=\"color: #D4D4D4\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       (<\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;original 3&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">drop<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">if<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">exists<\/span><span style=\"color: #D4D4D4\"> s;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">create<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> s (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   id <\/span><span style=\"color: #569CD6\">integer<\/span><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   op <\/span><span style=\"color: #569CD6\">varchar<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">)  <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">check<\/span><span style=\"color: #D4D4D4\"> (op <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;I&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;U&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;D&#39;<\/span><span style=\"color: #D4D4D4\">)),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   c1 <\/span><span style=\"color: #569CD6\">varchar<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">null<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">insert<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">into<\/span><span style=\"color: #D4D4D4\"> s <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">values<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;U&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;original 1&#39;<\/span><span style=\"color: #D4D4D4\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       (<\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;U&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;changed 2&#39;<\/span><span style=\"color: #D4D4D4\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       (<\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;D&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;deleted 3&#39;<\/span><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       (<\/span><span style=\"color: #B5CEA8\">4<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;I&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;new 4&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Now let&#8217;s run a <code>merge<\/code> statement in PostgreSQL 16 and Oracle Database 23c. The syntax is different. However, the example should be self-explanatory.<\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-9d6595d7 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">5a) PostgreSQL: merge with delete<\/span><span role=\"button\" tabindex=\"0\" data-code=\"merge into t\nusing s\n   on t.id = s.id\n when matched and op = 'U' \n              and t.c1 != s.c1 then\n      update\n         set c1 = s.c1\n when matched and op = 'D' then\n      delete\n when not matched then\n      insert (id, c1)\n      values (id, c1);\nselect * from t;\n\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">merge<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">into<\/span><span style=\"color: #D4D4D4\"> t<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">using<\/span><span style=\"color: #D4D4D4\"> s<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">on<\/span><span style=\"color: #D4D4D4\"> t.id = s.id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">when<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">matched<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">and<\/span><span style=\"color: #D4D4D4\"> op = <\/span><span style=\"color: #CE9178\">&#39;U&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #569CD6\">and<\/span><span style=\"color: #D4D4D4\"> t.c1 != s.c1 <\/span><span style=\"color: #569CD6\">then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">update<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> c1 = s.c1<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">when<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">matched<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">and<\/span><span style=\"color: #D4D4D4\"> op = <\/span><span style=\"color: #CE9178\">&#39;D&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">then<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">delete<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">when<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">matched<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">insert<\/span><span style=\"color: #D4D4D4\"> (id, c1)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">values<\/span><span style=\"color: #D4D4D4\"> (id, c1);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t;<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"MERGE 3\n id |     c1     \n----+------------\n  1 | original 1\n  2 | changed 2\n  4 | new 4\n(3 rows)\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">MERGE 3<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> id |     c1     <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">----+------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  1 | original 1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  2 | changed 2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  4 | new 4<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">(3 rows)<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">5b) OracleDB: merge with delete<\/span><span role=\"button\" tabindex=\"0\" data-code=\"merge into t\nusing s\n   on (t.id = s.id)\n when matched then\n      update\n         set c1 = s.c1\n       where op = 'U'\n         and t.c1 != s.c1\n      delete\n       where op = 'D'\n when not matched then\n      insert (id, c1)\n      values (s.id, s.c1);\nselect * from t;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">merge<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">into<\/span><span style=\"color: #D4D4D4\"> t<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">using<\/span><span style=\"color: #D4D4D4\"> s<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">on<\/span><span style=\"color: #D4D4D4\"> (t.id = s.id)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">when<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">matched<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">update<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> c1 = s.c1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> op = <\/span><span style=\"color: #CE9178\">&#39;U&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #569CD6\">and<\/span><span style=\"color: #D4D4D4\"> t.c1 != s.c1<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">delete<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> op = <\/span><span style=\"color: #CE9178\">&#39;D&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">when<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">matched<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">insert<\/span><span style=\"color: #D4D4D4\"> (id, c1)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">values<\/span><span style=\"color: #D4D4D4\"> (s.id, s.c1);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"2 rows merged.\n\n\n        ID C1                  \n---------- --------------------\n         1 original 1          \n         2 changed 2           \n         3 original 3          \n         4 new 4         \" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">2 rows merged.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        ID C1                  <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">---------- --------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         1 original 1          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         2 changed 2           <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">         3 original 3          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         4 new 4         <\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n\n\n\n<p>The predicates for insert, update and delete are the same in the statement applied in PostgreSQL and Oracle Database. However, the row with the ID <code>3<\/code> was not deleted in the Oracle Database because it was not updated by the merge statement. <\/p>\n\n\n\n<p>I like that this limitation does not exist in PostgreSQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"select_without_select_list\">Select Without Select List<\/h2>\n\n\n\n<p>Does that make sense?<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">6) PostgreSQL: select without select list<\/span><span role=\"button\" tabindex=\"0\" data-code=\"select;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"--\n(1 row)\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">--<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">(1 row)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Hardly. However, what about the next example?<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">7) PostgreSQL: exists subquery without select list<\/span><span role=\"button\" tabindex=\"0\" data-code=\"select *\n  from t1 a\n where exists ( -- bosses only\n          select \n            from t1 b\n           where b.mgr = a.empno\n       );\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1 a<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">exists<\/span><span style=\"color: #D4D4D4\"> ( <\/span><span style=\"color: #6A9955\">-- bosses only<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">          <\/span><span style=\"color: #569CD6\">select<\/span><span style=\"color: #D4D4D4\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t1 b<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #569CD6\">where<\/span><span style=\"color: #D4D4D4\"> b.mgr = a.empno<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       );<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\" empno | ename |    job    | mgr  |  hiredate  | sal  | comm | deptno \n-------+-------+-----------+------+------------+------+------+--------\n  7839 | KING  | PRESIDENT |      | 1981-11-17 | 5000 |      |     10\n  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 |      |     20\n  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 |      |     30\n  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 |      |     10\n  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000 |      |     20\n  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 |      |     20\n(6 rows)\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\"> empno | ename |    job    | mgr  |  hiredate  | sal  | comm | deptno <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">-------+-------+-----------+------+------------+------+------+--------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7839 | KING  | PRESIDENT |      | 1981-11-17 | 5000 |      |     10<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 |      |     20<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 |      |     30<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 |      |     10<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000 |      |     20<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 |      |     20<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">(6 rows)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Now it makes sense. <\/p>\n\n\n\n<p>Look at line 4. In the Oracle Database, we would define an arbitrary expression to match the syntax. Similar to <code>from dual<\/code> in versions before 23c. Similar to an <code>order_by_clause<\/code>. We should not be forced to provide an unnecessary clause. IMO it&#8217;s something that should be changed in the SQL standard as well.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">More?<\/h2>\n\n\n\n<p>Yes, there is more. <\/p>\n\n\n\n<p>For example, I like that a transaction in PostgreSQL 16 also covers DDL statements. Replicating this in Oracle Database 23c might be possible for simple cases with the help of <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/sqlrf\/FLASHBACK-TABLE.html\">flashback table<\/a> or other flashback features, but it is certainly somewhat more laborious.<\/p>\n\n\n\n<p>And there are a lot of small differences between PostgreSQL 16 and Oracle Database 23c. They are different SQL dialects after all. They express the same thing differently. For example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/16\/sql-select.html#SQL-LIMIT\">limit clause<\/a> in PostgreSQL vs. SQL:2023-compliant <a href=\"https:\/\/islandsql.github.io\/IslandSQL\/sql-2023-2-foundation.html#fetch_first_clause\">fetch_first_clause<\/a> in PostgreSQL and OracleDB<\/li>\n\n\n\n<li><code>table t1;<\/code> in PostgreSQL vs. <code>select * from t1;<\/code> in PostgreSQL and OracleDB<\/li>\n\n\n\n<li><code>select empno, sal from t1 where empno=7788 for update of t1;<\/code> in PostgreSQL vs. <code>select empno, sal from t1 where empno=7788 for update of sal;<\/code> in OracleDB<\/li>\n\n\n\n<li><code>select $id$'$$text$$'$id$;<\/code> in PostgreSQL vs. <code>select q'['$$text$$']';<\/code> in OracleDB<\/li>\n\n\n\n<li><code>select @ -42;<\/code> in PostgreSQL vs. <code>select abs(-42);<\/code> in PostgreSQL and OracleDB <\/li>\n\n\n\n<li><code>select distinct on (job) job, ename from t1<\/code> in PostgreSQL vs. <code>select job, any_value(ename) as ename from t1 group by job;<\/code> in PostgreSQL and OracleDB<\/li>\n\n\n\n<li><code>select distinct on (job) job, ename from t1 order by job, sal desc;<\/code> in PostgreSQL vs. <code>select distinct job, first_value(ename) over (partition by job order by sal desc) as ename from t1 order by job;<\/code> in PostgreSQL and OracleDB<\/li>\n\n\n\n<li><code>select '42'::int as val;<\/code> in PostgreSQL vs. <code>select cast('42' as int) as val;<\/code> in PostgreSQL and OracleDB<\/li>\n<\/ul>\n\n\n\n<p>It is important to note that even if the syntax in PostgreSQL 16 and Oracle Database 23c look the same, there might be semantic differences. For example <code>select cast('42.42' as int);<\/code> produces <code>42<\/code> in OracleDB but an error in PostgreSQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Outlook<\/h2>\n\n\n\n<p>In the next episode, the IslandSQL grammar will be extended to cover the complete PL\/SQL grammar. The plan is to focus on anonymous <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/lnpls\/block.html#GUID-9ACEB9ED-567E-4E1A-A16A-B8B35214FC9D\">PL\/SQL blocks<\/a> along with functions and procedures in <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/sqlrf\/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__GUID-28DA0E1D-87BF-462E-BCB8-8F77921022F9\">plsql_declarations<\/a> of the with clause. Further SQL statements will be added afterwards.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In the last episode, we covered DML statements in SQL*Plus\/SQLcl scripts for the Oracle Database 23c. The IslandSQL grammar can now also handle PostgreSQL 16 DML statements in psql scripts. In this blog post, we will look at some features in PostgreSQL 16 which I miss in the Oracle Database 23c.<span class=\"excerpt-hellip\"> [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":13344,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[139,86,137,140,111,85],"class_list":["post-13297","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-antlr","tag-code-analysis","tag-islandsql","tag-oracle-26ai","tag-postgresql","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c - Philipp Salvisberg&#039;s Blog<\/title>\n<meta name=\"description\" content=\"In this episode, we focus on DML features in PostgreSQL 16 missed in the Oracle Database 23c. Returning, DML in With, Merge-Delete and more.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c - Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"og:description\" content=\"In this episode, we focus on DML features in PostgreSQL 16 missed in the Oracle Database 23c. Returning, DML in With, Merge-Delete and more.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-23T15:23:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-17T09:04:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/IslandSQL7.png\" \/>\n\t<meta property=\"og:image:width\" content=\"500\" \/>\n\t<meta property=\"og:image:height\" content=\"500\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c\",\"datePublished\":\"2024-03-23T15:23:45+00:00\",\"dateModified\":\"2024-06-17T09:04:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/\"},\"wordCount\":1006,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/03\\\/IslandSQL7.png\",\"keywords\":[\"ANTLR\",\"Code Analysis\",\"IslandSQL\",\"Oracle 26ai\",\"PostgreSQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/\",\"name\":\"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c - Philipp Salvisberg&#039;s Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/03\\\/IslandSQL7.png\",\"datePublished\":\"2024-03-23T15:23:45+00:00\",\"dateModified\":\"2024-06-17T09:04:27+00:00\",\"description\":\"In this episode, we focus on DML features in PostgreSQL 16 missed in the Oracle Database 23c. Returning, DML in With, Merge-Delete and more.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/03\\\/IslandSQL7.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/03\\\/IslandSQL7.png\",\"width\":500,\"height\":500,\"caption\":\"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/03\\\/23\\\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg&#039;s Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c - Philipp Salvisberg&#039;s Blog","description":"In this episode, we focus on DML features in PostgreSQL 16 missed in the Oracle Database 23c. Returning, DML in With, Merge-Delete and more.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/","og_locale":"en_US","og_type":"article","og_title":"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c - Philipp Salvisberg&#039;s Blog","og_description":"In this episode, we focus on DML features in PostgreSQL 16 missed in the Oracle Database 23c. Returning, DML in With, Merge-Delete and more.","og_url":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/","og_site_name":"Philipp Salvisberg&#039;s Blog","article_published_time":"2024-03-23T15:23:45+00:00","article_modified_time":"2024-06-17T09:04:27+00:00","og_image":[{"width":500,"height":500,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/IslandSQL7.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c","datePublished":"2024-03-23T15:23:45+00:00","dateModified":"2024-06-17T09:04:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/"},"wordCount":1006,"commentCount":1,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/IslandSQL7.png","keywords":["ANTLR","Code Analysis","IslandSQL","Oracle 26ai","PostgreSQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/","url":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/","name":"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c - Philipp Salvisberg&#039;s Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/IslandSQL7.png","datePublished":"2024-03-23T15:23:45+00:00","dateModified":"2024-06-17T09:04:27+00:00","description":"In this episode, we focus on DML features in PostgreSQL 16 missed in the Oracle Database 23c. Returning, DML in With, Merge-Delete and more.","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/IslandSQL7.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/03\/IslandSQL7.png","width":500,"height":500,"caption":"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c"},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2024\/03\/23\/islandsql-episode-7-dml-statements-in-postgresql-16-and-what-i-miss-in-oracle-database-23c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg&#039;s Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=13297"}],"version-history":[{"count":47,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13297\/revisions"}],"predecessor-version":[{"id":13346,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13297\/revisions\/13346"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/13344"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=13297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=13297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=13297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}