5.25. PostgreSQL WITH clause

In PostgreSQL WITH Clause provides a way to write auxiliary statements for use in larger queries.

WITH Clauses help break down complex large queries into simpler forms that are easy to read. These statements are often referred to as general table expressions (Common Table Express, CTE) and can also be treated as temporary tables that exist for queries.

WITH Clause is particularly useful when executing a subquery multiple times, allowing us to refer to it by its name (possibly multiple times) in the query.

WITH Clauses must be defined before they can be used.

5.25.1. Grammar ¶

The basic syntax of a 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 an existing table name and have priority.

可以在 WITH 中使用数据 INSERT , UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。

5.25.2. WITH recursion ¶

In WITH You can use your own output data in the clause.

A common table expression (CTE) has the important advantage of being able to reference itself to create a recursive CTE. A recursive CTE is a common table expression that repeatedly executes the initial CTE to return a subset of data until the complete result set is obtained.

5.25.3. Example ¶

Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# 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)

The following will use the WITH Clause queries the data in the table above:

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

The results are as follows:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
| Paul  |  32 | California|  20000
| Allen |  25 | Texas     |  15000
| Teddy |  23 | Norway    |  20000
| Mark  |  25 | Rich-Mond |  65000
| David |  27 | Texas     |  85000
| Kim   |  22 | South-Hall|  45000
| James |  24 | Houston   |  10000
(7 rows)

Next let’s use the RECURSIVE Keywords and WITH Clause to write a query to find SALARY(工资) Data with fields less than 20000 and calculate their sum:

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

The results are as follows:

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

Let’s set up a picture and COMPANY Tabularly similar COMPANY1 Tables, usin DELETE Statement and WITH Clause to delete from the COMPANY table SALARY(工资) Data with a field greater than or equal to 30000, and insert the deleted data into COMPANY1 Table, implementing the COMPANY Table data is transferred 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 results are as follows:

INSERT 0 3

At this time CAMPANY Table and CAMPANY1 The data of the table is as follows:

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


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