Postgres provides "WITH" clause which helps to write auxiliary statements for use in larger query.It helps in breaking down complicated and large queries into simpler forms, which are easily readable. These statements, which are often referred to as Common Table Expressions.
WITH clause has two forms; Simple & Recursive.
Simple WITH Clause
Example is mentioned below.
Recursive WITH Clause
Recursive WITH or Hierarchical queries, is a form of Common Table Expressions(CTE) where a CTE can reference to itself, i.e., a WITH query can refer to its own output, hence the name recursive.
Example to generate 1000 Rows.
To populate the date dimension.
WITH clause has two forms; Simple & Recursive.
Example is mentioned below.
- With data_set AS
- (Select
- ID
- , NAME
- , AGE
- , ADDRESS
- , SALARY
- FROM COMPANY )
- Select * from data_set;
Recursive WITH or Hierarchical queries, is a form of Common Table Expressions(CTE) where a CTE can reference to itself, i.e., a WITH query can refer to its own output, hence the name recursive.
- WITH RECURSIVE ROWS100 AS (
- SELECT 1 AS ID
- UNION ALL
- SELECT ID+1 FROM ROWS100
- WHERE ID<100
- ) SELECT * FROM ROWS100
- WITH RECURSIVE time_dim(id) AS (
- select 1 AS ID, 0.000694444444444444444444444444444444444444 as interval_period, date_trunc('Day',current_timestamp) + interval '0.000694444444444444444444444444444444444444 days'as date
- UNION ALL
- SELECT id+1 , (id+1)*interval_period, date_trunc('Day',current_timestamp) + interval '0.000694444444444444444444444444444444444444 days'*(id+1) as date
- from time_dim
- WHERE id < 1440
- )
- select id, date, lpad(extract('hour' from date)::varchar(2),2,'0') as hour , lpad(extract('minute' from date)::varchar(2),2,'0') as minute,to_char(date,'HH') TWLVE,
- TO_CHAR(date,'AM') am_pm
- from time_dim
Another example specified below will give the sum of the salaries < 20000
- WITH RECURSIVE t(n) AS (
- VALUES (0)
- UNION ALL
- SELECT SALARY FROM COMPANY WHERE SALARY < 20000
- )
- SELECT sum(n) FROM t;
Example for will delete the data from a set the deleted records are inserted into another table using WITH Clause
- 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);
