Summary: in this tutorial, you’ll learn how to use the PostgreSQL DECIMAL data type to store the exact numeric data in the database.
Getting started with PostgreSQL DECIMAL data type #
PostgreSQL DECIMAL is a numeric data type that allows you to store exact numeric values with precision.
The DECIMAL data type, with its precision and exactness, is suitable for storing financial amounts and other numbers where accuracy is crucial.
Here’s the syntax for the defining a DECIMAL column:
CREATE TABLE table_name(
column_name DECIMAL(p,s),
...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)DECand NUMERIC are synonyms of the DECIMAL, so you can use them interchangeably. For example:
CREATE TABLE table_name(
column_name DEC(p,s),
...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In this syntax:
pstands for precision. The precision is the number of significant digits, including integer and fractional parts. The precision must be between1and1000.sstands for scale. The scale is the number of digits to the right of the decimal point. The valid range for scale is from-1000to1000. More on the negative scale soon.
For example, a DEC(11,2) column can store a number with 11 digits, including two digits after the decimal point.
If you omit the scale, it will default to zero:
DEC(p)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)It is equivalent to:
DEC(p,0)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)However, the precision and scale will default to their above limits if you ignore both precision and scale:
DECCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)If you don’t use precision and scale constraints, PostgreSQL can store up to 131072 digits before and up to 16383 digits after the decimal point.
PostgreSQL Decimal Data Type example #
First, create a table called inventories:
CREATE TABLE inventories (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
quantity INT NOT NULL CHECK (quantity >= 0),
price DEC(11, 2) NOT NULL CHECK (price > 0),
brand VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Second, insert some rows into the inventories table:
INSERT INTO
inventories (product_name, quantity, price, brand)
VALUES
('iPhone 16 Pro', 50, 999.99, 'Apple'),
('iPhone 16 Pro Max', 40, 1099.99, 'Apple'),
('iPhone 16 Plus', 55, 899.99, 'Apple'),
('Galaxy S24 Ultra', 45, 1199.99, 'Samsung'),
('Galaxy S24 Plus', 50, 999.99, 'Samsung'),
('Galaxy Z Fold 6', 30, 1799.99, 'Samsung'),
('Galaxy Z Flip 6', 35, 999.99, 'Samsung'),
('Pixel 9', 50, 799.99, 'Google'),
('Pixel 9 Pro', 40, 999.99, 'Google'),
('Pixel 9 Pro Fold', 25, 1799.99, 'Google')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
id | product_name | quantity | price | brand
----+-------------------+----------+---------+---------
1 | iPhone 16 Pro | 50 | 999.99 | Apple
2 | iPhone 16 Pro Max | 40 | 1099.99 | Apple
3 | iPhone 16 Plus | 55 | 899.99 | Apple
4 | Galaxy S24 Ultra | 45 | 1199.99 | Samsung
5 | Galaxy S24 Plus | 50 | 999.99 | Samsung
6 | Galaxy Z Fold 6 | 30 | 1799.99 | Samsung
7 | Galaxy Z Flip 6 | 35 | 999.99 | Samsung
8 | Pixel 9 | 50 | 799.99 | Google
9 | Pixel 9 Pro | 40 | 999.99 | Google
10 | Pixel 9 Pro Fold | 25 | 1799.99 | GoogleCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Third, calculate the total inventory value for each brand:
SELECT
brand,
SUM(quantity * price)
FROM
inventories
GROUP BY
brand;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
brand | sum
---------+-----------
Google | 124998.85
Apple | 143498.55
Samsung | 192998.40Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Negative scales #
Starting from PostgreSQL 15, you can declare a decimal column with a negative scale:
DEC(p,-s)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In this syntax, PostgreSQL will round the number to the left of the decimal point. The precision (p) represents the maximum number of non-rounded digits.
For example:
First, create a table called items with a DEC column that has a negative scale:
CREATE TABLE items (
item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
value DEC(5, -2) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Second, insert two rows into the items table:
INSERT INTO
items (value)
VALUES
(12345),
(67890)
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
item_id | value
---------+-------
1 | 12300
2 | 67900Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Since the value column has a precision of -2, PostgreSQL rounds them to the nearest hundred, with the total number of significant digits being 5.
Rounding #
PostgreSQL uses the round ties away from zeros for decimal values.
For example, if you round 2.5 to the nearest whole number, PostgreSQL will round it to 3. If you round -2.5 to the nearest number, PostgreSQL will round it to -3.
The following example shows how to use the ROUND function to round the average inventory values to numbers with two digits after the decimal point:
SELECT
brand,
AVG(quantity * price),
ROUND(AVG(quantity * price), 2)
FROM
inventories
GROUP BY
brand;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
brand | avg | round
---------+--------------------+----------
Google | 41666.283333333333 | 41666.28
Apple | 47832.850000000000 | 47832.85
Samsung | 48249.600000000000 | 48249.60Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Special values #
Besides regular values, the decimal type supports several special values specified in the IEEE 754 standard:
Infinity– represents an infinitely large value.-Infinity– represents an infinitely small value.NaN(not-a-number) – represents a value that is not a number, which results from an invalid calculation.
When using these values in SQL statements, you must place them in a single quote like 'Infinity' ,'-Infinity', and 'NaN'.
Summary #
- Use the PostgreSQL
DECIMALdata type to store numeric values that require exactness.
