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 the 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 serial NOT NULL,
       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 that 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;




NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...