SQL REAL

What is the SQL REAL data type?

REAL is a single-precision floating-point data type used to store approximate numeric values.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REAL: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, MariaDB, DB2

SQL REAL Full Explanation

REAL is defined by the SQL standard as a single-precision floating-point (approximate) numeric type. It typically maps to a 4-byte IEEE-754 float that can represent roughly 6–9 decimal digits of precision and a wide range of magnitudes. Because REAL is approximate, arithmetic and comparisons may introduce rounding error. Use REAL for scientific or statistical values where small precision loss is acceptable, not for currency or exact totals.Behavior varies by database:- PostgreSQL and Snowflake implement REAL as 4-byte float.- SQL Server maps REAL to 4-byte float.- Oracle maps BINARY_FLOAT to REAL when using ANSI syntax.- MySQL treats REAL as DOUBLE by default unless the SQL_MODE includes REAL_AS_FLOAT.- SQLite stores REAL as 8-byte float (DOUBLE) internally but preserves the REAL type affinity.REAL supports all numeric operators and aggregate functions (SUM, AVG, MIN, MAX). Conversions to and from other numeric types may truncate or round. Indexes on REAL columns work but equality comparisons can be unreliable because 0.1 cannot be represented exactly. Prefer range comparisons or rounding when filtering.Storage size, precision, and the keyword REAL itself are part of SQL-92, making the type portable across engines, but always confirm your dialect’s exact implementation.

SQL REAL Syntax

-- Column definition
column_name REAL;

-- Cast literal
SELECT CAST(42.7 AS REAL);

-- Create table
CREATE TABLE readings (
    id INT PRIMARY KEY,
    temperature REAL,
    humidity REAL
);

SQL REAL Parameters

Example Queries Using SQL REAL

-- 1. Create table with REAL columns
CREATE TABLE measurements (
    id SERIAL PRIMARY KEY,
    temperature REAL,
    humidity REAL
);

-- 2. Insert REAL values
INSERT INTO measurements (temperature, humidity)
VALUES (23.4, 0.45),
       (19.8, 0.40);

-- 3. Aggregate REAL data
SELECT AVG(temperature) AS avg_temp
FROM measurements;

-- 4. Filter with tolerance to avoid precision issues
SELECT *
FROM measurements
WHERE temperature > 20.0;

Expected Output Using SQL REAL

Table created.
Two rows inserted.
Query returns a single row with the average temperature (e.g., 21.6).
Query returns rows where temperature exceeds 20.0.

Use Cases with SQL REAL

Storing sensor readings or scientific measurements where perfect precision is not critical.
Calculating averages, minimums, maximums, standard deviations on large numeric datasets.
Representing percentages, ratios, or probabilities that tolerate small rounding error.

Common Mistakes with SQL REAL

Using REAL for monetary values, leading to rounding errors. Use DECIMAL/NUMERIC instead.
Expecting exact equality (temperature = 0.1). Use a tolerance (ABS(val - 0.1) < 0.00001).
Assuming REAL is always 4-byte; some engines store 8-byte.
Forgetting that MySQL’s default REAL is actually DOUBLE unless REAL_AS_FLOAT is set.

First Introduced In

SQL-92 Standard

Frequently Asked Questions

What precision does REAL provide?

REAL typically offers about 6–9 significant decimal digits, depending on the specific value stored and the database implementation.

Does REAL round or truncate values on insert?

Values are rounded to the nearest representable binary float. Very small fractions may be stored as zero, and repeating decimals like 0.1 cannot be represented exactly.

Can I index a REAL column?

Yes, but be cautious with equality lookups; floating-point rounding can make exact matches unreliable. Range queries generally work as expected.

How do I convert REAL to DECIMAL without losing data?

Use an explicit CAST or CONVERT. Precision beyond the 6–9 digits stored in REAL cannot be recovered, so round appropriately before casting.