If you’re getting an error that starts with “sample size must be between…” when using the array_sample() function in PostgreSQL, it’s probably because your second argument is either a negative value, or it’s larger than the number of elements in the array.

The second argument must be no less than zero, and it can’t be any larger than the number of elements in the array.

To fix this error, be sure that your second argument is greater than zero and less than or equal to the number of elements in the array at the first argument.

Example of Error

Here’s an example of code that produces the error:

SELECT array_sample( ARRAY[ 'Cat', 'Dog', 'Bird', 'Fox' ], 6 );

Result:

ERROR:  sample size must be between 0 and 4

Here the error message tells me that the sample size must be between zero and four. I got this error message because I passed 6 as the second argument when there are only four elements in the array.

We get the same error when passing a negative value for the sample size:

SELECT array_sample( ARRAY[ 'Cat', 'Dog', 'Bird', 'Fox' ], -1 );

Result:

ERROR:  sample size must be between 0 and 4

The upper number will depend on how many elements are in the array. For example if there are ten elements, then the error will read “sample size must be between 0 and 10“.

Solution

We can fix the problem by ensuring that the second argument is greater than zero and less than or equal to the number of elements in the array at the first argument.

Example:

SELECT array_sample( ARRAY[ 'Cat', 'Dog', 'Bird', 'Fox' ], 2 );

Result:

 array_sample 
--------------
 {Dog,Fox}

This time the function worked without error.