Skip to main content

PostgreSQL- Common Table Expression

Common Table Expression(CTEs) is a handy feature in PostgreSQL, it is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. It provides a convenient way to manage the completed queries. CTEs are defined within the PostgreSQL statement using the WITH clause. It resolves many difficulties we face in PostgreSQL query statement such as removes the complexity of sub-query, which executed multiple times, equally helpful in place of temporary tables.

In this article, we will learn the uses of Common Table Expression based on the scenario. The CTEs start with WITH clause and followed by the expression name. Further we use the expression name in a select query to obtain the records of Common Table Expression.

Syntax of WITH clause in PostgreSQL


WITH CTE_Name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

 For example, suppose we have the Employee table that contains employee details with managerid.

CREATE TABLE public.employee
(
    id integer NOT NULL DEFAULT nextval('employee_id_seq'::regclass),
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id)
)

 Inserts some records into the employee table, run the following query.

INSERT INTO public.Employee(
                FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
                VALUES ('Name1','last1', 'name1@gmail.com', 'name@0123', '9088877787', '20000','2018/07/07',0);

INSERT INTO public.Employee(
                FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
                VALUES ('Name2','last2', 'name2@gmail.com', 'name@0123', '9288877787', '30000','2018/07/07',3);
             
INSERT INTO public.Employee(
                FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
                VALUES ('Name3','last3', 'name3@gmail.com', 'name@0123', '9388877787', '40000','2017/09/08',1);

INSERT INTO public.Employee(
                FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
                VALUES ('Name4','last4', 'name4@gmail.com', 'name@0123', '9344877787', '70000','2018/06/09',2);


Common Table Expression without recursive, Suppose I want to get all those records which have Salary greater than 20000.

Simple CTE Example:


WITH CTE(EmpID, FirstName, LastName, MgrID, Salary,YearOfJoining) as (
                SELECT Id,FName,LName,ManagerID,Salary,date_part('year', doj)
FROM public.Employee 
WHERE Salary>20000
                 )
 SELECT EmpID, FirstName, LastName, MgrID, Salary,YearOfJoining FROM CTE;


Result

Recursive CTE Example

From the Employee table, I want to get the records of the employee and manager, also want to show the level of the hierarchy.

WITH RECURSIVE CTE(EmpID, FirstName, LastName, MgrID, EmpLevel) as (
                SELECT Id,FName,LName,ManagerID,1 FROM public.Employee  WHERE ManagerID=0
                UNION ALL
                SELECT e.Id, e.FName,e.LName, e.ManagerID, r.EmpLevel + 1
    FROM public.Employee e
      INNER JOIN CTE r
        ON e.ManagerID = r.EmpID
)
SELECT EmpID, FirstName, LastName, MgrID, EmpLevel FROM CTE;


Result

Another example of recursive Common Table Expression

WITH RECURSIVE cte (i) AS (
    SELECT 10
  UNION ALL
    SELECT i+10 FROM cte WHERE i+10<= 100
)
SELECT i FROM cte;



Result:

INSERT records using WITH clause

We can modify the records using CTE Statements (Data-Modifying Statements in WITH), PostgreSQL WITH clause allows us to perform several different operations in the same query.
Suppose you want to delete some records from the Employee table and want to log the deleted data in Employee_Log table then use the following query.

WITH deleted_rows AS (
    DELETE FROM employee
    WHERE
        username = 'name4@gmail.com'
    RETURNING *
)
INSERT INTO employee_log
SELECT * FROM deleted_rows;

Update records using with clause

Suppose you want to increase the salary of 10% of your employee, for example, see the following query.

WITH upd_emp AS (
    UPDATE employee SET Salary = Salary*1.1
    RETURNING *
)
SELECT * FROM upd_emp;