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