How to create a sequence of numbers in PostgreSQL? [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

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

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