SQL PRECISION

What is SQL PRECISION?

PRECISION sets the total number of significant digits (or fractional-seconds digits) for numeric and temporal data types.

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 PRECISION: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite (numeric only), Snowflake, BigQuery, Redshift

SQL PRECISION Full Explanation

PRECISION is not an executable command but a specification that appears inside data type declarations and CAST expressions. In numeric contexts (DECIMAL, NUMERIC) it defines the maximum count of significant digits that can be stored, while an optional SCALE controls digits to the right of the decimal point. In temporal contexts (TIME, TIMESTAMP, INTERVAL) precision represents the number of fractional-seconds digits. Declaring precision helps enforce data quality, determines storage size, and influences rounding rules during inserts, updates, and arithmetic operations. If an inserted or calculated value exceeds the declared precision the database rounds, truncates, or raises an error, depending on the dialect and session settings. Precision accepts positive integers, typically 1-38 for numeric types and 0-9 for fractional seconds. Omitting precision defaults to an implementation-specific value (e.g., NUMERIC defaults to the engine’s maximum; TIMESTAMP defaults to 6 in MySQL, 0 in SQL Server). PRECISION cannot be altered by itself; instead ALTER TABLE … ALTER COLUMN must redefine the full data type.

SQL PRECISION Syntax

-- Numeric
NUMERIC(precision [, scale])
DECIMAL(precision [, scale])

-- Temporal fractional seconds
TIME(precision)
TIMESTAMP(precision)
INTERVAL field1 TO field2(precision)

-- CAST
CAST(expression AS NUMERIC(precision, scale));

SQL PRECISION Parameters

precision (INTEGER) - total number of significant or fractional-seconds digits
scale (INTEGER) - optional, digits to the right of the decimal point (must be <= precision); not used for temporal types

Example Queries Using SQL PRECISION

-- Create table with numeric precision
CREATE TABLE sales (
  id INT PRIMARY KEY,
  total NUMERIC(10,2) -- up to 99999999.99
);

-- Insert that exceeds precision -> error or rounded
INSERT INTO sales (id, total) VALUES (1, 123456789.00);

-- Temporal precision
SELECT CAST('2024-06-15 12:34:56.789123' AS TIMESTAMP(3));

-- Change precision with ALTER TABLE
ALTER TABLE sales ALTER COLUMN total TYPE NUMERIC(12,4);

Expected Output Using SQL PRECISION

Table is created
The insert either fails or rounds depending on dialect because 123456789
00 needs 11 digits, exceeding the declared precision of 10
The CAST returns 2024-06-15 12:34:56
789 (milliseconds)
The ALTER TABLE enlarges the column to accept 12 total digits with 4 decimals

Use Cases with SQL PRECISION

Enforce business rules by limiting the size of monetary or scientific values
Reduce storage and index size for large fact tables
Control fractional-seconds precision when high-resolution timestamps are unnecessary
Prevent silent overflow by triggering errors when data exceeds allowed digits

Common Mistakes with SQL PRECISION

Confusing precision with scale; precision is total digits, scale is decimals
Declaring scale greater than precision, which is invalid
Assuming default precision is the same across databases
Forgetting to enlarge precision before loading larger historical data
Expecting temporal precision to affect whole seconds (it only controls fractional seconds)

First Introduced In

SQL-92

Frequently Asked Questions

What does PRECISION control?

It sets the total number of allowed digits for numeric types and the number of fractional-seconds digits for temporal types.

Can PRECISION be changed after table creation?

Yes. Use ALTER TABLE … ALTER COLUMN to redefine the column with a new data type that includes the desired precision and scale.

Is PRECISION mandatory?

No. If omitted, the database applies its default precision, which differs across engines (e.g., NUMERIC defaults to maximum precision; TIMESTAMP defaults vary).

What errors occur with incorrect precision settings?

Typical errors include precision exceeds maximum, scale exceeds precision, or numeric value out of range if an inserted value needs more digits than allowed.