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, making them easier to read. These statements are commonly referred to as Common Table Expressions (CTE), and can be treated as temporary tables for the purpose of the query.

The WITH clause is particularly useful when executing subqueries 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 for 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]

nameforsummarydata is the name of the WITH clause, nameforsummarydata can be the same as an existing table name and has precedence.

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

Recursive WITH

The WITH clause can use its own output data.

Common Table Expressions (CTE) have an important advantage in that they can reference themselves, thereby creating recursive CTEs. A recursive CTE is a CTE that repeatedly executes the initial CTE to return a subset of data until it obtains the complete result set.

Example

Create a COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# 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 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's write a query using the RECURSIVE keyword and the WITH clause to find data where the 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)

Below we create a table COMPANY1 similar to the COMPANY table, using the DELETE statement and WITH clause to remove records from the COMPANY table where the SALARY field is greater than or equal to 30000, and insert the deleted data 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 COMPANY and COMPANY1 tables are as follows:

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

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