typeconv-query

Value storage type conversion

1. Check for an exact match with the target type.

2. If no exact match exists, try to convert the expression to the target type. This succeeds if an assignment cast between the two types is registered in the pg_cast catalog (see CREATE CAST). Alternatively, if the expression is an unknown-type literal, its contents are passed to the input conversion routine for the target type.

3. Check for a sizing cast — a cast from that type to itself — in the pg_cast catalog. If one exists, apply it to the expression before storing it in the destination column. The implementation function for a sizing cast always takes an extra integer parameter that receives the column's atttypmod value (typically its declared length, though the interpretation of atttypmod varies by data type). It may also take a third boolean parameter indicating whether the cast is explicit or implicit. The cast function is responsible for all length-dependent semantics, such as size checking and truncation.

Example 1. character storage type conversion

For a target column declared as character(20), the following statements show that the stored value is sized correctly:

CREATE TABLE vv (v character(20));
INSERT INTO vv SELECT 'abc' || 'def';
SELECT v, octet_length(v) FROM vv;

Result:

          v           | octet_length
----------------------+--------------
 abcdef               |           20
(1 row)

Here is what happens internally:

1. The two unknown literals 'abc' and 'def' are resolved to text by default, which allows the || operator to be resolved as text concatenation.

2. The text result is then converted to bpchar (blank-padded char — the internal name of the character data type) to match the target column type. Because the conversion from text to bpchar is binary-coercible, no real function call is inserted.

3. Finally, the sizing function bpchar(bpchar, integer, boolean) is looked up in the system catalog and applied to the operator's result and the stored column length. This type-specific function performs the required length check and adds the necessary padding spaces.