primes Table Function

primes() – Returns an infinite table with a single prime column (UInt64) that contains prime numbers in ascending order, starting from 2. Use LIMIT (and optionally OFFSET) to restrict the number of rows.
primes(N) – Returns a table with the single prime column (UInt64) that contains the first N prime numbers, starting from 2.
primes(N, M) – Returns a table with the single prime column (UInt64) that contains M prime numbers starting at the N-th prime (0-based).
primes(N, M, S) – Returns a table with the single prime column (UInt64) that contains M prime numbers starting from the N-th prime (0-based) with step S by prime index. The returned primes correspond to indices N, N + S, N + 2S, ..., N + (M - 1)S. S must be ≥ 1.

This is similar to the system.primes system table.

The following queries are equivalent:

SELECT * FROM primes(10);
SELECT * FROM primes(0, 10);
SELECT * FROM primes() LIMIT 10;
SELECT * FROM system.primes LIMIT 10;
SELECT * FROM system.primes WHERE prime IN (2, 3, 5, 7, 11, 13, 17, 19, 23, 29);

The following queries are also equivalent:

SELECT * FROM primes(10, 10);
SELECT * FROM primes() LIMIT 10 OFFSET 10;
SELECT * FROM system.primes LIMIT 10 OFFSET 10;

Examples

The first 10 primes.

SELECT * FROM primes(10);

  ┌─prime─┐
  │     2 │
  │     3 │
  │     5 │
  │     7 │
  │    11 │
  │    13 │
  │    17 │
  │    19 │
  │    23 │
  │    29 │
  └───────┘

The first prime greater than 1e15.

SELECT prime FROM primes() WHERE prime > toUInt64(1e15) LIMIT 1;

  ┌────────────prime─┐
  │ 1000000000000037 │ -- 1.00 quadrillion
  └──────────────────┘

The first 7 Mersenne primes.

SELECT prime
FROM primes()
WHERE bitAnd(prime, prime + 1) = 0
LIMIT 7;

  ┌──prime─┐
  │      3 │
  │      7 │
  │     31 │
  │    127 │
  │   8191 │
  │ 131071 │
  │ 524287 │
  └────────┘

Notes

The fastest forms are the plain range and point-filter queries that use the default step (1), for example, primes(N) or primes() LIMIT N. These forms use an optimized prime generator to compute very large primes efficiently. For example, the following query executes almost instantly:

SELECT sum(prime)
FROM primes()
WHERE prime BETWEEN toUInt64(1e6) AND toUInt64(1e6) + 100
   OR prime BETWEEN toUInt64(1e12) AND toUInt64(1e12) + 100
   OR prime BETWEEN toUInt64(1e15) AND toUInt64(1e15) + 100
   OR prime IN (9999999967, 9999999971, 9999999973)
   OR prime = 1000000000000037;

  ┌───────sum(prime)─┐
  │ 2004010006000641 │ -- 2.00 quadrillion
  └──────────────────┘

1 row in set. Elapsed: 0.090 sec.

Using a non-zero offset and/or step greater than 1 (primes(offset, count) / primes(offset, count, step)) may be slower because additional primes may need to be generated and skipped internally. If you don't need an offset or step, omit them.

Examples​

Notes​

Examples

Notes