| PostgreSQL 7.4.16 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 10. Type Conversion | Fast Forward | Next |
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.
If the target is a fixed-length type (e.g., char or varchar declared with a length) then try to find a sizing function for the target type. A sizing function is a function of the same name as the type, taking two arguments of which the first is that type and the second is of type integer, and returning the same type. If one is found, it is applied, passing the column's declared length as the second parameter.
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.
No comments could be found for this page.
Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.
In order to submit a comment, you must have a community account.
* denotes required field