How to create a 1M record table with a single query

Re:How to create a 1M record table with a single query by schoetbi on Wednesday March 24, 2021 @08:39AM (#26566377)

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

[ Parent | on HN ]

Re:How to create a 1M record table with a single query by jolmg on Wednesday March 24, 2021 @06:33PM (#26573880)

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.

[ Parent | on HN ]