PG_INPUT_IS_VALID

Return true if the input string can be cast to the specified data type without error.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

PG_INPUT_IS_VALID(str, type_name)

Description

## Overview Returns TRUE when the input string can be cast to the requested PG-compat type without raising an error, and FALSE otherwise. Use this function for dynamic validation when the target type is known only at runtime (for example, driven by a schema probe table) and the type-specific IS_* helpers do not cover it. ## Behavior - Returns NULL if either argument is NULL. - Recognizes the common PG-compat type names: `integer`, `bigint`, `smallint`, `numeric`, `float`, `double precision`, `boolean`, `date`, `time`, `timestamp`, `uuid`, `text`, `json`, and `jsonb`. - Unknown or unsupported type names raise an error rather than returning FALSE. - Deterministic and side effect free. ## Compatibility - PG-compat alias for `pg_input_is_valid` with the same semantics.

Parameters

Name	Type	Description
`str`		Specifies the string value to validate against the target data type.
`type_name`		Specifies the name of the target data type (for example, 'integer', 'date', 'boolean', 'uuid').

Examples

-- Valid integer input
SELECT PG_INPUT_IS_VALID('42', 'integer');  -- true

-- Invalid integer input
SELECT PG_INPUT_IS_VALID('abc', 'integer');  -- false

-- Valid date
SELECT PG_INPUT_IS_VALID('2025-12-31', 'date');  -- true

-- Valid boolean
SELECT PG_INPUT_IS_VALID('true', 'boolean');  -- true

-- Calendar-invalid date
SELECT PG_INPUT_IS_VALID('2025-13-01', 'date');  -- false

-- Dynamic validation across a schema-probe column
SELECT col, val, PG_INPUT_IS_VALID(val, expected_type) AS ok
FROM stage.raw.schema_probe;

Pitfalls

An unknown type name raises an error. Validate the type name itself against a known whitelist before passing it to PG_INPUT_IS_VALID.
The function tells you whether the cast would succeed, not whether the resulting value is meaningful. A valid date may still be outside your business-specific acceptable range.
Type names must match the PG-compat canonical spelling (for example, `double precision`, not `double`). Normalize input at the call site.
For simple checks on common types, the dedicated IS_INTEGER / IS_NUMERIC / IS_DATE / IS_UUID functions are more efficient and avoid the string-type argument.