Coercion Notes

This section describes the Yellowbrick data types in terms of their compatibility and ability to be coerced. Expressions in queries often result in implicit conversions, where a given data type is automatically coerced to another compatible type. One-to-one matching of data values and data types is not necessary; if data types are compatible, implicit conversion occurs.
In many cases, this behavior prevents queries and DML operations from returning errors. To cover some of the cases where implicit conversion is not supported, you can convert data types in expressions explicitly, as described in Type Casting.

Data Type Compatibility

In general, data types fall into compatible categories:
  • String or character types: VARCHAR, CHAR
  • Numeric types: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL (FLOAT4), DOUBLE PRECISION (FLOAT8)
  • Date/time types: DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL

    Note that INTERVAL is not supported as a stored column type.

  • BOOLEAN
  • UUID

Implicit Conversions

The following table lists data types that are converted implicitly. In most cases when implicit conversions are not possible, you will see the following HINT as part of the error message:
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Data Type Category From Data Type To Data Type(s)
Character string VARCHAR CHAR
CHAR VARCHAR
Numeric SMALLINT All other numeric types (for upcasting). DECIMAL values are upcast to DOUBLE PRECISION.
INTEGER
BIGINT
DECIMAL
REAL
DOUBLE PRECISION
Date/time DATE TIMESTAMP, TIMESTAMPTZ. DATE values are upcast to TIMESTAMPTZ values.
INTERVAL TIME
TIME INTERVAL
TIMESTAMP DATE, TIMESTAMPTZ. TIMESTAMP values are upcast to TIMESTAMPTZ values.
TIMESTAMPTZ DATE, TIMESTAMP
Boolean BOOLEAN No implicit conversion
UUID UUID No implicit conversion

Implicit casting of Boolean values to integers or integers to Boolean values is not supported. However, the values of 0 and 1 are comparable with false and true for Boolean columns.

By default, character strings are not cast down to fit into smaller CHAR or VARCHAR types. For example, attempting to insert a 3-character string into a 2-character column now returns an error. To allow down-casting and truncation of strings, set the enable_silent_coerce parameter to ON; the default is OFF:
premdb=# show enable_silent_coerce;
 enable_silent_coerce 
----------------------
 off
(1 row)