Numeric Type

20min
Int Type
The INT data type is whole numbers, and they do not have decimal numbers. This is 32-bit signed and has a limit from -2147483648 to 2147483647.
Format
Pgsql
column_name INT
column_name INT
﻿
Example
The following is an example of how to create a column using an INT type:
Pgsql
CREATE TABLE cities (
    city_id INT,
    cityname TEXT,
    population INT
);
INSERT INTO cities (city_id, cityname, population)
VALUES 
(8557411, 'New York', 8419000),
(8557421, 'London', 8982000),
(8557451, 'Hongkong', 7482000),
(8557491, 'Seoul', 9776000);
CREATE TABLE cities (
    city_id INT,
    cityname TEXT,
    population INT
);
INSERT INTO cities (city_id, cityname, population)
VALUES 
(8557411, 'New York', 8419000),
(8557421, 'London', 8982000),
(8557451, 'Hongkong', 7482000),
(8557491, 'Seoul', 9776000);
﻿
Now, run the following query to display the cities table:
Pgsql
SELECT * FROM cities;
SELECT * FROM cities;
﻿
It will show the cities table as shown below:
Pgsql
+----------+-----------+------------+
| city_id  | cityname  | population |
+----------+-----------+------------+
| 8557411  | New York  | 8419000    |
| 8557421  | London    | 8982000    |
| 8557451  | Hongkong  | 7482000    |
| 8557491  | Seoul     | 9776000    |
+----------+-----------+------------+
+----------+-----------+------------+
| city_id  | cityname  | population |
+----------+-----------+------------+
| 8557411  | New York  | 8419000    |
| 8557421  | London    | 8982000    |
| 8557451  | Hongkong  | 7482000    |
| 8557491  | Seoul     | 9776000    |
+----------+-----------+------------+
﻿
Bigint Type
The BIGINT data type is similar to an Integer but is designed to store numbers exceeding the INT range limit. This is 64-bit signed and has a limit from -9223372036854775808 to 9223372036854775807.
Format
Pgsql
column_name BIGINT
column_name BIGINT
﻿
Example
The following is an example of how to create a column using the BIGINT type:
Pgsql
CREATE TABLE galaxies (
    galaxy_name TEXT,
    star BIGINT
);
INSERT INTO galaxies (galaxy_name, star)
VALUES 
('Milky Way', 100000000000),
('Cigar', 30000000000),
('Andromeda', 1000000000000),
('Cosmos', 2000000000000000000);
CREATE TABLE galaxies (
    galaxy_name TEXT,
    star BIGINT
);
INSERT INTO galaxies (galaxy_name, star)
VALUES 
('Milky Way', 100000000000),
('Cigar', 30000000000),
('Andromeda', 1000000000000),
('Cosmos', 2000000000000000000);
﻿
Now, run the following query to display the table:
Pgsql
SELECT * FROM galaxies;
SELECT * FROM galaxies;
﻿
You will get the galaxy names and their stars as the final output:
Pgsql
+---------------+---------------------+
| galaxy_name   | star                |
+---------------+---------------------+
| Milky Way     | 100000000000        |
| Cigar         | 30000000000         |
| Andromeda     | 1000000000000       |
| Cosmos        | 2000000000000000000 |
+---------------+---------------------+
+---------------+---------------------+
| galaxy_name   | star                |
+---------------+---------------------+
| Milky Way     | 100000000000        |
| Cigar         | 30000000000         |
| Andromeda     | 1000000000000       |
| Cosmos        | 2000000000000000000 |
+---------------+---------------------+
﻿
Real Type
The REAL data type is a 32-bit floating-point number.
Format
Pgsql
column_name REAL
column_name REAL
﻿
Example
1) Create a Table
Here, we are creating a table named "numbers” with a REAL column type.
Pgsql
CREATE TABLE numbers (
    column_1 REAL
);

INSERT into numbers (column_1)
VALUES (1.234568);
CREATE TABLE numbers (
    column_1 REAL
);

INSERT into numbers (column_1)
VALUES (1.234568);
﻿
Display the table with the following query:
Pgsql
SELECT * FROM numbers;
SELECT * FROM numbers;
﻿
The following table shows that the column contains at least six decimal digits:
Pgsql
+------------+
| column_1   |
+------------+
| 1.234568   |
+------------+
+------------+
| column_1   |
+------------+
| 1.234568   |
+------------+
﻿
2) Rounding
Rounding might happen if the precision of an input number is too high. 
Pgsql
CREATE TABLE numbers1 (
column_1 REAL
);

INSERT into numbers1 (column_1)
VALUES (1.2345689);
CREATE TABLE numbers1 (
column_1 REAL
);

INSERT into numbers1 (column_1)
VALUES (1.2345689);
﻿
Display the table with the following query:
Pgsql
SELECT * FROM numbers1;
SELECT * FROM numbers1;
﻿
The following table shows that the column can contain at least six decimal digits; the rest is rounded off:
Pgsql
+------------+
| column_1   |
+------------+
| 1.234569   |
+------------+
+------------+
| column_1   |
+------------+
| 1.234569   |
+------------+
﻿
3) Create a Table With Numbers Exceeding the Range
The REAL type only stores 32-bit floating-point numbers. In this example, we input the numbers that exceed the range:
Pgsql
CREATE TABLE numbers2 (
    column_1 REAL
);

INSERT into numbers2 (column_1)
VALUES (1.2345682991822);
CREATE TABLE numbers2 (
    column_1 REAL
);

INSERT into numbers2 (column_1)
VALUES (1.2345682991822);
﻿
Display the table with the following query:
Pgsql
SELECT * FROM numbers2;
SELECT * FROM numbers2;
﻿
The final output will only return numbers that match the range.
Pgsql
+------------+
| column_1   |
+------------+
| 1.2345684  |
+------------+
+------------+
| column_1   |
+------------+
| 1.2345684  |
+------------+
﻿
Double Precision Type
The DOUBLE PRECISION data type is a 64-bit floating-point number.
Format
Pgsql
column_name DOUBLE PRECISION
column_name DOUBLE PRECISION
﻿
Example
1) Create a Table
Here, we are creating a table named numbersdouble with a DOUBLE PRECISION type column.
Pgsql
CREATE TABLE numbersdouble (
    column_1 DOUBLE PRECISION
);

INSERT into numbersdouble (column_1)
VALUES (1.234568817283122);
CREATE TABLE numbersdouble (
    column_1 DOUBLE PRECISION
);

INSERT into numbersdouble (column_1)
VALUES (1.234568817283122);
﻿
Display the table with the following query:
Pgsql
SELECT * FROM numbersdouble;
SELECT * FROM numbersdouble;
﻿
The following result shows that the column contains sixteen decimal digits, following the at least 15 decimal digit rule:
Pgsql
+--------------------+
| column_1           |
+--------------------+
| 1.234568817283122  |
+--------------------+
+--------------------+
| column_1           |
+--------------------+
| 1.234568817283122  |
+--------------------+
﻿
2) Rounding
Rounding might happen if the precision of an input number is too high. 
Pgsql
CREATE TABLE numbersdouble1 (
    column_1 DOUBLE PRECISION
);

INSERT into numbersdouble1 (column_1)
VALUES (1.234568817283122773);
CREATE TABLE numbersdouble1 (
    column_1 DOUBLE PRECISION
);

INSERT into numbersdouble1 (column_1)
VALUES (1.234568817283122773);
﻿
Display the table with the following query:
Pgsql
SELECT * FROM numbersdouble1;
SELECT * FROM numbersdouble1;
﻿
The following result shows that the column can contain at least fifteen decimal digits:
Output
+---------------------+
| column_1            |
+---------------------+
| 1.2345688172831228  |
+---------------------+
+---------------------+
| column_1            |
+---------------------+
| 1.2345688172831228  |
+---------------------+
﻿
﻿