Description

CREATE CAST defines a new cast. A
cast specifies how to perform a conversion between two data
types. For example,

SELECT CAST(42 AS text);

converts the integer constant 42 to type text by invoking a previously specified function, in
this case text(int4). (If no suitable
cast has been defined, the conversion fails.)

Two types may be binary compatible,
which means that they can be converted into one another
"for free" without invoking any
function. This requires that corresponding values use the same
internal representation. For instance, the types text and varchar are binary
compatible.

By default, a cast can be invoked only by an explicit cast
request, that is an explicit CAST(x AS typename), x::typename, or typename(x)
construct.

If the cast is marked AS ASSIGNMENT
then it can be invoked implicitly when assigning a value to a
column of the target data type. For example, supposing that
foo.f1 is a column of type text, then

INSERT INTO foo (f1) VALUES (42);

will be allowed if the cast from type integer to type text is marked
AS ASSIGNMENT, otherwise not. (We
generally use the term assignment cast
to describe this kind of cast.)

If the cast is marked AS IMPLICIT
then it can be invoked implicitly in any context, whether
assignment or internally in an expression. For example, since
|| takes text
operands,

SELECT 'The time is ' || now();

will be allowed only if the cast from type timestamp to text is marked
AS IMPLICIT. Otherwise it will be
necessary to write the cast explicitly, for example

SELECT 'The time is ' || CAST(now() AS text);

(We generally use the term implicit
cast to describe this kind of cast.)

It is wise to be conservative about marking casts as implicit.
An overabundance of implicit casting paths can cause PostgreSQL to choose surprising
interpretations of commands, or to be unable to resolve commands
at all because there are multiple possible interpretations. A
good rule of thumb is to make a cast implicitly invokable only
for information-preserving transformations between types in the
same general type category. For example, the cast from int2 to int4 can reasonably be
implicit, but the cast from float8 to
int4 should probably be assignment-only.
Cross-type-category casts, such as text to
int4, are best made explicit-only.

To be able to create a cast, you must own the source or the
target data type. To create a binary-compatible cast, you must be
superuser. (This restriction is made because an erroneous
binary-compatible cast conversion can easily crash the
server.)

Parameters

sourcetype

The name of the source data type of the cast.

targettype

The name of the target data type of the cast.

funcname(argtype)

The function used to perform the cast. The function name
may be schema-qualified. If it is not, the function will be
looked up in the path. The argument type must be identical
to the source type, the result data type must match the
target type of the cast.

WITHOUT FUNCTION

Indicates that the source type and the target type are
binary compatible, so no function is required to perform
the cast.

AS ASSIGNMENT

Indicates that the cast may be invoked implicitly in
assignment contexts.

AS IMPLICIT

Indicates that the cast may be invoked implicitly in any
context.

Notes

Use DROP CAST to remove user-defined
casts.

Remember that if you want to be able to convert types both
ways you need to declare casts both ways explicitly.

Prior to PostgreSQL 7.3,
every function that had the same name as a data type, returned
that data type, and took one argument of a different type was
automatically a cast function. This convention has been abandoned
in face of the introduction of schemas and to be able to
represent binary compatible casts in the system catalogs. (The
built-in cast functions still follow this naming scheme, but they
have to be shown as casts in the system catalog pg_cast now.)

Examples

To create a cast from type text to type
int4 using the function int4(text):

CREATE CAST (text AS int4) WITH FUNCTION int4(text);

(This cast is already predefined in the system.)

Compatibility

The CREATE CAST command conforms to
SQL99, except that SQL99 does not make provisions for
binary-compatible types. AS IMPLICIT is
a PostgreSQL extension, too.

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.