Postgresql 中文操作指南

10.4. Value Storage #

将要插入表中的值根据以下步骤转换为目标列的数据类型。

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

Example 10.9. character Storage Type Conversion

对于声明为 character(20) 的目标列，以下语句显示存储的值大小正确：

For a target column declared as character(20) the following statement shows that the stored value is sized correctly:

CREATE TABLE vv (v character(20));
INSERT INTO vv SELECT 'abc' || 'def';
SELECT v, octet_length(v) FROM vv;

          v           | octet_length
----------------------+--------------
 abcdef               |           20
(1 row)

此处发生的实际情况是：默认情况下，两个未知文本解析为 text，这样 || 操作符就可以解析为 text 连接。然后，操作符的 text 结果转换为 bpchar (“空填充字符”，character 数据类型的内部名称) 以匹配目标列类型。(由于从 text 到 bpchar 的转换是二进制可强制转换的，因此此转换不会插入任何实际函数调用。) 最后，可在系统目录中找到尺寸调整函数 bpchar(bpchar, integer, boolean)，并将该函数应用于操作符的结果和存储的列长度。此类型特定函数执行所需的长度检查和填充空格的添加。

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