Numeric Type

Int Type

The INT data type represents whole numbers without decimal points. It is a 32-bit signed integer with a range from -2147483648 to 2147483647.

Format

column_name INT

Example

The following is an example of how to create a column using an INT type.

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 table.

SELECT * FROM cities;

It will result in a table show below.

 city_id | cityname | population 
---------+----------+------------
 8557411 | New York |    8419000
 8557421 | London   |    8982000
 8557451 | Hongkong |    7482000
 8557491 | Seoul    |    9776000
(4 rows)

Bigint Type

The BIGINT data type stores large whole numbers that exceed the INT range. It is a 64-bit signed integer with a range from -9223372036854775808 to 9223372036854775807.

Format

column_name BIGINT

Example

The following is an example of how to create a column using the BIGINT type:

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:

SELECT * FROM galaxies;

You will get the following output:

 galaxy_name |        star         
-------------+---------------------
 Milky Way   |        100000000000
 Cigar       |         30000000000
 Andromeda   |       1000000000000
 Cosmos      | 2000000000000000000
(4 rows)

Real Type

The REAL data type is a 32-bit floating-point number compliant with the IEEE 754 binary32 format.

Format

column_name REAL

Example

1. Create a Table

Here, we are creating a table with a REAL column type.

CREATE TABLE numbers (
    column_1 REAL
);

INSERT into numbers (column_1)
VALUES (1.234568);

Display the table with the following query.

SELECT * FROM numbers;

The stored value is shown below.

 column_1 
----------
 1.234568
(1 row)

2. Rounding

Rounding might happen if the precision of an input number is too high.

CREATE TABLE numbers1 (
column_1 REAL
);

INSERT into numbers1 (column_1)
VALUES (1.2345689);

Display the table with the following query.

SELECT * FROM numbers1;

The table below shows the value after rounding.

 column_1 
----------
 1.234569
(1 row)

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.

CREATE TABLE numbers2 (
    column_1 REAL
);

INSERT into numbers2 (column_1)
VALUES (1.2345682991822);

Display the table with the following query.

SELECT * FROM numbers2;

The final output will only return numbers that match the range.

 column_1  
-----------
 1.2345684
(1 row)

Double Precision Type

The DOUBLE PRECISION data type is a 64-bit floating-point number compliant with the IEEE 754 binary64 format.

Format

column_name DOUBLE PRECISION

Example

1. Create a Table

Here, we are creating a table with a DOUBLE PRECISION type column.

CREATE TABLE numbersdouble (
    column_1 DOUBLE PRECISION
);

INSERT into numbersdouble (column_1)
VALUES (1.234568817283122);

Display the table with the following query.

SELECT * FROM numbersdouble;

The output is shown below.

     column_1      
-------------------
 1.234568817283122
(1 row)

2. Rounding

Rounding might happen if the precision of an input number is too high.

CREATE TABLE numbersdouble1 (
    column_1 DOUBLE PRECISION
);

INSERT into numbersdouble1 (column_1)
VALUES (1.234568817283122773);

Display the table with the following query.

SELECT * FROM numbersdouble1;

The table below shows the value after rounding.

      column_1      
--------------------
 1.2345688172831228
(1 row)

Scientific Notation Support

Oxla now supports scientific notation for floating-point types. This feature allows you to use expressions like 1.1e+3, 1e-20, 1.1e02 and similar in your queries.

Example

SELECT 1.1e+3, 1e-20, 1.1e02;

Output

 ?column? | ?column? | ?column?
----------+----------+----------
     1100 |    1e-20 |      110
(1 row)