PostgreSQL supports three separate conventions for type coercion (also called type casting , or explicit type casting ). Type coercion is a somewhat ugly looking term which refers to a PostgreSQL method for changing a value from one data type to another. In the middle of a SQL statement, this has the net effect of explicitly creating a constant of an arbitrary type.

Any one of the following three methods can be used in order to cast the value contained within a string constant to another type:

  • type 'value'

  • 'value'::type

  • CAST ('value' AS type)

The value in this case represents the content that you are wishing to modify the data type of, and type represents the type that you wish to coerce, or cast, the data into. Bear in mind that not every data type can be coerced into every other data type. For example, there is no meaningful way to convert the character data 'abcd' into a binary bit type. Invalid casting will result in an error from PostgreSQL.

In addition to these type casting conventions, there are some functions which can be called to achieve essentially the same effect as an explicit cast of any of the previously mentioned forms. These often bear the name of the type itself (such as the timestamp() function), though others are named more specifically (such as bitfromint4() ). Example 3-15 shows an example of such a function, converting the integer 1000 to a character string of type text representing the characters '1000' .

Example 3-15. Using Type Conversion Functions

booktown=# SELECT text(1000) 
booktown-# AS explicit_text;
(1 row)
Note Casting Non-String Constants

Because of conflicting semantics recognized by PostgreSQL's parser, the type coercion format of type 'value' can only be used to specify the data type of a single value (e.g., a string constant bound by single quotes). In contrast, the other available methods of type coercion ( 'value'::type , CAST('value' AS type) , and type conversion functions, where applicable) can be used to to specify the type of arbitrary expressions.

This is partially because attempting to follow a data type with a grouped expression (e.g., in parentheses) will cause PostgreSQL to expect a function with the name of the provided data type (which will often cause an error), while each of the other methods are syntactically valid upon grouped expressions.

booktown=# SELECT 1 + integer ('1' || '2') AS add_one_to_twelve;
ERROR:  Function 'integer(text)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
booktown=# SELECT 1 + ('1' || '2')::integer AS add_one_to_twelve;
(1 row)

booktown=# SELECT 1 + CAST('1' || '2' AS integer) AS add_on_to_twelve;
(1 row)