PostgreSQL WITH Clause

In PostgreSQL, the WITH clause provides a method for writing auxiliary statements to be used in larger queries.

The WITH clause helps to decompose complex large queries into simpler forms for easier reading. These statements are usually called Common Table Expressions (CTE) and can also be treated as temporary tables existing for the query.

The WITH clause is particularly useful when executing subqueries multiple times, allowing us to refer to it by its name (which may be multiple times) in the query.

The WITH clause must be defined before use.

Syntax

The basic syntax of the 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)
　　　[ORDER BY columns]

name_for_summary_data is the name of the WITH clause,name_for_summary_data Can be the same as the existing table name and have priority.

INSERT, UPDATE, or DELETE statements can be used in WITH, allowing you to perform multiple different operations in the same query.

WITH RECURSIVE

Data output from itself can be used in the WITH clause.

The Common Table Expression (CTE) has an important advantage that it can refer to itself, thus creating a recursive CTE. A recursive CTE is a common table expression that repeats the execution of the initial CTE to return a subset of data until the complete result set is obtained.

Online example

Create COMPANY table (Download COMPANY SQL file ），data content as follows：

w3codeboxdb# 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 in the above table:

With CTE AS
(Select
　ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select　*　From CTE;

The result obtained 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 RECURSIVE Write a query using the keyword and WITH clause to find SALARY(salary) Field less than 2Calculate the sum of the data for 0000:

WITH RECURSIVE t(n) AS (
　　　VALUES (0)
　　　UNION　ALL
　　　SELECT　SALARY　FROM　COMPANY　WHERE　SALARY　<　20000
)
SELECT　sum(n)　FROM　t;

The result obtained is as follows:

　sum
-------
　25000
(1　row)

We will establish a table similar to the COMPANY table called COMPANY1 table, using the DELETE statement and WITH clause to delete the COMPANY table SALARY(salary) field greater than or equal to 30000 of the data, and insert the deleted data into COMPANY1 Table, to implement the transfer of COMPANY table data to COMPANY1 Table：

CREATE　TABLE　COMPANY1(
　　　ID　INT　PRIMARY　KEY　　　　　NOT　NULL,
　　　NAME　　　　　　　　　　　TEXT　　　　NOT　NULL,
　　　AGE　　　　　　　　　　　　INT　　　　　NOT　NULL,
　　　ADDRESS　　　　　　　　CHAR(50),
　　　SALARY　　　　　　　　　REAL
);
WITH　moved_rows　AS　(
　　　DELETE　FROM　COMPANY
　　　WHERE
　　　　　　SALARY　>=　30000
　　　RETURNING　*
)
INSERT　INTO　COMPANY1　(SELECT　*　FROM　moved_rows);

The result obtained is as follows:

INSERT　0　3

At this time, the CAMPANY table and CAMPANY1 The data in the table is as follows:

w3codeboxdb=#　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)
w3codeboxdb=#　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)