CODEWITHSUNDEEP
arrayspostgresql
joncodo
joncodo

Reputation: 2328

postgres insert random number into array column

So I have an array column on a table that has type int. I am load testing for a million rows and need four array elements per record all with different values.

INSERT INTO contacts (numbers)
SELECT '{4443331111, 2223334444, 2223339999, 8887779999}'
  FROM generate_series(1,2) AS x(id);

This creates 2 records but I need the numbers to be unique.

Upvotes: 0

Views: 3188

Answers (2)

IMSoP
IMSoP

Reputation: 97718

What you need is the random() function, multiplied by some suitable number to give your maximum value; but you need to construct an array from four calls to that function.

There is a slightly different syntax for constructing array values which will be easier to work with here, which looks like this:

SELECT ARRAY[4443331111, 2223334444, 2223339999, 8887779999]

Since you don't need to build it up as a string, you can use a function call for the parts of the array, meaning this should work:

SELECT ARRAY[random() * 1000000, random() * 1000000, random() * 1000000, random() * 1000000]
FROM generate_series(1,2) AS x(id);

Since random() returns a floating point number and you want an integer, you need a cast somewhere. The easiest to write is to cast the whole array with a ::int[] at the end, like this:

SELECT ARRAY[random() * 1000000, random() * 1000000, random() * 1000000, random() * 1000000]::int[]
FROM generate_series(1,2) AS x(id);

Postgres Docs: random(); array constructors (including note on the casting shortcut)

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

INSERT INTO contacts (numbers)
SELECT distinct array[
        (random() * 99999999)::integer,
        (random() * 99999999)::integer,
        (random() * 99999999)::integer,
        (random() * 99999999)::integer
    ]
FROM generate_series(1, 1000000) AS x(id);

Notice that the number 8887779999 is out of range for integer. So perhaps you want biginteger.

Upvotes: 3

Related Questions