Values to be inserted into a table are converted to the destination column's data type according to the following steps.
Value Storage Type Conversion
Check for an exact match with the target.
Otherwise, try to convert the expression to the target type. This will succeed if there is a registered cast between the two types. If the expression is an unknown-type literal, the contents of the literal string will be fed to the input conversion routine for the target type.
Check to see if there is a sizing cast for the target type. A sizing
cast is a cast from that type to itself. If one is found in the
pg_cast
catalog, apply it to the expression before storing
into the destination column. The implementation function for such a cast
always takes an extra parameter of type integer
, which receives
the destination column's declared length (actually, its
atttypmod
value; the interpretation of
atttypmod
varies for different data types). The cast function
is responsible for applying any length-dependent semantics such as size
checking or truncation.
Example 10.6. character
Storage Type Conversion
For a target column declared as character(20)
the following statement
ensures that the stored value is sized correctly:
CREATE TABLE vv (v character(20)); INSERT INTO vv SELECT 'abc' || 'def'; SELECT v, length(v) FROM vv; v | length ----------------------+-------- abcdef | 20 (1 row)
What has really happened here is that the two unknown literals are resolved
to text
by default, allowing the ||
operator
to be resolved as text
concatenation. Then the text
result of the operator is converted to bpchar
(“blank-padded
char”, the internal name of the character
data type) to match the target
column type. (Since the types text
and
bpchar
are binary-compatible, this conversion does
not insert any real function call.) Finally, the sizing function
bpchar(bpchar, integer)
is found 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 addition of
padding spaces.