PostgreSQL Numeric

Introduction to PostgreSQL Numeric

While dealing with numbers extra care needs to be incorporated while storing them and the right type of the datatype should be declared for our numeric field according to the value that is expected to be stored in it. PostgreSQL provides 10 types of data types that can be used to store and handle numeric values in PostgreSQL databases. In this article, we will discuss all the datatypes that are provided to handle numeric data in PostgreSQL and also see its storage size and range required and allowed for each of that datatype and take an example to understand how these values are stored and retrieved.

Numeric Datatypes

In all, there are 10 numeric data types present in the PostgreSQL database and contain integral values with 2,4 and 8 bytes of size, floating-point values, an auto-incrementing serial values with variable ranges. The below table lists out the name of the data type, the size that is required to store each value belonging to that particular datatype and the usage specifying when and where that data type should be used.

Numeric Datatype	Size in bytes	Usage
Smallserial datatype	2 bytes space	Integers that will be autoncremented and are small in size.
Serial datatype	4 bytes space	Integers that will be autoncremented and are medium in size.
Bigserial datatype	8 bytes space	Integers that will be autoncremented and are large in size.
Smallint datatype	2 bytes space	Integers that will have a small range of values.
Integer datatype	4 bytes space	Most commonly used and preferred datatype for storing integral values.
Bigint datatype	8 bytes space	Integers that might have a large range of values.
Decimal datatype	Variable size	It is a decimal value that will have user-specified and exact precision values stored in it.
Double precision datatype	8 bytes space	It is a decimal value that will have variable precision values stored in it with a maximum of 15 decimal digits of precision in it.
Numeric datatype	Variable size	It is a decimal value that will have user-specified and exact precision values stored in it.
Real datatype	4 bytes space	It is a decimal value that will have variable precision values stored in it with a maximum of 6 decimal digits of precision in it.

Integer Datatypes

These are the data types that allow us to store the integer value that does not contain any fraction part in it and are wholesome numbers. The most preferred data type for storing the integer values is Integer that gives us the range of -2147483648 to +2147483647. Smallint is used only when the memory space is at stake and you need to use your storage resource effectively in the scarcity of memory and has a range of -32768 to +32767 and can also be used for storing less valued integers. BigInt is used when the range of integer is not sufficient to store your integer values and gives us a huge range from -9223372036854775808 to +9223372036854775807.

Let us create one table named educba_integer_demo with three columns of datatype smallint, integer, and bigint.

CREATE TABLE educba_integer_demo(small smallint,medium integer, big bigint);

that gives the following output if the table is created successfully.

Let us enter some records in it.

INSERT INTO educba_integer_demo VALUES(32767,2147483647,9223372036854775807);

If the value is inserted properly, the output will be as follows –

Let us increase the value of the big column by one so that it would exceed the range and see what happens if such an attempt is made.

INSERT INTO educba_integer_demo VALUES(32767,2147483647,9223372036854775808);

The output will be as follows –

As can be seen, an error is thrown saying bigint is out of range.

Now, attempt to insert a value that is not in the range of small int using the following query –

INSERT INTO educba_integer_demo VALUES(32778,2147483647,9223372036854775807);

that gives the following output with an error saying smallint is out of range –

Inserting a value that is out of range of integer also gives the error as follows when the query like this is fired –

INSERT INTO educba_integer_demo VALUES(32767,2147483650,9223372036854775807);

Inserting all the three values that are not in the range gives the following error when such attempt is made by executing the following query –

INSERT INTO educba_integer_demo VALUES(32790,2147483750,9223372036854775900);

As can be seen, it throws the error for only the first column that it traverses which in our case is the smallint data type column.

Serial Datatypes

In Postgres, we have three data types that are available to create the columns that behave in the auto-incrementing fashion for storing values that will automatically be incremented by 1 by default and are unique fields. There are three datatypes – smallserial, serial and bigserial datatypes. Generally, the serial data type is used for storing autoincremented column values in Postgres that provide the range from 1 to 2147483647. When we don’t have to store many values then we can use smallserial datatype that has the range from 1 to 32767. If we know that our database will store a lot of rows even more than 2147483647 then we can use bigserial datatype that has a range of 1 to 9223372036854775807.

Let us create the table having all these three data typed columns named educba_serial_demo using the following query –

CREATE TABLE educba_serial_demo(small smallserial,medium serial, big bigserial);

INSERT INTO educba_serial_demo VALUES (DEFAULT,DEFAULT,DEFAULT),(DEFAULT,DEFAULT,DEFAULT),(DEFAULT,DEFAULT,DEFAULT);

Executing the above query gives the following output –

Let us see what values are inserted in the  educba_serial_demo table using select statement –

select * from educba_serial_demo;

that gives the following output –

As can be seen, the values are automatically inserted incrementally for all three columns.

Floating-point data types

We have four datatypes to store floating-point numbers that have different ranges and have either user-defined or variable precision. Numeric and decimal have user-defined precision and exact up to 131072 and 16383 digits before and after the decimal point while real and double have variable precision with 6 and 15 decimal digit precision respectively. We can use them as per our use-case and requirement and specify the precision and scale in the case of numeric and decimal datatypes.

Conclusion

There are 10 numeric data types available in our PostgreSQL database that have different ranges and occupy different storage spaces in the database. We can use them as per our convenience and requirement. However, we have to be careful while using them and consider their behavior and range.

This is a guide to PostgreSQL Numeric. Here we discuss introduction, syntax, format, and examples with code implementation. You may also have a look at the following articles to learn more –

1. PostgreSQL Link
2. PostgreSQL SET
3. PostgreSQL Integer
4. PostgreSQL Character Varying