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;
|
