There are many cases that you want to convert a value of one data type into another.
PostgreSQL provides you with the CAST operator that allows you to do this. You can convert string to integers, casting to Boolean, casting string to date and so on.
Syntax:
CAST (expression AS data type);
One more Syntax:
Expression :: type
Examples
String to integer Casting
SELECT
CAST ('1' AS INTEGER);
//output
1
If it's not possible to convert, it will raise an error.
Using the 2nd syntax, let's cast float to int.
select 1.0::integer;
//output
1
timestamp to date casting Syntax
SELECT expression ::TIMESTAMP::DATE;
example
SELECT expression ::TIMESTAMP::DATE;
//output
2020-03-18
Type DOUBLE doesn't exist in Postgres. So, you need to use DOUBLE PRECISION.
For example,
SELECT CAST ('1100.100' AS DOUBLE PRECISION);
//output
1100.1
You can also do something like this:
SELECT CAST ( 2 AS numeric ) + 4.0;
//output
6.0
If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression. So, you can write the above query without casting..
SELECT 2 + 4.0;
//output
6.0
casting database sql query