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:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [PostgreSQL]: Port [5432]: 4144 Username [PostgreSQL]: orkb Password FOR USER orkb: |
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:
1 2 3 4 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \ -U orkb \ -d PostgreSQL \ -p 4144 |
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:
1 | SELECT CAST( VALUE AS new_datatype ) |
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:
1 2 3 4 5 | SELECT 89.12::INT; int4 ------ 89 (1 ROW) |
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:
1 2 3 4 5 | SELECT CAST ('77' AS INT); int4 ------ 77 (1 ROW) |
Cast Integer as String
The following example illustrates how to cast the integer value to a string value:
1 2 3 4 5 | SELECT CAST (77 AS VARCHAR); VARCHAR --------- 77 (1 ROW) |
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:
1 2 3 4 5 | SELECT CAST ('92.56418' AS DECIMAL(10,2)); NUMERIC --------- 92.56 (1 ROW) |
Cast as Date
The following example demonstrates how to cast a date that is currently a string value to a date data type value:
1 2 3 4 5 | SELECT CAST ('July 24, 1999' AS DATE); DATE ------------ 1999-07-24 (1 ROW) |
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:
1 2 3 4 5 | SELECT TRIM('Today is April 21, 2020', 'Today is ')::DATE date_today; date_today ------------ 2020-04-21 (1 ROW) |
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:
1 2 3 4 5 | SELECT SUBSTRING('I am 21 year old' FROM 6 FOR 2)::INT age; age ----- 21 (1 ROW) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started