#Postgresql cast decimal to integer how to#
#Postgresql cast decimal to integer code#

To do this, you use the CASE expression with the type CAST as shown in the following query: SELECT id,ĬASE WHEN rating~E '^\d+$' THEN CAST (rating AS INTEGER)

Now, we have to convert all values in the rating column into integers, all other A, B, C ratings will be displayed as zero.

#Postgresql cast decimal to integer code#

Ratings Code language: SQL (Structured Query Language) ( sql )

So the ratings table stores mixed values including numeric and string. ( 'C') Code language: SQL (Structured Query Language) ( sql )īecause the requirements change, we use the same ratings table to store ratings as number e.g., 1, 2, 3 instead of A, B, and C: INSERT INTO ratings (rating)Ĭode language: SQL (Structured Query Language) ( sql ) Second, insert some sample data into the ratings table. ) Code language: SQL (Structured Query Language) ( sql ) The data type of the rating column is VARCHAR(1): CREATE TABLE ratings ( Here is the output: 7) Using CAST with table data exampleįirst, create a ratings table that consists of two columns: id and rating. '3 month':: interval Code language: SQL (Structured Query Language) ( sql ) This example uses the cast operator to convert a string to an interval: SELECT '15 minute':: interval, This example uses the cast operator (::) to convert a string to a timestamp: SELECT ' 14:30:20':: timestamp Code language: SQL (Structured Query Language) ( sql ) 6) Convert a string to an interval example Here is the output: 5) Convert a string to a timestamp example This example uses the CAST() to convert the string ‘true’, ‘T’ to true and ‘false’, ‘F’ to false: SELECT CAST( 'true' AS BOOLEAN),ĬAST( 'F' as BOOLEAN) Code language: SQL (Structured Query Language) ( sql ) To fix this, you need to use DOUBLE PRECISION instead of DOUBLE as follows: SELECT CAST ( '10.2' AS DOUBLE PRECISION) Code language: SQL (Structured Query Language) ( sql ) 4) Cast a string to a boolean example LINE 2: CAST ( '10.2' AS DOUBLE) Code language: PHP ( php ) Whoops, we got the following error message: ERROR: type 'double' does not exist In the following example, we try to convert a string '10.2' into a double value: SELECT CAST ( '10.2' AS DOUBLE) Code language: SQL (Structured Query Language) ( sql ) Second, we converted 0 to October 1st 2015. This example uses the CAST to convert a string to a date: SELECT CAST ( '' AS DATE),ĬAST ( '0' AS DATE) Code language: SQL (Structured Query Language) ( sql )įirst, we converted literal string into January 1st 2015. LINE 2: CAST ( '10C' AS INTEGER) Code language: PHP ( php ) 2) Cast a string to a date example See the following example: SELECT CAST ( '10C' AS INTEGER) Code language: SQL (Structured Query Language) ( sql ) ERROR: invalid input syntax for integer: '10C' If the expression cannot be converted to the target type, PostgreSQL will raise an error.

The following statement converts a string constant to an integer: SELECT CAST ( '100' AS INTEGER) Code language: SQL (Structured Query Language) ( sql ) Let’s take some examples of using the CAST operator to convert a value of one type to another. Notice that the cast syntax with the cast operator (::) is PostgreSQL-specific and does not conform to the SQL standard PostgreSQL CAST examples '0':: DATE Code language: SQL (Structured Query Language) ( sql ) See the following example: SELECT '100':: INTEGER,

Then, specify the target data type to which you want to convert the result of the expression.īesides the type CAST syntax, you can use the following syntax to convert a value of one type into another: expression::type Code language: SQL (Structured Query Language) ( sql ).

First, specify an expression that can be a constant, a table column, an expression that evaluates to a value.

The following illustrates the syntax of type CAST: CAST ( expression AS target_type ) Code language: SQL (Structured Query Language) ( sql )

PostgreSQL provides you with the CAST operator that allows you to do this. There are many cases that you want to convert a value of one data type into another.

#Postgresql cast decimal to integer how to#

Summary: in this tutorial, we will show you how to use PostgreSQL CAST operator to convert a value of one type to another.