Cloud

Numeric Types

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

To display the table, run the query:

SELECT * FROM cities;

This returns the following result.

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

To display the table, run the query:

SELECT * FROM galaxies;

The query returns 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

Create a table

The following example creates 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 in the following output.

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

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 following output shows the value after rounding.

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

Create a table with numbers exceeding the range

The real type only stores 32-bit floating-point numbers. In this example, the input numbers 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

Create a table

The following example creates 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 following is the output.

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

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 following output shows the value after rounding.

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

numeric type

The numeric data type stores exact decimal numbers. Use it for values where rounding errors are not acceptable, such as monetary amounts. decimal is an alias for numeric.

A numeric type has a precision and a scale:

  • Precision: The total number of digits. Precision must be between 1 and 76.

  • Scale: The number of digits after the decimal point. Scale must be between 0 and the precision.

For example, numeric(10, 2) stores numbers such as 12345678.90.

Format

column_name NUMERIC(precision, scale)
column_name NUMERIC(precision)
column_name NUMERIC

  • NUMERIC(precision) has a scale of 0.

  • NUMERIC without parameters is NUMERIC(18, 0).

If the precision or scale is out of range, the statement fails with one of these errors:

  • DECIMAL precision <p> must be between 1 and 76

  • DECIMAL scale <s> cannot be larger than precision or negative

Where numeric values come from

  • Avro decimal fields: A topic-backed table maps an Avro field with the decimal logical type to a numeric column with the same precision and scale. An Avro decimal field with a precision above 76 is not supported. Avro big-decimal fields are not supported.

  • Iceberg decimal columns: When Redpanda SQL reads the Iceberg history of a topic, it maps an Iceberg decimal(p, s) column to numeric(p, s). Iceberg allows precision up to 38.

  • Casts: You can cast text, integer, floating-point, and other numeric values to numeric, such as '12.50'::NUMERIC(5, 2). You can also cast a numeric value to text, integer, and floating-point types.

    Redpanda SQL reads a number literal with a decimal point, such as 12.50, as a floating-point value. A floating-point value keeps about 15 significant digits. To keep all the digits, cast a string literal, such as '12.50', not a number literal.

Operations on numeric values

You can use numeric values in:

  • Comparisons, such as =, <, and IS DISTINCT FROM

  • GROUP BY, ORDER BY, and window partitions

  • JOIN conditions

  • COALESCE, when all the values have the same scale

The following operations don’t support numeric values yet:

  • Arithmetic: +, -, *, and /

  • The aggregate functions sum(), avg(), min(), and max()

  • to_char()

  • Indexes

To calculate with numeric values, cast them to double precision. The result is not exact.

Example

Find the orders with an amount above a threshold, sorted by amount. In this example, amount is a numeric(10, 2) column that comes from an Avro decimal field:

SELECT order_id, amount
FROM default_redpanda_catalog=>orders
WHERE amount > '100.00'::NUMERIC(10, 2)
ORDER BY amount DESC;

Calculate an average of a numeric column by casting it to double precision:

SELECT avg(amount::DOUBLE PRECISION)
FROM default_redpanda_catalog=>orders;

int16 and int32 types

The int16 and int32 data types are signed integers that are larger than bigint. The type names give the size in bytes, not in bits:

  • int16: A 128-bit signed integer, with a range from -2127 to 2127-1.

  • int32: A 256-bit signed integer, with a range from -2255 to 2255-1.

Use these types in casts in a query. You can’t store them in a table in a Redpanda catalog or an Iceberg catalog, because topic schemas and Iceberg have no matching type.

Example

The sum of a bigint column can be larger than the bigint range. To get the correct sum, cast the column to int16. The sum of int16 values is an int32 value:

SELECT sum(views::INT16) AS total_views
FROM default_redpanda_catalog=>page_events;

Scientific notation support

Redpanda SQL supports scientific notation for floating-point types. This feature supports expressions like 1.1e+3, 1e-20, 1.1e02, and similar in your queries.

Example

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

The query returns:

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