REAL

The PostgreSQL REAL type (also known as FLOAT4) stores single-precision floating-point numbers using 4 bytes. It is an inexact type — like DOUBLE PRECISION — but trades precision for storage efficiency. Use it for sensor readings, physical measurements, and other use cases where 6 significant digits is sufficient.

Key characteristics

• Storage: 4 bytes
• Range: approximately -3.40282347E+38 to 3.40282347E+38
• Precision: about 6 significant decimal digits
• Synonyms: REAL, FLOAT4

Storing weather data with REAL

CREATE TABLE weathers (
  id                  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  location            VARCHAR(255) NOT NULL,
  wind_speed_mps      REAL NOT NULL,
  temperature_celsius REAL NOT NULL,
  recorded_at         TIMESTAMP NOT NULL
);

INSERT INTO weathers (location, wind_speed_mps, temperature_celsius, recorded_at)
VALUES
  ('New York', 5.2, 15.3, '2024-04-19 09:00:00'),
  ('New York', 4.8, 14.9, '2024-04-19 10:00:00'),
  ('New York', 6.0, 16.5, '2024-04-19 11:00:00'),
  ('New York', 5.5, 15.8, '2024-04-19 12:00:00'),
  ('New York', 5.9, 16.1, '2024-04-19 14:00:00');

Calculate average wind speed and temperature:

SELECT
  AVG(wind_speed_mps)      AS wind_speed,
  AVG(temperature_celsius) AS temperature_celsius
FROM weathers
WHERE location = 'New York'
  AND DATE(recorded_at) = '2024-04-19';

    wind_speed     | temperature_celsius
-------------------+---------------------
 5.480000019073486 |  15.719999980926514

The slight imprecision in the output (e.g., 5.48000001...) is characteristic of single-precision floating-point arithmetic.

REAL vs. DOUBLE PRECISION at a glance

• REAL (FLOAT4): 4 bytes, ~6 significant digits — good for high-volume sensor or telemetry data
• DOUBLE PRECISION (FLOAT8): 8 bytes, ~15 significant digits — better for scientific calculations requiring higher accuracy
• NUMERIC: variable bytes, exact — required for financial and other exact-precision use cases

Production tips

• Use REAL when storing large time-series datasets where storage reduction is meaningful and 6 digits of precision is sufficient.
• Avoid REAL for any financial, billing, or precise scientific computation — use NUMERIC for exact values.
• Avoid equality comparisons on REAL columns — always use range comparisons to account for floating-point imprecision.
• Consider DOUBLE PRECISION if you need to aggregate (SUM, AVG) REAL values frequently — accumulated rounding errors are more pronounced in REAL than in DOUBLE PRECISION.

Reference: PostgreSQL documentation — Floating-Point Types.