[cribwi]

If you're running PostgreSQL, you can use the built-in generate_series (1) function like:

  SELECT id, random()
  FROM generate_series(0, 1000000) g (id);

There seems to be an equivalent in SQLite: https://sqlite.org/series.html

[1] https://www.postgresql.org/docs/current/functions-srf.html

[dintech]

Here's how to do something similar in Q (KDB)

    ([]x?x:1000000)

Which gives a table of a million rows like:

    x
    ------
    877095
    265141
    935540
    49015
    ...

[klibertp]

Nothing to do with the article, SQL, or a DB, but I can't help wanting to add even just a bit when I see array languaes mentioned. So, in J it's just:

    ?~1000000

or, if you don't need elements to be unique:

    ?$~1000000

Though it's just an array, not a persistent table - I don't know much about Jd :(

[sradman]

This can generally fall under the category of “Generate Test Data in SQL” with both generic techniques like Recursive CTEs, as in the OP, and SQL dialect or tool specific options. Search is your friend. The OP also provides a table lookup technique for readable names but doesn’t address other data types such as timestamps or other functions such as MD5(). Other data distribution techniques other than random may also be needed. This post scratches the surface of testing with generated data.

[jeffbee]

If you're running PostgreSQL you can also just

  xxd -ps -c 16 -l $bignum < /dev/urandom | (echo '\copy random_data from stdin'; cat) | psql

Depending on how you want your random keys formatted.

[em500]

If you're importing from command line tools, you might as well use the specialized `shuf` tool:

  shuf -i 1-$bignum

or for random numbers with replacement

  shuf -i 1-$bignum -r -n $bignum

These give you a sample of random integers. `shuf` is part of GNU coreutils, so present in most Linux installs (but not on macOS).

[schoetbi]

you have to use it since PostgreSQL does not support limit within a with expression.

[jolmg]

You can use a with-expression. It's true that you can't use `limit` to limit it, but you can use a `where` condition. This is equivalent to cribwi's example:

  WITH RECURSIVE
    t AS (
      SELECT 0 id
      UNION ALL
      SELECT id + 1
        FROM t
        WHERE id < 1000000
    )
    SELECT id, random() FROM t;

It returns 1000001 rows, but so does cribwi's.

[deepstack]

use a sequence or uuid