PostgreSQL CAST for ObjectRocket Instance

Introduction

There may be times during the performance of database administrative tasks that calls for the conversion of data types. The PostgreSQL CAST function allows for the conversion of one data type to another, such as string to integers, and specifies how to perform the conversion using a previously specified function. This tutorial will provided explanations and examples for using the PostgreSQL CAST for a ObjectRocket instance.

Prerequisites

• An instance of PostgreSQL on a ObjectRocket account.

• The ability to connect to the instance remotely to use the PostgreSQL CAST for a ObjectRocket instance.

ObjectRocket Instance of PostgreSQL

Begin by setting up a PostgreSQL instance on a ObjectRocket account via the create instance tab on the ObjectRocket website.

Connection of PostgreSQL to the Instance

The following section provides examples on how to connect to the instance.

Connect Using the Windows

For Windows machines, connect to the instance using either the command prompt or the SQL (psql) shell.

The following example shows how to connect to the instance with the SQL shell:

Connect Using UNIX-Based Operating System

For UNIX-based systems, such as Linux and macOS, execute the following command in the terminal window to connect to the instance:

PostgreSQL CAST Function

The Postgres CAST function is used to convert the data type of a value to another specified data type.

Here is the syntax for CAST function:

A breakdown of the syntax for the CAST function is as follows:

• 1) Specify the value and the datatype inside the parenthesis.
• 2) The value is what the existing datatype will be converted to.
• 3) The new_datatype is the datatype to convert the value to; this can be an integer, decimal, numeral, float, date, varchar, text, etc.

Another way to cast a value, with a shorter query, is to just use two successive colons ::.

Following is an example of using the colons to change a decimal value to an integer value:

Note the value was successfully changed to an integer value using just :: to cast the value.

PostgreSQL CAST Function Examples

The following sections provide examples of various methods for converting a value with the CAST function.

Cast String as Integer

The following example demonstrates how to cast a string value to an integer value:

Cast Integer as String

The following example illustrates how to cast the integer value to a string value:

Note the datatype of the value has been sucessfully casts as VARCHAR.

Cast String as Decimal

This example shows how to cast a string as decimal. Here a number that is currently a string value will be to converted to decimal, but with only two places after the decimal point:

Cast as Date

The following example demonstrates how to cast a date that is currently a string value to a date data type value:

CAST With TRIM

The following CAST with TRIM example shows how to cast a value from a sentence with a TRIM function to get just the specific value that will be cast:

Note that the date from the sentence has now been cast as the datatype DATE.

CAST With SUBSTRING

The final example in this tutorial illustrates how to use the SUBSTRING function to cast an age value from sentence as just an interger:

Note that the age from the sentence has been successfully casts as an integer value.

Conclusion

This tutorial provided explanations and examples for using the PostgreSQL CAST for a ObjectRocket instance. The tutorial first covered setting up a PostgreSQL instance on a ObjectRocket account and how to connect to the instance with both Windows and Unix systems. The article then provided examples to demonstrate how to use the CAST function to convert the data types, the syntax for the function and a breakdown of the syntax. The tutorial then provided examples for executing the PostgreSQL CAST function for casting a string as integer, an integer as string, a string as decimal, as date, how to CAST with TRIM and CAST with SUBSTRING. Remember that an alternate, and shorter, way to cast a value is by just using two colons :: in succession.