61

Using PostgreSQL 9.3, I want to convert the calculated values to data type float.

My first attempt:

SELECT float(SUM(Seconds))/-1323 AS Averag;

Gives me this error:

syntax error at or near "SUM"

My second attempt:

SELECT to_float(SUM(Seconds))/-1323 AS Averag;

Gives me this error:

 function to_float(bigint) does not exist
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
MAK
  • 6,824
  • 25
  • 74
  • 131

3 Answers3

106

I use the shorthand cast syntax almost everywhere:

SELECT sum(seconds)::float / -1323 AS averag;

More details:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Yes! Thank you so much. But what is the best way to cast. Is that using `::` or using `CAST` ? – MAK Mar 18 '15 at 07:13
  • 2
    @MAK: Both do the same. I *[almost](http://stackoverflow.com/questions/28552825/postgres-using-an-index-for-one-table-but-not-another/28555617#28555617)* always use the shorter and clearer (IMO) `::target_type`. Follow the link in my question and in this comment, you'll find everything there is to know. – Erwin Brandstetter Mar 18 '15 at 07:19
  • for this case, is "float" is the same as "double precision"? – Kokizzu Nov 23 '16 at 05:48
  • what i mean is, is it 8-byte? – Kokizzu Nov 23 '16 at 05:48
  • @Kokizzu: Yes. `float` = `float8` = `double precision`. [Details in the manual here.](https://www.postgresql.org/docs/current/static/datatype-numeric.html) – Erwin Brandstetter Nov 23 '16 at 05:51
67

You need to use the cast syntax:

SELECT CAST (SUM(Seconds) AS FLOAT)/-1323 AS Averag;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • What is the difference between `CAST` and `CONVERT`? – MAK Feb 26 '15 at 07:07
  • 7
    In PostgreSQL, `cast` is used for type conversions (e.g., `int` to `float`, like this example does) and `convert` is used to change string encondings. – Mureinik Feb 26 '15 at 07:11
6

It is not exact casting but a trick to do the job :) and works almost in any language.

SELECT SUM(Seconds)/-1323.0 AS Averag;

OR

SELECT SUM(Seconds)*1.0/-1323 AS Averag;

Souvik
  • 813
  • 10
  • 7