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.

From Data Type To Data Type(s)
VARCHAR All types
CHAR All types
SMALLINT INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, VARCHAR, CHAR
INTEGER SMALLINT, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, VARCHAR, CHAR
BIGINT SMALLINT, INTEGER, DECIMAL, REAL, DOUBLE PRECISION, VARCHAR, CHAR
DECIMAL SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, VARCHAR, CHAR
REAL SMALLINT, INTEGER, BIGINT, DOUBLE PRECISION, VARCHAR, CHAR
DOUBLE PRECISION SMALLINT, INTEGER, BIGINT, REAL, VARCHAR, CHAR
DATE TIMESTAMP, TIMESTAMPTZ, VARCHAR, CHAR
INTERVAL TIME, VARCHAR, CHAR
TIME INTERVAL, VARCHAR, CHAR
TIMESTAMP DATE, TIMESTAMPTZ, TIME, VARCHAR, CHAR
TIMESTAMPTZ DATE, TIMESTAMP, TIME, VARCHAR, CHAR
BOOLEAN VARCHAR, CHAR
UUID VARCHAR, CHAR
IPV4 IPV6, VARCHAR, CHAR
IPV6 VARCHAR, CHAR
MACADDR MACADDR8, VARCHAR, CHAR
MACADDR8 VARCHAR, CHAR
UNKNOWN (special Postgres type) All types

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 returns an error.

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 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.

  • TIMESTAMPTZ
  • TIMESTAMP
  • DATE
  • INTERVAL
  • TIME
  • DOUBLE PRECISION (FLOAT8)
  • REAL (FLOAT4)
  • DECIMAL (NUMERIC)
  • BIGINT
  • INT
  • SMALLINT
  • BOOLEAN
  • UUID
  • IPV6
  • IPV4
  • MACADDR8
  • MACADDR
  • VARCHAR
  • CHAR
  • 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:

Expression Rule Details
AND/OR Cast all arguments to boolean.
NOT <expr> Attempt to coerce <expr> to boolean.
CASE expressions The resulting output is the highest type of each branch (including the ELSE part of the expression). All other branches are implicitly cast to that type. Given this form:
CASE <expr> WHEN <expr0> then <expr0'> ... WHEN <exprN> then <exprN'>

all <expr0>...<exprN> and <expr> parts are cast to the highest type.

DECODE Apply the same rule as for CASE.
COALESCE(<expr0>, <expr1>, ... <exprN>)
Coerce all expressions to the highest of <expr0>...<exprN>. The result is of the same type as the highest.
GREATEST, LEAST Apply the same rule as for COALESCE.
NVL(<bool>, <expr0>) Return the type of <expr0>.
NVL2(<bool>, <expr0>, <expr1>) Return the highest type of <expr0> and <expr1>.
SELECT <expr0> AS x
UNION / EXCEPT / INTERSECT
...
SELECT <exprN>
Coerce x to the highest type for <expr0>...<exprN>.

All other types are implicitly cast to that type. If an implicit cast is unavailable, the query returns an error.

+, -, /, *, % When one side of an operator is a string type and the other is numeric, implicitly cast the string to the numeric type. (Even when a string is added to an integer, en error can still result if the string representation of the integer overflows the target integer type.)
When one side of an operator is a datetime type and the other is not:
  • If the non-datetime type can be cast to the same type as the datetime type, that type is used.
  • Otherwise, the highest available cast in the datetime hierarchy is used.

See also Implicit Casting Examples.

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))
...