Supported Versions: Current (16) / 15 / 14 / 13 / 12
Development Versions: devel
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

5.4. Query Targets

Query Target Type Resolution

  1. Check for an exact match with the target.

  2. Otherwise, try to coerce the expression to the target type. This will succeed if the two types are known binary-compatible, or if there is a conversion function. 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.

  3. 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 an integer, and returning the same type. If one is found, it is applied, passing the column's declared length as the second parameter.

5.4.1. Examples

5.4.1.1. varchar Storage

For a target column declared as varchar(4) the following query ensures that the target is sized correctly:

tgl=> CREATE TABLE vv (v varchar(4));
CREATE
tgl=> INSERT INTO vv SELECT 'abc' || 'def';
INSERT 392905 1
tgl=> SELECT * FROM vv;
  v
------
 abcd
(1 row)
What's 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 coerced to varchar to match the target column type. (But, since the parser knows that text and varchar are binary-compatible, this coercion is implicit and does not insert any real function call.) Finally, the sizing function varchar(varchar,int4) is found in the system catalogs and applied to the operator's result and the stored column length. This type-specific function performs the desired truncation.