How to Differentiate Between Numeric and Decimal Data Types in PostgreSQL

1. Data Types in PostgreSQL
2. Login to PostgreSQL
3. Use the NUMERIC Data Type in PostgreSQL
4. Use the DECIMAL Data Type in PostgreSQL
5. Conclusion

This tutorial guides the reader on the difference between numeric and decimal data types in the PostgreSQL database.

Data Types in PostgreSQL

The PostgreSQL database has many data types ranging from text to numeric, including integer, bigint, decimal, numeric, character varying, character, text, etc.

These data types help us store an application’s data. The data type chosen depends on the storage requirements and overall application performance; for example, the bigint data type is used when the integer data type does not meet the storage requirements of our data.

When working with data that do not have the fractional part, such as a person’s age, it is recommended to use the data types that do not have the scale part, such as the integer. When working with data that have the fractional part, such as the price of a product, it is recommended to use the data types that have the scale part, such as numeric and decimal.

In this tutorial, we will learn how to use the NUMERIC and DECIMAL data types to represent monetary values.

Login to PostgreSQL

To log in to the PostgreSQL database, open a new terminal window and use the following command to log in to the local PostgreSQL server.

david@david-HP-ProBook-6470b:~$ psql -U 

Press the Enter button on your keyboard, and if a password is requested, enter the password created during the database installation and press Enter again. If the password is correct, the terminal window should be connected to the postgres database, as shown below.

david@david-HP-ProBook-6470b:~$ psql -U postgres
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# 

Use the NUMERIC Data Type in PostgreSQL

We will use a separate database for each example to understand how these two numeric types vary. Use the following SQL command to create a database named numeric_db.

numeric_vs_decimal=# create database numeric_db;
CREATE DATABASE

The CREATE DATABASE shows that the database was successfully created. We only need to use the following command to connect to the database.

numeric_vs_decimal=# \c numeric_db;
You are now connected to database "numeric_db" as user "postgres".
numeric_db=#

The terminal window should now be connected to numeric_db, as shown in the above code.

Copy and paste the following SQL command to the terminal to create a table named product with the primary key id and the attributes productName and productPrice.

numeric_db=# create table product(
numeric_db(# id SERIAL NOT NULL UNIQUE,
numeric_db(# productName VARCHAR(100),
numeric_db(# productPrice NUMERIC(6,4));
CREATE TABLE

The CREATE TABLE shows that the table was created. Note that the productName is of type VARCHAR and productPrice is of type NUMERIC.

Note that we have passed the arguments 6 and 4 in the declaration of the productPrice data type. The parameters provide information regarding the precision and scale of the data, respectively.

Precision refers to the total number of digits on both sides of the decimal point, and scale refers to the number of digits in the fractional part. When working with money, it is crucial to define the scale to ensure that large fractional values are coerced to the required scale.

To see this in action, use the following SQL command to insert some values into the product table.

numeric_db=# insert into product(productName,productPrice)
numeric_db-# values ('Iphone 13 Pro',25.48678),
numeric_db-# ('Samsung galaxy a53',15.5476);
INSERT 0 2

The INSERT shows that the values were inserted successfully. Note that between the two numeric data types, the first has a precision of 7, which violates our precision of 6.

Since we specified a scale of 4, our value will be coerced to the exact scale we set. Use the following SQL command to see the value inserted into the table.

numeric_db=# select * from product;
 id |    productname     | productprice 
----+--------------------+--------------
  1 | Iphone 13 Pro      |      25.4868
  2 | Samsung galaxy a53 |      15.5476
(2 rows)

Use the DECIMAL Data Type in PostgreSQL

For this example, we will use a database named decimal_db. Use the command that we used in the previous example to create this database.

After creating the database, connect to the database and use the following SQL command to create a table named product with a primary key named id and the attributes productName and productPrice.

decimal_db=# create table product(
decimal_db(# id SERIAL NOT NULL UNIQUE,
decimal_db(# productName VARCHAR(100),
decimal_db(# productPrice DECIMAL(6,4));
CREATE TABLE

Note that this table is similar to the table in the previous example. The only change we have made is converting the productPrice data type from NUMERIC to DECIMAL.

Use the following SQL command to insert some values into the product table. As seen in the previous example, the scale will ensure that the value is coerced to the exact scale we have specified.

decimal_db=# INSERT INTO product(productName, productPrice)
decimal_db-# VALUES ('IPhone 13 Pro',25.48678),
decimal_db-# ('Samsung galaxy a53',14.5476);
INSERT 0 2

The INSERT statement is the same as the one in the previous example, and no changes have been made. To verify that the INSERT statement worked as expected, use the following SQL command to view the values inserted into the table.

decimal_db=# select * from product;
 id |    productname     | productprice 
----+--------------------+--------------
  1 | IPhone 13 Pro      |      25.4868
  2 | Samsung galaxy a53 |      14.5476
(2 rows)

Conclusion

In this tutorial, we’ve learned how to use the NUMERIC and DECIMAL data types in PostgreSQL. We have seen how they coerce values, which is crucial when working with monetary values.

These examples show that the two data types have no differences and can be used interchangeably. The reader should be free to use NUMERIC or DECIMAL as they will have the same effect.