In PostgreSQL, the pg_input_is_valid() function tests whether the given string is valid input for the specified data type.

It returns a boolean value of either true or false.

This function only works if the data type’s input function has been updated to report invalid input as a “soft” error. 

The pg_input_is_valid() function was introduced in PostgreSQL 16, which was released on September 14th 2023.

Syntax

The syntax goes like this:

pg_input_is_valid ( string text, type text ) 

The function returns its result as a boolean.

Example

Here’s an example to demonstrate:

SELECT pg_input_is_valid('1.5', 'integer');

Result:

 pg_input_is_valid 
-------------------
 f

Here, my first argument was 1.5, and that was validated against my second argument (integer). An integer can’t have a decimal point and so we get f for false, which means the value is not valid for that type.

Here’s one that returns true:

SELECT pg_input_is_valid('1', 'integer');

Result:

 pg_input_is_valid 
-------------------
 t

The value of 1 is indeed an integer, and so it’s valid for the integer type. Therefore we get t for true.

Passing the Wrong Argument Type

Passing an argument of the wrong type results in an error:

SELECT pg_input_is_valid(1, 'integer');

Result:

ERROR:  function pg_input_is_valid(integer, unknown) does not exist
LINE 1: SELECT pg_input_is_valid(1, 'integer');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Passing a Null Argument

If any of the arguments are null, then null is returned:

SELECT 
    pg_input_is_valid(null, 'integer'),
    pg_input_is_valid('1', null),
    pg_input_is_valid(null, null);

Result:

 pg_input_is_valid | pg_input_is_valid | pg_input_is_valid 
-------------------+-------------------+-------------------
 null              | null              | null

Checking the Error Message

PostgreSQL also has a pg_input_error_info() function that allows us to see the error details that would be returned if we were to pass an invalid value for the specified data type. This includes the error message, the sql error code, the details (if available), and the hint (if available).