Product Documentation

Appearance

All platforms

enable_empty_string_cast_as_zero (boolean) ​

  • Default value: off

When this parameter is on, casting an empty or whitespace-only string (VARCHAR or CHAR) to an integer type (SMALLINT, INTEGER, BIGINT) or a DECIMAL/NUMERIC type during query execution yields 0 instead of failing with the error VARCHAR '' cannot be converted to <TYPE>. This restores the behavior of Yellowbrick releases before 7.1, and is useful for ETL workloads (for example, Informatica-generated queries) that rely on the older behavior. The parameter was introduced in version 7.4.5.

The leniency applies only to empty or whitespace-only input:

  • NULL input still produces NULL (not 0).
  • Invalid non-empty strings such as 'abc' still produce an error, exactly as when the parameter is off.
  • Casts to REAL (FLOAT4) and DOUBLE PRECISION (FLOAT8) are not affected and still produce an error for empty input, matching pre-7.1 releases.
  • Literal casts evaluated ahead of query execution, such as SELECT ''::DECIMAL, are not affected and still produce an error, matching pre-7.1 releases.

For example:

txt
create table t (v varchar(10));
insert into t values ('');

select v::int from t;
ERROR:  VARCHAR '' cannot be converted to INT32

set enable_empty_string_cast_as_zero to on;

select v::int from t;
 v
---
 0
(1 row)

The recommended deployment is to enable the parameter only for the roles that need it:

sql
ALTER ROLE etl_role SET enable_empty_string_cast_as_zero = on;

Alternatively, it can be set per session with SET or system-wide with ALTER SYSTEM SET. Changes take effect without a restart.