Search notes:

PostgreSQL: with clause containing a delete statement

Today, I stumbled upon a cool SQL feature (via Markus Winand): the with clause containing a delete statement that returns the deleted records to a subsequent SQL statement.

The subsequent SQL can then process the «deleted» records further.

Apparently, this feature is only available on PostgreSQL (but I'd like to be corrected on that if I am wrong).

I try to demonstrate this feature in the following simple example:

First, we need to create a table to store some data. It contains «important» and «less important» data.

create table abc (
   id              integer     not null,
   val_important   varchar(10) not null,
   val_unimportant varchar(10) not null
);

Github repository about-PostgreSQL, path: /sql/with/delete/create-table-abc.sql

Then, we need a history table to receive the important data when it is deleted in abc (for whatever reason that I am not able to make up a good reason, currently).

create table abc_hist (
   id              integer     not null,
   val_important   varchar(10) not null,
   delete_date     timestamp       null
);

Github repository about-PostgreSQL, path: /sql/with/delete/create-table-abc_hist.sql

Fill some values.

insert into abc values (1, 'one'  , 'foo');
insert into abc values (2, 'two'  , 'bar');
insert into abc values (3, 'three', 'baz');
insert into abc values (4, 'four' , 'bla');

Github repository about-PostgreSQL, path: /sql/with/delete/insert.sql

Delete one record. Use the with clause and pass the deleted records to an outer insert statement, so that the deleted record can be inserted into the history table with the timestamp (localtimestamp) of the deletion:

with del as (
   delete from abc
   where
     id = 1
   returning *
)
insert into abc_hist
select
   del.id,
   del.val_important,
   localtimestamp
from
   del;

Github repository about-PostgreSQL, path: /sql/with/delete/delete-one-record.sql

Same thing as above, but with multiple records:

with del as (
   delete from abc
   where
     id < 4
   returning *
)
insert into abc_hist
select
   del.id,
   del.val_important,
   localtimestamp
from
   del;

Github repository about-PostgreSQL, path: /sql/with/delete/delete-multiple-records.sql

Be sure, we actually deleted three records …

select * from abc;
--
-- id   val_im
-- --   ------ ---
-- 4    four   bla

Github repository about-PostgreSQL, path: /sql/with/delete/select-from-abc.sql

… and inserted them into the history table:

select * from abc_hist;
--
-- id   val_im  delete_date
-- --   ------  --------------------------
-- 1    one     2019-06-07 20:18:50.053954
-- 2    two     2019-06-07 20:19:37.92388
-- 3    three   2019-06-07 20:19:37.92388

Github repository about-PostgreSQL, path: /sql/with/delete/select-from-abc_hist.sql

Index