YouTip

In PostgreSQL, the WITH clause provides a way to write auxiliary statements for use in a larger query. The WITH clause helps break down complex, large queries into simpler forms for easier reading. These statements are commonly called Common Table Expressions (CTE), and can also be treated as a temporary table that exists for a query. The WITH clause is particularly useful when executing subqueries multiple times, allowing us to reference it by its name (possibly multiple times) in the query. The WITH clause must be defined before it can be used. ### Syntax The basic syntax for WITH query is as follows: WITH name_for_summary_data AS ( SELECT Statement) SELECT columns FROM name_for_summary_data WHERE conditions ( SELECT column FROM name_for_summary_data) **name_for_summary_data** is the name of the WITH clause, which can be the same as an existing table name and takes precedence. You can use INSERT, UPDATE, or DELETE statements within WITH, allowing you to perform multiple different operations in the same query. ### WITH RECURSIVE In the WITH clause, you can use data that outputs from itself. Common Table Expressions (CTE) have an important advantage: they can reference themselves, creating recursive CTEs. A recursive CTE is a Common Table Expression that repeatedly executes the initial CTE to return data subsets until the complete result set is obtained. ### Examples Create a COMPANY table ((https://static.jyshare.com/download/company.sql)), with the following data: tutorialdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000(7 rows) Below we will use the WITH clause to query data from the table above: With CTE AS (Select ID , NAME , AGE , ADDRESS , SALARY FROM COMPANY )Select * From CTE; The result is as follows: id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000(7 rows) Next, let's use the **RECURSIVE** keyword with the WITH clause to write a query that finds data where the **SALARY** field is less than 20000 and calculates their sum: WITH RECURSIVE t(n) AS ( VALUES (0) UNION ALL SELECT SALARY FROM COMPANY WHERE SALARY = 30000 RETURNING *) INSERT INTO COMPANY1 (SELECT * FROM moved_rows); The result is as follows: INSERT 0 3 At this point, the data in the COMPANY table and COMPANY1 table is as follows: tutorialdb=# SELECT * FROM COMPANY; id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 7 | James | 24 | Houston | 10000(4 rows) tutorialdb=# SELECT * FROM COMPANY1; id | name | age | address | salary ----+-------+-----+-------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000(3 rows)