Postgresql – postgres point insert

functionspostgresqlpostgresql-9.4

I have a table with a point data type that stores the latitude and longitude. I plan to insert from a function, and the input argument is defined as a float(18) [which is where I suspect the problem comes from].

Let's say the following values are used: 

48.277274, -116.5547745

If I insert these with from the command line the database shows those values. But if I insert them from the function the values in the database are: 

48.2772750854492, -116.55477142334

I'm not doing anything to modify the values, as I mention above I believe bringing them in as float is causing the problem, but I've tried various other types and only get other issues.

Would greatly appreciate any help.

CREATE OR REPLACE FUNCTION myfun(
    float(18), --   lat
    float(18)) --  lon
  RETURNS integer AS
$BODY$
DECLARE
    postcnt integer; 
BEGIN
   INSERT INTO user(usergeo)
   VALUES (point($1,$2))
   RETURN 1;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Best Answer

When executing this:

INSERT INTO table VALUES(point(48.277274, -116.5547745));

the two decimal constants are implicitly casted to double precision numbers, since it corresponds to the closest matching types for the point() function:

test=> \df point
                                  List of functions
   Schema   | Name  | Result data type |        Argument data types         |  Type  
------------+-------+------------------+------------------------------------+--------
 pg_catalog | point | point            | box                                | normal
 pg_catalog | point | point            | circle                             | normal
 pg_catalog | point | point            | double precision, double precision | normal
 pg_catalog | point | point            | lseg                               | normal
 pg_catalog | point | point            | path                               | normal
 pg_catalog | point | point            | polygon                            | normal
(6 rows)

On the other hand, when executing this:

SELECT myfun(48.277274, -116.5547745)

the two decimal constants are implicitly casted to real as the type, because float(n) is equivalent to real when 1<=n<=24, as explained in the documentation on datatypes:

PostgreSQL also supports the SQL-standard notations float and float(p) for specifying inexact numeric types. Here, p specifies the minimum acceptable precision in binary digits. PostgreSQL accepts float(1) to float(24) as selecting the real type, while float(25) to float(53) select double precision. Values of p outside the allowed range draw an error. float with no precision specified is taken to mean double precision

This downcast comes with a loss of precision, because, from the same doc page:

On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits

Conclusion: use double precision instead of float(18), which in fact is a synonym for the less-precise real.

Related Question