PostgreSQL stuff

Mostly about databases, programming, system administration and application deployment.

Fibonacci sequence using WITH RECURSIVE

-- Fibonacci sequence
-- Example to demo WITH RECURSIVE in PostgreSQL 8.4
with recursive f as (
    select 0 as a, 1 as b
    union all
    select b as a, a+b from f where a < 100
) select a from f;

  a 
-----
   0
   1
   1
   2
   3
   5
   8
  13
  21
  34
  55
  89
 144
(13 rows)

The blog (http://planetpostgresql.org/, not http://planet.postgresql.org/) contains an error: change "where a 100" to "where a < 100".

Regards, Andreas

@Andreas: Thanks for noticing. I'm afraid it's out of my reach though since both planets use the same RSS feed and it's properly encoded.

And it's fun to play with to overflow numeric types...

with f as (
select cast(0 as float) as a, cast(1 as float) as b
union all
select b as a, a+b from f
) select a, b from f;

@Ian: hehe, yeah:

robertg=# with recursive f as (
select cast(0 as float) as a, cast(1 as float) as b
union all
select b as a, a+b from f
) select a, b from f;
ERROR: value out of range: overflow
robertg=#