# Natural Numbers

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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```