Common Table Expression In Postgres
Apr 22nd, 2022 - written by Kimserey with .
Common table expression, also called WITH queries, can be used to create auxiliary queries that can be used in larger queries. They can be used to breakdown complex queries into multiple simpler queries which are then used in a primary statement or they can also be used to write recursive queries. In today’s post, we will look at how to define CTE with examples in Postgres.
Breakdown Complex Queries
Breaking down complex queries using common table expression can be done as followed:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with salaries as (
select
id,
title,
(case when remuneration < 100 then 'low' when remuneration < 500 then 'medium' else 'high' end) as category
from job
), groups as (
select
count(*) as job_count,
max(category) as category
from salaries
group by category
)
select * from groups
where category != 'low'
We start by using with keyword to create a table result salaries which will contain the jobs with a salary categorised between low, medium and high.
We then continue with another common table by separating with a comma, and use salaries to group by the category.
And lastly we use the common tables into the primary statement which exclude low.
We can see how this notation can be used to breakdown queries into small parts and assembling them to be usable in the primary statement.
Recursive Queries
Another usage of CTE is to execute recursive queries. The format of a recursive query is as followed:
1
2
3
4
5
6
with recursive t(n) as (
values (1)
union all
select n+1 from t where n < 10
)
select * from t
which will return:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+----+
| n |
|----|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
SELECT 10
Time: 0.003s
The definition of the recursive CTE is a non-recursive statement, followed by union or union all and a recursive statement.
The first non-recursive statement is the initial dataset extracted, where the recursive statement will be applied to. At each application of the recursive statement, the data is appended following either union or union all, and the process is repeated until there is no more item returned by the recursive statement.
1
2
3
4
5
6
7
8
with recursive all_jobs as (
select id, title, parentJobId from job where id = '1'
union all
select j.id, j.title, j.parentJobId
from job j
join all_jobs pj on pj.id = j.parentJobId
)
select * from all_jobs
With this query we are able to recursively query all the children of job 1 at all levels. This is achieved by first getting job 1 in the non-recursive statement, then on the recursive statement, join the table back and retrieve rows that have the jobs as parent.
Lastly just like regular CTE, the recursive CTE can also be combined:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with recursive all_jobs as (
select id, title, parentJobId, remuneration from job where id = '1'
union all
select j.id, j.title, j.parentJobId, j.remuneration
from job j
join all_jobs pj on pj.id = j.parentJobId
), salaries as (
select
id,
title,
parentJobId,
(case when remuneration < 100 then 'low' when remuneration < 500 then 'medium' else 'high' end) as category
from all_jobs
)
select * from salaries
Just as an example we added our previous auxiliary statement which categories the jobs.
And that concludes today’s post on CTE!
Conclusion
In today’s post, we looked at Postgres common table expression. We started by looking at how they could be used to breakdown complex queries in smaller pieces and we then moved on to look at how we could write recursive queries. I hope you liked this post, and I’ll see you on the next one!