Help Center > > Developer Guide> SQL Reference> Type Conversion> Value Storage

Value Storage

Updated at:Mar 13, 2020 GMT+08:00

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


Use the character storage type conversion as an example. For a target column declared as character(20) the following statement shows that the stored value is sized correctly:

CREATE TABLE tpcds.value_storage_t1 (

SELECT VS_COL1, octet_length(VS_COL1) FROM tpcds.value_storage_t1;
       vs_col1        | octet_length 
 abcdef               |           20
(1 row)

DROP TABLE tpcds.value_storage_t1;

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 conversion from text to bpchar is binary-coercible, this conversion does not insert any real function call. Finally, the sizing function bpchar(bpchar, integer, Boolean) is found in the system catalog and used for the operator's result and the stored column length. This type-specific function performs the required length check and addition of padding spaces.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?

Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel