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.

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)

Parent topic:SQL Data Types