common table expressions in postgres

Four weeks ago I was askes to show some features of PostgreSQL. In that presentation I came up with an interesting statement, with which I could show nice feature.

What I’m talking about is the usage of common table expressions (or short CTE) and explain.

Common table expressions create a temporary table just for this query. The result can be used anywhere in the rest of the query. It is pretty useful to group sub selects into smaller chunks, but also to create DML statements which return data.

A statement using CTEs can look like this:

with numbers as (
  select generate_series(1,10)
)
select * from numbers;

But it gets even nicer, when we can use this to move data between tables, for example to archive old data.

Lets create a table and an archive table and try it out.

$ create table foo(
  id serial primary key,
  t text
);
$ create table foo_archive(
  like foo
);
$ insert into foo(t)
  select generate_series(1,500);

The like option can be used to copy the table structure to a new table.

The table foo is now filled with data. Next we will delete all rows where the modulus 25 of the ID resolves to 0 and insert the row to the archive table.

$ with deleted_rows as (
  delete from foo where id % 25 = 0 returning *
)
insert into foo_archive select * from deleted_rows;

Another nice feature of postgres is the possibility to get an explain from a delete or insert. So when we prepend explain to the above query, we get this explain:

                            QUERY PLAN
───────────────────────────────────────────────────────────────────
 Insert on foo_archive  (cost=28.45..28.57 rows=6 width=36)
   CTE deleted_rows
     ->  Delete on foo  (cost=0.00..28.45 rows=6 width=6)
           ->  Seq Scan on foo  (cost=0.00..28.45 rows=6 width=6)
                 Filter: ((id % 25) = 0)
   ->  CTE Scan on deleted_rows  (cost=0.00..0.12 rows=6 width=36)
(6 rows)

This explain shows, that a sequence scan is done for the delete and grouped into the CTE deleted_rows, our temporary view. This is then scanned again and used to insert the data into foo_archive.