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
.
![single_table_insert in Oracle Database 23c](http://wpfc.ml/b.gif)
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 OracleDBselect 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 OracleDBselect $id$'$$text$$'$id$;
in PostgreSQL vs.select q'['$$text$$']';
in OracleDBselect @ -42;
in PostgreSQL vs.select abs(-42);
in PostgreSQL and OracleDBselect 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 OracleDBselect 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 OracleDBselect '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.
1 Comment
[…] the last episode, we looked at some features in PostgreSQL which I miss in the Oracle Database. The IslandSQL […]