PostgreSQL WITH Clause

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 readability. These statements are often called Common Table Expressions (CTEs) and can also be thought of as temporary tables that exist for the query.

The WITH clause is especially useful when a subquery is executed multiple times, allowing us to reference it by its name (possibly multiple times) within the query.

The WITH clause must be defined before it is used.

Syntax

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_datais the name of the WITH clause,name_for_summary_dataIt can be the same as an existing table name, and takes precedence.

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

WITH Recursive

In the WITH clause, you can use data output by itself.

A common table expression (CTE) has an important advantage: it can reference itself, thus creating a recursive CTE. A recursive CTE is a common table expression that repeatedly executes the initial CTE to return subsets of data until the complete result set is obtained.

Examples

Create the COMPANY table (Download the COMPANY SQL file), the data content is as follows:

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

Next, we will use the WITH clause to query data from the above table:

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 us useRECURSIVEkeyword and the WITH clause to write a query to findSALARYrecords whose SALARY field is 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 result is as follows:

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

Next, we create a COMPANY1 table similar to the COMPANY table, and use the DELETE statement and the WITH clause to delete from the COMPANY tableSALARYrecords whose SALARY field is greater than or equal to 30000, and insert the deleted records into the COMPANY1 table, thereby transferring data from the COMPANY table to the 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 is as follows:

INSERT 0 3

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

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


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

Other extensions