Natural Numbers

Library Snippets

Integer to Text

Works with PostgreSQL

any version

Written in

SQL

Depends on

Nothing

It can be useful to have a source of natural numbers to join to e.g. to generate some kind of sequence.

I know two ways to create such a source:

view

create or replace view nats as 
select row_number() over (order by 1) as n
from (select 1 union select 2) t1
join (select 1 union select 2) t2
join (select 1 union select 2) t3
join (select 1 union select 2) t4
join (select 1 union select 2) t5
...

Append more lines as required. The t-number is the power-of-2 number of rows that the view produces e.g. t5 will produce 32 rows (2^5).

function

Using a function as a source of numbers seems to be slightly faster than the view, but more importantly, there's no limit on how many you can generate.

create or replace function nats(numeric) returns setof numeric as
$$
-- return a table of ints from 0 to specified limit.
-- usage:
-- select nats(4096) as n;
declare
    i numeric;
begin
    for i in 0..$1 loop
        return next i;
    end loop;
    return;
end;
$$
language 'plpgsql' immutable strict;

I used the nats function to generate a sequence of date intervals like so:

select
      start_time
    , end_time
    , period_start_ts
    , period_end_ts
from (
    select
          CAST('1970-01-01' AS TIMESTAMP WITHOUT TIME ZONE) + CAST(CAST(period_start_ts AS VARCHAR) || ' s' AS INTERVAL) AS start_time
        , CAST('1970-01-01' AS TIMESTAMP WITHOUT TIME ZONE) + CAST(CAST(period_end_ts   AS VARCHAR) || ' s' AS INTERVAL) AS end_time
        , period_start_ts
        , period_end_ts
    from (
        select
              :start_ts +  n      * :period_len_secs as period_start_ts
            , :start_ts + (n + 1) * :period_len_secs as period_end_ts
        from (select nats(50000) as n) nats
    ) nats
    where period_end_ts <= :end_ts
) dates