PostgreSQL CTE

Summary: in this tutorial, you will learn how to use the PostgreSQL Common Table Expression (CTE) to query data from the database.

Getting Started with PostgreSQL CTE #

CTE stands for common table expression. PostgreSQL CTE provides a way to define a temporary table that can be referenced within a SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Here’s the syntax for defining a CTE:

WITH cte_name(column_list) AS (
   -- CTE query
   SELECT ...
)
-- Main query
SELECT select_list
FROM cte_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• The WITH keyword defines a common table expression( CTE). You can think of a CTE as a temporary table within the query.
• The cte_name is the name you assign to the CTE. Later, you can reference the cte_name in the main query as a regular table.
• The column_list is an optional common-separated list of columns of the CTE. If you don’t specify the column_list, the CTE will use the columns returned from the CTE Query.
• The CTE Query is a statement that defines the CTE’s structure. It can be any statement that returns a result set including SELECT, INSERT, UPDATE, DELETE, or MERGE.
• The Main Query is a statement that uses the CTE by referencing the cte_name. The main query can be a SELECT, INSERT, UPDATE, DELETE, or MERGEstatement.

Basic PostgreSQL CTE example #

The following example uses a CTE to calculate the maximum inventory value across all warehouses:

WITH warehouse_inventories (warehouse_name, amount) AS (
    SELECT
      warehouse_name,
      SUM(quantity * price)
    FROM
      inventories
      INNER JOIN products USING (product_id)
      INNER JOIN warehouses USING (warehouse_id)
    GROUP BY
      warehouse_name
)
SELECT
  MAX(amount)
FROM
  warehouse_inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    max
------------
 2419483.10Code language: plaintext (plaintext)

How it works.

First, define a CTE called warehouse_inventories with two columns:

• warehouse_name
• amount

In the CTE query, retrieve the total inventory per warehouse from the three tables inventories, products, and warehouses using INNER JOIN and GROUP BY clauses:

SELECT
  warehouse_name,
  SUM(quantity * price)
FROM
  inventories
  INNER JOIN products USING (product_id)
  INNER JOIN warehouses USING (warehouse_id)
GROUP BY
  warehouse_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     warehouse_name      |    sum
-------------------------+------------
 San Francisco Warehouse | 2419483.10
 Los Angeles Warehouse   | 2379982.20
 San Jose Warehouse      | 2044481.10Code language: plaintext (plaintext)

Second, retrieve the max value from the warehouse_inventories in the main query:

SELECT
  MAX(amount)
FROM
  warehouse_inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL CTE with DELETE statement example #

First, create a table called product_logs for storing deleted products:

CREATE TABLE product_logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INT NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(11, 2),
  brand_id INT NOT NULL,
  category_id INT NOT NULL,
  deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, use a CTE to delete products with id less than or equal 3 and insert these deleted products into the product_logs table:

WITH deleted_products AS (
    DELETE FROM products
    WHERE product_id <= 3 
    RETURNING *
)
INSERT INTO
  product_logs (product_id, product_name, price,  brand_id, category_id)
SELECT
  product_id, product_name, price, brand_id, category_id 
FROM
  deleted_products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, retrieve data from the product_logs table:

SELECT * FROM product_logs;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

id | product_id  |    product_name    |  price  | brand_id | category_id |         deleted_at
----+------------+--------------------+---------+----------+-------------+----------------------------
  1 |          1 | Samsung Galaxy S24 |  999.99 |        1 |           1 | 2024-12-02 16:57:20.060067
  2 |          2 | Apple iPhone 15    | 1099.99 |        2 |           1 | 2024-12-02 16:57:20.060067
  3 |          3 | Huawei Mate 60     |  899.99 |        3 |           1 | 2024-12-02 16:57:20.060067Code language: plaintext (plaintext)

Summary #

• Use the WITH statement to define a CTE or a temporary table name within a query.
• Use PostgreSQL CTE to simplify complex queries.

Quiz #