Implicit Type Conversion

Value Storage Type Resolution

1. Search for an exact match with the target column.
2. Try to convert the expression to the target type. If a registered conversion function exists between the two types, call the conversion function directly. If the expression is an unknown-type literal, the content of the literal string will be fed to the input conversion routine for the target type.
3. Check whether there is a sizing cast for the target type. A sizing cast is a cast from that type to itself. If the target type has sizing cast logic, 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 atttypmod value (typically its declared length, although the interpretation of atttypmod varies for different data types), and it may take a third Boolean parameter that says whether the cast is explicit or implicit. The cast function is responsible for applying any length-dependent semantics such as size checking or truncation.

Example:

Convert the CHAR storage type. For a target column declared as CHAR(20), the following statement shows that the stored value is sized correctly:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

m_db=# CREATE TABLE char_storage (
    VS_COL1 CHAR(20)
);
m_db=# INSERT INTO char_storage VALUES('abcdef');
m_db=# SELECT VS_COL1, length(VS_COL1) FROM char_storage;
 VS_COL1 | length 
---------+--------
 abcdef  |      6
(1 row)

m_db=# DROP TABLE char_storage;

The input column is resolved as the UNKNOWN type. Then, the input function of the CHAR type is found in the system catalog based on the target column. Finally, the sizing function bpchar(bpchar, integer, bool) is found in the system catalog and used for the result and the stored column length. This type-specific function performs the required length check and addition of padding spaces.

Convert the FLOAT storage type to the BINARY storage type. Insert the value obtained from the FLOAT column to the table whose target column is BINARY(5).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

m_db=# CREATE TABLE varchar_storage (
    VS_COL1 BINARY(5)
);
m_db=# CREATE TABLE float_storage (
    VS_COL1 FLOAT
);
m_db=# INSERT INTO float_storage VALUES(1234567);
m_db=# INSERT INTO varchar_storage SELECT * FROM float_storage ;
INSERT 0 1
m_db=# SELECT * FROM varchar_storage;
 VS_COL1 
---------
 1.2e6
(1 row)
m_db=# DROP TABLE varchar_storage, float_storage;

When data is inserted from the FLOAT column to the BINARY column, the implicit conversion rule from the FLOAT type to the BINARY type in the system catalog is searched and the corresponding type conversion function is called. Finally, the sizing function binary(bpchar, integer, bool) is found in the system catalog and used for the result and the stored column length. This type-specific function performs the required length check and addition of padding spaces.