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.
Generally any of the following three methods can be used in order to cast the value contained within a string constant to
another type:
In the case of maintained numeric constants that you wish to cast to a character string, you will need to use one of the following
syntax forms:
-
value
::
type
-
CAST (
value
AS
type
)
The
value
in this syntax represents the constant whose data type you wish to modify,
and
type
represents the type that you wish to coerce, or cast, the value into.
Note: Remember that the money type is deprecated, and therefore not easily cast.
Constants are not the only data values that may be coerced to different types. Columns of a data set returned by a SQL query
may be cast by using its identifier in one of the following syntax forms:
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 string
abcd
into a binary bit type. Invalid casting will result in an error from
PostgreSQL. Common valid casts are from character string, date/time type, or a numeric type to text, or character strings to numeric values.
In addition to these type casting conventions, there are some functions that 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 text() function), though others are named more specifically
(such as bitfromint4()). Example 3-30 shows such a function, converting the integer 1000 to a character string of type
text representing the characters
1000
.
Example 3-30. Using Type Conversion Functions
booktown=#
SELECT text(1000)
booktown-#
AS explicit_text;
explicit_text
---------------
1000
(1 row)
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;
add_one_to_twelve
-------------------
13
(1 row)
booktown=#
SELECT 1 + CAST('1' || '2' AS integer) AS add_on_to_twelve;
add_on_to_twelve
------------------
13
(1 row)
