postgres cte - ghdrako/doc_snipets GitHub Wiki

Queries combined into a cte behave as though they were in a repeatable read transaction block
Changes made to table rows are not visible to other parts of the cte

MATERIALIZED and NOT MATERIALIZED

If we want to perform a CTE that materializes a temporary resultset, we have to add the materialized keyword:

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;

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. Behavior like i i linę views :

with posts_author_1 as not 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;

If we don’t specify any option, the default is NOT MATERIALIZED, and this could be a problem if we are migrating a database from a minor version to PostgreSQL 12. This is because the behavior of the query planner could change, and the performance could change too.

Move records

The query deletes all the records from the t_posts table that have their category as 'Database' and, in the same transaction, inserts all the records deleted in the delete_posts table, as we can see here:

with del_posts as ( 
  delete from t_posts  
   where category in (select pk 
                        from categories 
                       where title ='Database Discussions') 
     returning *) 
   insert into delete_posts select * from del_posts;

SQL query that moves, in the same transaction, all the records that are present in the t_posts table to the inserted_posts table. This query will be as follows:

with ins_posts as ( 
  insert into inserted_posts select * from t_posts returning pk) 
 delete from t_posts where pk in (select pk from ins_posts);

Batch update

Only update those rows that actually have changed thanks to using a row comparatorin the update part of them CTE. Finally, note the usage of an anti-join in the insert part of them CTE in order to only insert data we didn’t have already.

with upd as 
 ( 
 update moma.artist 
 set (name, bio, nationality, gender, begin, "end", wiki_qid, ulan) 
 = (batch.name, batch.bio, batch.nationality, 
    batch.gender, batch.begin, batch."end", 
    batch.wiki_qid, batch.ulan) 
 from batch 
where batch.constituentid = artist.constituentid 
 and (artist.name, artist.bio, artist.nationality, 
      artist.gender, artist.begin, artist."end", 
      artist.wiki_qid, artist.ulan) 
 <> (batch.name, batch.bio, batch.nationality, 
 batch.gender, batch.begin, batch."end", 
 batch.wiki_qid, batch.ulan) 
 returning artist.constituentid 
 ), 
 ins as 
 ( 
 insert into moma.artist 
 select constituentid, name, bio, nationality, 
 gender, begin, "end", wiki_qid, ulan 
 from batch 
 where not exists 
 ( 
 select 1 
 from moma.artist 
 where artist.constituentid = batch.constituentid 
 ) 
 returning artist.constituentid 
 ) 
 select (select count(*) from upd) as updates, 
 (select count(*) from ins) as inserts;

Recursive CTE

Recursive CTEs A recursive CTE is a special construct that allows an auxiliary statement to reference itself and, therefore, join itself onto previously computed results. This is particularly useful when we need to join a table an unknown number of times, typically to “explode” a fl at tree structure. The traditional solution would involve some kind of iteration, probably by means of a cursor that iterates one tuple at a time over the whole resultset. However, with recursive CTEs, we can use a much cleaner and simpler approach. A recursive CTE is made by an auxiliary statement that is built on top of the following:

  • A non-recursive statement, which works as a bootstrap statement and is executed when the auxiliary term is fi rst evaluated
  • A recursive statement, which can either reference the bootstrap statement or itself These
select * from tags order by pk;
 pk | tag | parent 
----+-------------------+-------- 
 1 | Database |  
 2 | Operating Systems |  
 3 | PostgreSQL | 1

WITH RECURSIVE tags_tree AS (
 -- non recursive statement 
 SELECT tag, pk, 1 AS level FROM tags WHERE parent IS NULL UNION 
-- recursive statement 
SELECT tt.tag|| ' -> ' || ct.tag, ct.pk , tt.level + 1 FROM tags ct JOIN tags_tree tt ON tt.pk = ct.parent ) SELECT level,tag FROM tags_tree order by level;
 level | tag
-------+------------------------ 
1 | Database  
1 | Operating Systems  
2 | Database -> PostgreSQL

Insert using cte

insert into geoname.neighbour 
 with n as ( 
 select isocode, 
 regexp_split_to_table(neighbours, ',') as neighbour 
 from raw.country 
 ) 
 select n.isocode, 
 country.isocode 
 from n 
 join geoname.country 
 on country.iso = n.neighbour;