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.

Answers


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.


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)


Need Your Help

Vertical animated css ul li menu

html css html5 css3 css-menu

I wanted to create a CSS menu on my website that animates to the right when hovered.

Is this the best way to show content?

android post runnable

I'm relatively new in Android's world and the UI/Thread model is still confusing.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.