How to create a sequence of numbers in PostgreSQL? [Resolved]

Posted by Crniranjanraj under PostgreSQL on 10/3/2015 | Points: 10 | Views : 553 | Status : [Member] | Replies : 3
I would like to know how can I create a sequence of numbers in PostgreSQL?In Sql Server I can do that by using a while loop.But how in PostgreSQL.I am new to it.




Responses

Posted by: Rajnilari2015 on: 10/4/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
You can use (for better performance) a recursive CTE as under

With Recursive CTE(Rn) AS
(
Select 1
Union All
Select Rn + 1 From CTE
Where Rn < 10
)
Select * From CTE;


Will generate numbers from 1 to 10.

Other alternatives

Using Union All

SELECT 1 Number UNION ALL
SELECT 2 Number UNION ALL
SELECT 3 Number UNION ALL
SELECT 4 Number UNION ALL
SELECT 5 Number UNION ALL
SELECT 6 Number UNION ALL
SELECT 7 Number UNION ALL
SELECT 8 Number UNION ALL
SELECT 9 Number UNION ALL
SELECT 10 Number

For Loop
FOR i IN 1..10 LOOP
-- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;


While Loop
DECLARE
iterator int := 1;
BEGIN
WHILE iterator <= 10
LOOP
iterator := iterator + 1;
-- do stuff
END LOOP;
END;


Hope this helps

--
Thanks & Regards,
RNA Team

Crniranjanraj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 10/5/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
Common Table Expression(CTE) is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables.It computes the aggregation once, and allows us to reference it by its name (may be multiple times) in the queries.It materialize subqueries thereby helping oracle not to recompute them multiple times.In PostgreSQL, we use the WITH clause for writing CTE based queries.It helps in breaking down complicated and large queries into simpler forms which is easily readable.

More information about the article, please read: http://beyondrelational.com/modules/2/blogs/80/posts/10748/day-6-common-table-expressions-using-with-clause-in-postgresql.aspx

--
Thanks & Regards,
RNA Team

Crniranjanraj, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Crniranjanraj on: 10/4/2015 [Member] Starter | Points: 25

Up
0
Down
Oh nice... so there are multiple ways.I have tried all from your answer but didn't understand what is recursive CTE- please explain

Crniranjanraj, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response