Website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Run Oxla in 2 minutes
🏃‍♂️Run Oxla on S3
🛫Multi-node Deployment
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
⛓️SQL Mutations
DELETE
UPDATE
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
🔄Understanding Transactions
📈Public Metrics
⚙️Oxla Configuration File
✨Changelog
Docs powered by Archbee
SQL Reference
SQL Data Types

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


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);


Now, run the following query to display the cities table:

Pgsql
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    |
+----------+-----------+------------+


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


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);


Now, run the following query to display the table:

Pgsql
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 |
+---------------+---------------------+


Real Type

The REAL data type is a 32-bit floating-point number.

Format

Pgsql
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);


Display the table with the following query:

Pgsql
SELECT * FROM numbers;


The following table shows that the column contains at least six decimal digits:

Pgsql
+------------+
| 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);


Display the table with the following query:

Pgsql
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   |
+------------+


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);


Display the table with the following query:

Pgsql
SELECT * FROM numbers2;


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

Pgsql
+------------+
| 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


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);


Display the table with the following query:

Pgsql
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  |
+--------------------+


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);


Display the table with the following query:

Pgsql
SELECT * FROM numbersdouble1;


The following result shows that the column can contain at least fifteen decimal digits:

Output
+---------------------+
| column_1            |
+---------------------+
| 1.2345688172831228  |
+---------------------+




Updated 09 Oct 2023
Did this page help you?
PREVIOUS
Text Type
NEXT
Numeric Data Type - Aliases
Docs powered by Archbee
TABLE OF CONTENTS
Int Type
Format
Example
Bigint Type
Format
Example
Real Type
Format
Example
Double Precision Type
Format
Example
Docs powered by Archbee

©2023 Oxla