Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Parse float to int in postgresql

I've a set of data in a postgresql DB, where one of these columns store string data in float format, but now I need remove the decimal component of the string. How can I do this using an sql update statement in my BD console? Is that possible?

for example:

"25.3" -> "25"

If it does not possible how can I do this? Thanks in advance.

like image 743
HCarrasko Avatar asked Feb 23 '15 18:02

HCarrasko


People also ask

How do I convert text to numeric in PostgreSQL?

Discussion: Use the :: operator to convert strings containing numeric values to the DECIMAL data type. In our example, we converted the string ' 5800.79 ' to 5800.79 (a DECIMAL value). This operator is used to convert between different data types.

How do I cast type in PostgreSQL?

PostgreSQL supports a CAST operator that is used to convert a value of one type to another. Syntax: CAST ( expression AS target_type ); Let's analyze the above syntax: First, specify an expression that can be a constant, a table column, an expression that evaluates to a value.

What is int2 in PostgreSQL?

float4. single precision floating-point number. smallint. int2. signed two-byte integer.

What is float8 in PostgreSQL?

real or float8 is a 4-byte floating-point number. numeric or numeric(p,s) is a real number with p digits with s number after the decimal point. The numeric(p,s) is the exact number.


Video Answer


1 Answers

You would be better suited casting the columns that were text, to numeric, to integer, so that rounding is taken into consideration e.g.

SELECT '25.3'::numeric::integer AS num1, '25.5'::numeric::integer AS num2

which would return integers of 25 and 26 respectively.

If you were not concerned with the digits following the point, the floor(column_name::numeric)::integer function or a substring, as mentioned, should be fine.

like image 133
Lucas Avatar answered Oct 27 '22 00:10

Lucas