NUMERIC Data Type in PostgreSQL

The PostgreSQL NUMERIC type (also called DECIMAL or DEC) stores numbers with exact decimal precision. It is the correct choice for financial data, quantities, invoice totals, or any value where floating-point approximation would cause incorrect results.

Syntax

column_name NUMERIC(precision, scale)
column_name DECIMAL(precision, scale)  -- identical alias
column_name NUMERIC                    -- unconstrained: up to 131,072 + 16,383 digits

• precision: total number of significant digits on both sides of the decimal point
• scale: digits to the right of the decimal point

For example, NUMERIC(7, 2) stores values up to 99999.99.

Practical Example

Create a product pricing table using NUMERIC for monetary columns:

CREATE TABLE products (
  id             SERIAL PRIMARY KEY,
  name           VARCHAR(100) NOT NULL,
  unit_price     NUMERIC(10, 2) NOT NULL,
  discount_pct   NUMERIC(5, 4) NOT NULL DEFAULT 0,
  stock_quantity NUMERIC(12, 0) NOT NULL DEFAULT 0
);

INSERT INTO products (name, unit_price, discount_pct, stock_quantity)
VALUES
  ('Titanium Bolt Set',    19.99,  0.1000, 500),
  ('Carbon Fiber Panel',  149.50,  0.0500, 120),
  ('Ceramic Brake Pad',    34.00,  0.0000,  75);

Compute the discounted price — no floating-point rounding errors:

SELECT
  name,
  unit_price,
  discount_pct,
  unit_price * (1 - discount_pct) AS discounted_price
FROM products;

        name         | unit_price | discount_pct | discounted_price
---------------------+------------+--------------+------------------
 Titanium Bolt Set   |      19.99 |       0.1000 |         17.9910
 Carbon Fiber Panel  |     149.50 |       0.0500 |        142.0250
 Ceramic Brake Pad   |      34.00 |       0.0000 |         34.0000

Rounding and Overflow Behavior

Values with more fractional digits than the scale are rounded:

INSERT INTO products (name, unit_price, discount_pct)
VALUES ('Test Item', 99.995, 0);

SELECT unit_price FROM products WHERE name = 'Test Item';
-- unit_price: 100.00  (rounded up)

Exceeding the total precision raises an error:

INSERT INTO products (name, unit_price, discount_pct)
VALUES ('Overflow Item', 123456789.99, 0);
-- ERROR: numeric field overflow
-- DETAIL: A field with precision 10, scale 2 must round to an absolute value less than 10^8.

NaN: Not-a-Number

NUMERIC supports a special NaN (not-a-number) value. Unlike IEEE 754, PostgreSQL treats NaN as equal to itself and greater than all regular numbers:

UPDATE products SET unit_price = 'NaN' WHERE id = 1;

SELECT name, unit_price FROM products ORDER BY unit_price DESC;
-- NaN rows sort first (greatest)

Testing with Vela

Precision and scale decisions made early in schema design are hard to change later. Before deploying a pricing schema to production, create a database branch on Vela, populate it with representative data, and run aggregation queries (SUM, AVG, ROUND) to verify that the chosen precision and scale handle your edge cases — extreme prices, discount stacking, tax calculations — without overflow or unexpected rounding.

Production Tips

• Always use NUMERIC for monetary values — never FLOAT or DOUBLE PRECISION, which can produce rounding errors in financial calculations.
• A common pattern for money is NUMERIC(19, 4): 15 digits before the decimal and 4 after, covering most real-world monetary values with room to spare.
• Arithmetic on NUMERIC is slower than on integer or float types. For non-financial analytical workloads where approximation is acceptable, prefer DOUBLE PRECISION.
• Use NUMERIC(p, s) with explicit precision and scale for columns that interface with external APIs to prevent unexpected precision changes at the boundary.
• Inspect precision and scale with \d+ table_name in psql or by querying information_schema.columns.