Floating number comparison

  1. Cast from float to numeric
  2. Round

Example: Round the latitude and longitude to 4 decimal points.

database-devel=# SELECT id,lat,lon
FROM pointofinterest
WHERE townid = 1234
  AND round(lat::numeric,4) <> 40.4167
  AND round(lon::numeric,4) <> -3.7035
ORDER BY id;

Cast to float number

database-devel=# SELECT ((count(id)::float * 100) / 2147483647) AS oc
FROM table ;

Date comparison

DATE

Comparison of DATE values returns an INTEGER:

database-devel=# SELECT DATE '1999-12-30' - DATE '1999-12-11';
19

TIMESTAMP

Comparison of TIMESTAMP values returns an INTERVAL:

database-devel=# SELECT TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11';
19 days, 0:00:00

database-devel=# SELECT id, dateadd, datelast, datelastfeed
  FROM list
  WHERE clientid = 677
  AND datelastfeed - dateadd > INTERVAL '60 days';