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...