WITH Clause Tips in Postgresql

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. 

1. With data_set AS  
2. (Select  
3. ID  
4. , NAME  
5. , AGE  
6. , ADDRESS  
7. , SALARY  
8. FROM COMPANY )  
9.   
10. Select * from data_set; 

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.

view plaincopy to clipboardprint?

1. WITH RECURSIVE ROWS100 AS (  
2. SELECT 1  AS ID  
3. UNION ALL  
4. SELECT ID+1 FROM ROWS100  
5. WHERE ID<100  
6. ) SELECT * FROM ROWS100  

To populate the date dimension.

1. WITH RECURSIVE time_dim(id) AS (  
2.                 select 1 AS ID, 0.000694444444444444444444444444444444444444 as interval_period, date_trunc('Day',current_timestamp) + interval '0.000694444444444444444444444444444444444444 days'as date  
3.                 UNION ALL   
4.                 SELECT id+1 , (id+1)*interval_period, date_trunc('Day',current_timestamp) + interval '0.000694444444444444444444444444444444444444 days'*(id+1) as date  
5. from time_dim  
6.                 WHERE id < 1440  
7. )  
8. 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,  
9. TO_CHAR(date,'AM') am_pm  
10. from time_dim  

Another example specified below will give the sum of the salaries < 20000

view plaincopy to clipboardprint?

1. WITH RECURSIVE t(n) AS (  
2.     VALUES (0)  
3.     UNION ALL  
4.     SELECT SALARY FROM COMPANY WHERE SALARY < 20000  
5. )  
6. 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

view plaincopy to clipboardprint?

1. CREATE TABLE COMPANY1(  
2.    ID INT PRIMARY KEY     NOT NULL,  
3.    NAME           TEXT    NOT NULL,  
4.    AGE            INT     NOT NULL,  
5.    ADDRESS        CHAR(50),  
6.    SALARY         REAL  
7. );  
8.   
9. WITH moved_rows AS (  
10.     DELETE FROM COMPANY  
11.     WHERE  
12.         SALARY >= 30000  
13.     RETURNING *  
14. )  
15. INSERT INTO COMPANY1 (SELECT * FROM moved_rows);