Yogesh Chauhan's Blog

Casting in PostgreSQL

in Postgres on April 25, 2020

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

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to compile and watch Sass using Gulp in WordPress?SCSSUseful (and probably ignored) HTML tags: Part 2HTMLSocial Media Colors: Sass VariablesMiscellaneousHow to activate and deactivate hooks in a WordPress plugin?WordPressObject destructuring in JavaScript: Unpacking fields from objects passed as function parameterJavaScriptResponsive Masonry Grid using CSS columns PropertyCSS