CTE(common table expression) 用來表示一條query執行的結果，寫法為將query用WITH clause包起來，例如下面的query:

with posts_author_1 as materialized
(select p.\* from posts p
inner join users u on p.author=u.pk
where username='scotty')
select pk,title from posts_author_1;

從PostgreSQL 12之後，CTE有MATERIALIZED以及NOT MATERIALIZED兩種選項，MATERIALIZE代表將CTE分開執行後，把結果暫存起來，而一般情況下PostgreSQL會視情況把CTE展開，執行的方式會稍微不一樣。

使用CTE的例子:
1.在table之間移動內容
// 建立t_posts內容

create temp table t_posts as select * from posts;

// 建立欄位與t_posts相同但是沒有資料的table inserted_posts
forumdb=# create table inserted_posts as select * from posts limit
0;
// 使用CTE的寫法，將t_posts內容插入inserted_posts之後，透過returning clause回傳的key來對ins_posts內容進行刪除，達到資料搬移的效果，寫在一條query就完成。值得一提的是，在CTE裡面做INSERT/UPDATE/DELETE是PostgreSQL特有的功能，能這樣做的其他資料庫並不多。

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);

// 檢視結果，資料已經移動過去了

select pk,title,category from t_posts ;

pk | title | category
----+-------+----------
(0 rows)

select pk,title,category from inserted_posts ;

pk | title | category
----+---------------+----------
4 | Re:my orange | 11
5 | my tomato | 12
2 | my orange | 11
6 | my new orange | 11
3 | my new apple | 10
(5 rows)

2.在一條Query內做多次的insert到不同的table
// 程式碼，將資料新增到table foo取得回傳id之後和另外的資料一起插入table other，用了兩條SQL

new_id = db.query("""
  INSERT INTO foo(blah, wibble)
  VALUES($1, $2)
  RETURNING id""",
  "blah", "wibble")
db.query("""
  INSERT INTO other(foo_id, floogle)
  VALUES($1, $2)""",
  new_id, 42)

// 將第一條query包進CTE，讓第二條query參考其回傳結果來執行

WITH step_one AS (
  INSERT INTO foo(blah, wibble)
  VALUES($1, $2)
  RETURNING id
)
INSERT INTO other(foo_id, floogle)
SELECT id, $3 FROM step_one;

(來源:https://dev.to/dwd/efficient-insert-multiple-with-postgres-3f06)

3.遞迴查詢
CTE也可以參考自己的查詢結果，比如說自己和自己的結果做join，可以用來做遞迴，適用在join的次數不確定的時候，像是樹狀結構的展開。
作法是在定義CTE的時候使用WITH RECURSIVE，然後在CTE裡面做兩條query的UNION(執行結果做聯集)。第一條query指定初始的狀態，第二條query指定做遞迴時要執行的動作。
例子:
// tags table內容，是樹狀結構，主鍵三號的節點是一號節點的孩子
pk | tag | parent
----+------------+--------
1 | fruits |
2 | vegetables |
3 | apple | 1
(3 rows)
// 遞迴印出tag之間的主從關係

WITH RECURSIVE tags_tree AS (
-- non recursive statment
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 | fruits
1 | vegetables
2 | fruits -> apple
(3 rows)