TutorialsArena

PostgreSQL: Numeric Data Types for Precise Calculations

Explore PostgreSQL's numeric data types for handling numbers with high precision. This guide covers different numeric types and their usage.

PostgreSQL Numeric Data Types

What is the PostgreSQL NUMERIC Data Type?

PostgreSQL's NUMERIC data type stores numbers with a high degree of precision. It's ideal for situations requiring exact values, such as monetary amounts or quantities where accuracy is crucial.

Numeric Data Types in PostgreSQL

PostgreSQL offers several numeric data types:

Name Storage Size Range Description
smallint 2 bytes -32768 to +32767 Stores whole numbers; small range.
integer 4 bytes -2147483648 to +2147483647 Stores whole numbers; common integer type.
bigint 8 bytes -9223372036854775808 to 9223372036854775807 Stores whole numbers; large range.
decimal Variable Up to 131072 digits before decimal; up to 16383 after. User-specified precision; exact.
numeric Variable Up to 131072 digits before decimal; up to 16383 after. User-specified precision; exact. Equivalent to decimal.
real 4 bytes 6 decimal digits precision Variable-precision; inexact.
double precision 8 bytes 15 decimal digits precision Variable-precision; inexact.
serial 4 bytes 1 to 2147483647 Auto-incrementing integer.
bigserial 8 bytes 1 to 9223372036854775807 Large auto-incrementing integer.

PostgreSQL Numeric Data Type Syntax

The syntax is:

Syntax

NUMERIC (precision, scale)

Parameters

Parameter Description
NUMERIC Keyword indicating a numeric data type.
precision Total number of digits.
scale Number of digits after the decimal point.

For example, in the number 2356.78, the precision is 6 and the scale is 2.

Note: NUMERIC can store up to 131,072 digits before the decimal and 16,383 after. The scale can be positive or zero.

If you omit the scale, it defaults to zero (NUMERIC(precision)). If you omit both precision and scale (NUMERIC), the database will allow any precision and scale up to its limits.

NUMERIC and DECIMAL are equivalent in PostgreSQL and follow the SQL standard.

Important: Avoid using NUMERIC if precision isn't required, as calculations are slower than with integers or floating-point types.

Examples

Example 1: Storing Numeric Values

Let's create a table called Items and insert some data. PostgreSQL will round values exceeding the declared scale.

Creating and Inserting into Items Table

DROP TABLE IF EXISTS Items;
CREATE TABLE Items (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,
    item_price NUMERIC(5,2)
);
INSERT INTO Items (item_name, item_price)
VALUES
('Mercedes Benz ',300.512),
('Lincoln Continental',300.513),
('Audi A7',300.514);
SELECT * FROM Items;

Since the scale of item_price is 2, the values are rounded to 300.51.

Attempting to insert a value exceeding the precision will result in an error (e.g., INSERT INTO Items (item_name, item_price) VALUES ('Mercedes Benz',23457.44); will cause a numeric field overflow).

Example 2: Using NaN (Not a Number)

The NUMERIC type can store NaN (Not a Number).

Using NaN

UPDATE Items
SET item_price = 'NaN'
WHERE item_id = 1;
SELECT * FROM Items;
SELECT * FROM Items ORDER BY item_price DESC;

NaN is not equal to any number, including itself (NaN = NaN is FALSE), but two NaN values are considered equivalent. This allows for efficient indexing.

Overview

We've covered PostgreSQL's NUMERIC data type, its variations, and how to handle precision, scale, and special values like NaN.