If you’re getting an error that reads “invalid input syntax for type integer…” when using the array_sample() function in PostgreSQL, it’s probably because your second argument is not an integer.

The second argument for this function must be an integer.

To fix this error, be sure that your second argument to array_sample() is an integer.

Example of Error

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

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

Result:

ERROR:  invalid input syntax for type integer: "Two"
LINE 1: ...array_sample( ARRAY[ 'Cat', 'Dog', 'Bird', 'Fox' ], 'Two' );
                                                               ^

Here the culprit is 'Two'. That’s not an integer.

Solution

We can fix the problem by ensuring that the second argument is an integer:

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

Result:

 array_sample 
--------------
 {Cat,Bird}

This time the function worked as expected, without error.