Implicit Casting ​

This section describes the Yellowbrick data types in terms of their ability to be coerced. Expressions in queries often result in implicit casts, 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.

To cover certain cases where implicit conversion is not supported, you can convert data types in expressions explicitly, as described in Explicit Casting.

Supported Implicit Casts ​

The following table lists data types that are cast implicitly.

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.

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.

Down-casting Character Strings ​

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 returns an error. Down-casting and truncation of strings can be allowed by setting the enable_silent_coerce configuration parameter.

Data Type Hierarchy ​

When data types are cast implicitly, they are cast "up" or "down" according to the following hierarchy.

For example, TIMESTAMPTZ has precedence over the other datetime types. In turn, a comparison of a TIMESTAMP value and a TIMESTAMPTZ value produces a TIMESTAMPTZ result.

For character strings, VARCHAR has precedence over CHAR, and CHAR has precedence over UNKNOWN.

• TIMESTAMPTZ
• TIMESTAMP
• DATE
• INTERVAL
• TIME
• DOUBLE PRECISION (FLOAT8)
• REAL (FLOAT4)
• DECIMAL (NUMERIC)
• BIGINT
• INT
• SMALLINT
• BOOLEAN
• UUID
• IPV6
• IPV4
• MACADDR8
• MACADDR
• JSONB
• JSON
• VARCHAR
• CHAR
• BYTEA
• UNKNOWN

Note: UNKNOWN is a special PostgreSQL type that is used for single-quoted strings and NULL values.

Data Types for Expression Results ​

When different types are compared or participate in expressions, including comparisons in join conditions, filters, sorts, and GROUP BY expressions, the following general typing rules apply:

• All data types are implicitly cast to the type that is highest in the hierarchy for the arguments involved.
• If no implicit cast from a lower type to a higher type is available, an error occurs.
• If multiple, equally valid, casts are available and the first rule does not apply, an error occurs. For example, SUM(NULL) returns an error because the data type of NULL cannot be inferred, and TO_CHAR(string, string) returns an error because there are multiple, equally valid, casts for the first argument.

The following table provides more details for certain operators, functions, and expressions:

See also Implicit Casting Examples.

Casting Strings with UNKNOWN Literals ​

When a common type must be found between two strings, and one or both of the strings is UNKNOWN, note the following behavior:

• UNKNOWN + UNKNOWN results in a VARCHAR. For example:

'abc  ' || 'zzz  ' -> 'abc  zzz  '

• UNKNOWN + CHAR results in a CHAR. For example:

'abc  '::char(10) || 'zzz   '  -> 'abczzz'

• UNKNOWN + VARCHAR results in a VARCHAR. For example:

'abc  '::varchar(10) || 'zzz   ' -> 'abc   zzz'

• VARCHAR + UNKNOWN + CHAR results in a VARCHAR. For example:

'abc  '::varchar(10) || 'zzz   ' || '+++   ':char(10)  -> 'abc   zzz   +++'

These examples show the || operator, but the same behavior applies to other operators, functions, and expressions where a common type must be found, such as set operators, CASE expressions, and simple > or < less than comparisons.

Assignment of Inserted and Updated Values ​

Assignment operations occur during the execution of statements such as INSERT, UPDATE, and FETCH, and when values are assigned to variables in stored procedures. These operations are treated differently from expression evaluation.

In these cases, both implicit and explicit casts should be emitted automatically. Given that the left side of the assignment has a known type, the other side must be cast, and there is only one candidate for the cast that can be chosen. For example, consider the following table:

premdb=# create table assign (a numeric(10,2), b float);
CREATE TABLE
premdb=# \d assign
        Table "public.assign"
 Column |       Type       | Modifiers 
--------+------------------+-----------
 a      | numeric(10,2)    | 
 b      | double precision | 

Distribution: Hash (a)

premdb=# insert into assign values (1.2::float, 1.2::numeric(10,2));
INSERT 0 1  
premdb=# update assign set a = b;
UPDATE 1

Note how the INSERT and UPDATE statements are executed. The inserted values are ultimately cast to the types defined for the columns, not the types defined in the INSERT statement.

premdb=# explain verbose insert into assign values (1.2::float, 1.2::numeric(10,2));
                                        QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Insert on public.assign (on manager)  (output dist=None)  (cost=0.00..0.00 rows=1 width=0)
   ->  Result (on manager)  (output dist=None)  (cost=0.00..0.00 rows=1 width=0)
        Output: 1.20::numeric(10,2), '1.2'::double precision
(3 rows)

In the UPDATE statement, column a is set by casting column b to NUMERIC(10,2), which is the stored data type of column a.

premdb=# explain verbose update assign set a = b;
                                                          QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 id   rows_planned   mem_planned   mem_actual   workers   node   
  2            100      3.00Gi H         0.00       all   INSERT INTO assign   
                                                           (assign.b::NUMERIC(10, 2), assign.b, assign.rowunique, assign.rowid)
                                                           distribute on (assign.b::NUMERIC(10, 2))
...