CTE in PostgreSQL since version 12
Starting from PostgreSQL version 12, things have changed, and two new options have been introduced for the execution of a CTE, namely MATERIALIZED and NOT MATERIALIZED. If we want to perform a CTE that materializes a temporary resultset, we have to add the materialized keyword:
forumdb=> with posts_author_1 as materialized
(select p.* from posts p
inner join users u on p.author=u.pk
where username='enrico_pirozzi')
select pk,title from posts_author_1;
pk | title
----+------------------------------
3 | A view of Data types in C++
(1 row)
The query written here materializes a temporary resultset, as happened automatically in previous versions of PostgreSQL. If we write the query with the NOT MATERIALIZE option, PostgreSQL will not materialize any temporary resultset:
forumdb=> with posts_author_1 as not materialized
(select p.* from posts p
inner join users u on p.author=u.pk
where username...