Hello everyone, today we wanted to share with you an entry on how to make CAST conversions in PostgreSQL . This command exists in all databases and is used to convert from one data type to another. The most common conversions are usually from String to Integer, or dates to String and vice versa.
It is very important to use the correct data type, especially when we use it in table joins, since if we leave the data conversion to the engine itself, sometimes it may not return the result correctly or not use the indexes that we have created and cause a problem. serious performance.
CAST syntax
The syntax of the CAST command is:
CAST (expression AS target_type);
where:
- The expression it can be a constant, a column in a table, or any expression that resolves to a value.
- The target_type is the final data type we want to convert the expression to.
You can also use the following syntax without problem, since the result is the same:
expression :: type
An example may be the converting the number 97 to a String type , it can be done in the following ways:
SELECT CAST (97 AS varchar);
or
SELECT 97 :: varchar;
The result of both commands is:
Examples of conversions with CAST
Example 1. String a Integer
If we need to perform any mathematical operation on a field of type STRING we can do the following conversion:
SELECT '100' :: integer;
This conversion is not possible in the case that the text is alphanumeric, since it would give you an error:
SELECT '100ab' :: integer;
Example 2. String a Date
It is usually used in case of need operate on a date , such as calculating the age of an employee. In that case, the conversion can be done with the command:
SELECT CAST ('01 -01-1990 'AS DATE)
The result will be:
The most common date formats used are: MM / DD / YYYY, MM-DD-YYYY, MM-DD-YY, MM / DD / YY, YYYY / MM / DD, YYYY-MM-DD.
As in the previous case, if the date is not valid, it will return an error:
Numerical conversions with CAST
Example 3: String a type Double
If you ran the command:
SELECT '10 .25 ':: DOUBLE
It returns an error because the DOUBLE type is not a type of PostgreSQL native data, we need to run the following:
SELECT '10 .25 ':: DOUBLE PRECISION
The result is:
Example 4: String to Boolean
The syntax in this case is:
SELECT CAST ('true' AS BOOLEAN),
CAST ('false' as BOOLEAN),
CAST ('T' as BOOLEAN),
CAST ('F' as BOOLEAN),
CAST ('1' as BOOLEAN),
CAST ('0' as BOOLEAN);
The result is:
The error we get when trying, for example, to CAST on the value 3 is the following:
Example 5: String a Timestamp
This operation can be done with the following command:
SELECT '02 -12-2020 13:13:21 ':: timestamp;
The result will be:
Example 6: String to Interval
The interval type is used to obtain the intervals of time that has passed between two dates. Several examples are:
SELECT '55 minute ':: interval, '2.5 hour' :: interval, '1.5 day' :: interval, '2.5 week' :: interval, '3.2 month' :: interval;
The result will be:
It is also easy to pass interval types to dates if necessary to perform operations on them.
We hope that the ticket will be useful to you and that you can use it in your daily operations. Don’t miss any by signing up for our newsletter monthly. With one email a month you will be informed of our publications.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn
Thank you.
More examples in: https://www.postgresqltutorial.com/postgresql-cast/