Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

Type Coercion

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:

  • type ' value  '

  • ' value  ':: type

  • CAST (' value  ' AS 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:

  • identifier  :: type

  • CAST ( identifier AS type  )

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

SELECT text(1000) 

AS explicit_text;

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

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
SELECT 1 + ('1' || '2')::integer AS add_one_to_twelve;

(1 row)

SELECT 1 + CAST('1' || '2' AS integer) AS add_on_to_twelve;

(1 row)

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire