Value Storage

Value Storage Type Resolution

1. Search for an exact match with the target column.
2. 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 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 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. The parameter receives the destination column's atttypmod value (typically its declared length, although the interpretation of atttypmod varies for different data types), and 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.

Examples

Character storage type conversion.

This example demonstrates the processing logic when inserting data into a column of type character(20). Although the length of the inserted string is less than the defined length, the query result shows that the storage length is 20 bytes, indicating that the system automatically pads with blanks.

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

CREATE TABLE x1
(
    customer_sk             integer,
    customer_id             char(20),
    first_name              char(6),
    last_name               char(8)
)
with (orientation = column,compression=middle)
distribute by hash (last_name);

INSERT INTO x1(customer_sk, customer_id, first_name) VALUES (3769, 'abcdef', 'Grace');

SELECT customer_id, octet_length(customer_id) FROM x1;
     customer_id      | octet_length 
----------------------+--------------
 abcdef               |           20
(1 row)

The character storage type conversion process is as follows:

1. Two unknown texts are parsed by the system as text by default, allowing the || operator to be resolved as text concatenation.
2. The text result of the operator is converted to bpchar ("blank-padded character", the internal name of the character type) to match the target column type. The conversion from text to bpchar is binary-compatible. This conversion process is implicit and does not actually call any function.
3. The length conversion function bpchar(bpchar, integer, boolean) is found in the system catalog and applied to the operator result and the stored column length. This function performs the necessary length check and pads the data with trailing blanks to meet the fixed-length requirement.