IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c

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.

• Returning Clause
• Insert, Update and Delete in the With Clause
• Deleting Rows With Merge
• Select Without Select List

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.

Returning Clause

Let’s create a table t1 with some test data. The script works in PostgreSQL 16 and Oracle Database 23c.

create table t1 as
select * 
  from (values
          (7839, 'KING',   'PRESIDENT', null, date '1981-11-17', 5000, null, 10),
          (7566, 'JONES',  'MANAGER',   7839, date '1981-04-02', 2975, null, 20),
          (7698, 'BLAKE',  'MANAGER',   7839, date '1981-05-01', 2850, null, 30),
          (7782, 'CLARK',  'MANAGER',   7839, date '1981-06-09', 2450, null, 10),
          (7788, 'SCOTT',  'ANALYST',   7566, date '1987-04-19', 3000, null, 20),
          (7902, 'FORD',   'ANALYST',   7566, date '1981-12-03', 3000, null, 20),
          (7499, 'ALLEN',  'SALESMAN',  7698, date '1981-02-20', 1600,  300, 30),
          (7521, 'WARD',   'SALESMAN',  7698, date '1981-02-22', 1250,  500, 30),
          (7654, 'MARTIN', 'SALESMAN',  7698, date '1981-09-28', 1250, 1400, 30),
          (7844, 'TURNER', 'SALESMAN',  7698, date '1981-09-08', 1500,    0, 30),
          (7900, 'JAMES',  'CLERK',     7698, date '1981-12-03',  950, null, 30),
          (7934, 'MILLER', 'CLERK',     7782, date '1982-01-23', 1300, null, 10),
          (7369, 'SMITH',  'CLERK',     7902, date '1980-12-17',  800, null, 20),
          (7876, 'ADAMS',  'CLERK',     7788, date '1987-05-23', 1100, null, 20)                        
       ) s (empno, ename, job, mgr, hiredate, sal, comm, deptno);

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.

In PostgreSQL we can do the following:

begin;
update t1 
   set sal = sal * 1.2
 where sal + coalesce(comm, 0) < 2000
returning empno, ename, cast(sal / 1.2 as int) as old_sal, sal as new_sal;
rollback;

BEGIN
 empno | ename  | old_sal | new_sal 
-------+--------+---------+---------
  7499 | ALLEN  |    1600 |    1920
  7521 | WARD   |    1250 |    1500
  7844 | TURNER |    1500 |    1800
  7900 | JAMES  |     950 |    1140
  7934 | MILLER |    1300 |    1560
  7369 | SMITH  |     800 |     960
  7876 | ADAMS  |    1100 |    1320
(7 rows)

UPDATE 7
ROLLBACK

The returning clause allows us to define a list of expressions to be returned for each changed row. So, an update, insert and delete statement in PostgreSQL can return a result similar to a select statement.

Doing the same in the Oracle Database 23c requires some PL/SQL code. For example something like this:

set serveroutput on size unlimited
declare
   cursor c1 is select empno, ename, sal as old_sal, sal as new_sal from t1;
   type t_row_type is table of c1%rowtype;
   t_row t_row_type;
begin
   update t1
      set sal = sal * 1.2
    where sal + coalesce(comm, 0) < 2000
   return empno, ename, old sal, new sal
     bulk collect into t_row;
   dbms_output.put_line(sql%rowcount || ' rows updated.');
   dbms_output.put_line(null);
   dbms_output.put_line('EMPNO ENAME  OLD_SAL NEW_SAL');
   dbms_output.put_line('----- ------ ------- -------');
   for i in t_row.first..t_row.last
   loop
      dbms_output.put_line(rpad(t_row(i).empno, 6)
         || rpad(t_row(i).ename, 7)
         || lpad(t_row(i).old_sal, 7)
         || ' '
         || lpad(t_row(i).new_sal, 7));
   end loop;
   rollback;
end;
/

7 rows updated.

EMPNO ENAME  OLD_SAL NEW_SAL
----- ------ ------- -------
7499  ALLEN     1600    1920
7521  WARD      1250    1500
7844  TURNER    1500    1800
7900  JAMES      950    1140
7934  MILLER    1300    1560
7369  SMITH      800     960
7876  ADAMS     1100    1320


PL/SQL procedure successfully completed.

There is one thing I like about this solution. On line 10 we refer to the old value of the column sal. We do not need to reverse the logic used in the update to get the old value.

Of course, there are other solutions. Like the next one:

set verify off
column start_scn new_value scn noprint
lock table t1 in share row exclusive mode;
select dbms_flashback.get_system_change_number as start_scn;
update t1
   set sal = sal * 1.2
 where sal + coalesce(comm, 0) < 2000;
select empno, ename, cast(sal / 1.2 as int) as old_sal, sal as new_sal
  from t1
 where (empno, sal) not in (select empno, sal from t1 as of scn &&scn)
 order by rowid;
rollback;

Lock succeeded.


7 rows updated.


     EMPNO ENAME     OLD_SAL    NEW_SAL
---------- ------ ---------- ----------
      7499 ALLEN        1600       1920
      7521 WARD         1250       1500
      7844 TURNER       1500       1800
      7900 JAMES         950       1140
      7934 MILLER       1300       1560
      7369 SMITH         800        960
      7876 ADAMS        1100       1320

7 rows selected. 


Rollback complete.

This solution does not use the returning clause. Instead, after the update statement, a rather costly query produces the result. However, it’s important to note that the lock table statement on line 3 is necessary to ensure that another session cannot change the table t1 after querying the current SCN and before the start of the update statement. In other words, this guarantees that we get only the rows changed by the update statement.

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 returning clause in PostgreSQL?

Insert, Update and Delete in the With Clause

Yes, we can. In PostgreSQL we sort the result of an update statement like this:

begin;
with
   upd as (
      update t1 
         set sal = sal * 1.2
       where sal + coalesce(comm, 0) < 2000
      returning empno, ename, cast(sal / 1.2 as int) as old_sal, sal as new_sal
   )
select *
  from upd
 order by new_sal desc;
rollback;

BEGIN
 empno | ename  | old_sal | new_sal 
-------+--------+---------+---------
  7499 | ALLEN  |    1600 |    1920
  7844 | TURNER |    1500 |    1800
  7934 | MILLER |    1300 |    1560
  7521 | WARD   |    1250 |    1500
  7876 | ADAMS  |    1100 |    1320
  7900 | JAMES  |     950 |    1140
  7369 | SMITH  |     800 |     960
(7 rows)

ROLLBACK

We could use this feature to implement an archiving process. Let’s say we want to move the employees of the departments 10 and 20 to a new table t2. In PostgreSQL we can do that as follows:

begin;
drop table if exists t2;
create table t2 as select * from t1 where false;
with
   del as (
      delete from t1
       where deptno in (10, 20)
      returning *
   )
insert into t2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
select empno, ename, job, mgr, hiredate, sal, comm, deptno
  from del
rollback;

BEGIN
DROP TABLE
SELECT 0
INSERT 0 8

The move is implemented as a single insert statement.

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:

drop table if exists t2;
create table t2 as select * from t1 where false;
lock table t1, t2 in share row exclusive mode;
insert into t2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
select empno, ename, job, mgr, hiredate, sal, comm, deptno
  from t1
 where deptno in (10, 20);
delete from t1
 where deptno in (10, 20);
rollback;

Table T2 dropped.


Table T2 created.


Lock succeeded.


8 rows inserted.


8 rows deleted.


Rollback complete.

Please note that it is not possible in the Oracle Database 23c to use a returning clause in an insert statement together with a subquery.

Supporting the with clause in select, insert, update and delete statements and allowing select, insert, update and delete in named queries is a great PostgreSQL feature thanks to the powerful returning clause.

Deleting Rows With Merge

In this blog post, I explained that we must first update a row before we can delete it when we use a merge statement. This is still true for Oracle Database 23c. Let’s see if this limitation also exists in PostgreSQL.

Here’s the setup script that works in PostgreSQL 16 and Oracle Database 23c. A table t (target) with three rows and a table s (source) with 4 rows. 

drop table if exists t;
create table t (
   id integer     not null primary key,
   c1 varchar(20) not null
);
insert into t 
values (1, 'original 1'), 
       (2, 'original 2'), 
       (3, 'original 3');
drop table if exists s;
create table s (
   id integer     not null,
   op varchar(1)  not null check (op in ('I', 'U', 'D')),
   c1 varchar(20) not null
);
insert into s 
values (1, 'U', 'original 1'), 
       (2, 'U', 'changed 2'), 
       (3, 'D', 'deleted 3'),
       (4, 'I', 'new 4');

Now let’s run a merge statement in PostgreSQL 16 and Oracle Database 23c. The syntax is different. However, the example should be self-explanatory.

merge into t
using s
   on t.id = s.id
 when matched and op = 'U' 
              and t.c1 != s.c1 then
      update
         set c1 = s.c1
 when matched and op = 'D' then
      delete
 when not matched then
      insert (id, c1)
      values (id, c1);
select * from t;

MERGE 3
 id |     c1     
----+------------
  1 | original 1
  2 | changed 2
  4 | new 4
(3 rows)

merge into t
using s
   on (t.id = s.id)
 when matched then
      update
         set c1 = s.c1
       where op = 'U'
         and t.c1 != s.c1
      delete
       where op = 'D'
 when not matched then
      insert (id, c1)
      values (s.id, s.c1);
select * from t;

2 rows merged.


        ID C1                  
---------- --------------------
         1 original 1          
         2 changed 2           
         3 original 3          
         4 new 4         

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 3 was not deleted in the Oracle Database because it was not updated by the merge statement.

I like that this limitation does not exist in PostgreSQL.

Select Without Select List

Does that make sense?

select;

--
(1 row)

Hardly. However, what about the next example?

select *
  from t1 a
 where exists ( -- bosses only
          select 
            from t1 b
           where b.mgr = a.empno
       );

 empno | ename |    job    | mgr  |  hiredate  | sal  | comm | deptno 
-------+-------+-----------+------+------------+------+------+--------
  7839 | KING  | PRESIDENT |      | 1981-11-17 | 5000 |      |     10
  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 |      |     20
  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 |      |     30
  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 |      |     10
  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000 |      |     20
  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 |      |     20
(6 rows)

Now it makes sense.

Look at line 4. In the Oracle Database, we would define an arbitrary expression to match the syntax. Similar to from dual in versions before 23c. Similar to an order_by_clause. We should not be forced to provide an unnecessary clause. IMO it’s something that should be changed in the SQL standard as well.

More?

Yes, there is more.

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 flashback table or other flashback features, but it is certainly somewhat more laborious.

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:

• limit clause in PostgreSQL vs. SQL:2023-compliant fetch_first_clause in PostgreSQL and OracleDB
• table t1; in PostgreSQL vs. select * from t1; in PostgreSQL and OracleDB
• select empno, sal from t1 where empno=7788 for update of t1; in PostgreSQL vs. select empno, sal from t1 where empno=7788 for update of sal; in OracleDB
• select $id$'$$text$$'$id$; in PostgreSQL vs. select q'['$$text$$']'; in OracleDB
• select @ -42; in PostgreSQL vs. select abs(-42); in PostgreSQL and OracleDB
• select distinct on (job) job, ename from t1 in PostgreSQL vs. select job, any_value(ename) as ename from t1 group by job; in PostgreSQL and OracleDB
• select distinct on (job) job, ename from t1 order by job, sal desc; in PostgreSQL vs. select distinct job, first_value(ename) over (partition by job order by sal desc) as ename from t1 order by job; in PostgreSQL and OracleDB
• select '42'::int as val; in PostgreSQL vs. select cast('42' as int) as val; in PostgreSQL and OracleDB

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 select cast('42.42' as int); produces 42 in OracleDB but an error in PostgreSQL.

Outlook

In the next episode, the IslandSQL grammar will be extended to cover the complete PL/SQL grammar. The plan is to focus on anonymous PL/SQL blocks along with functions and procedures in plsql_declarations of the with clause. Further SQL statements will be added afterwards.