INT

CockroachDB supports various signed integer data types.

Note:

For instructions showing how to auto-generate integer values (e.g., to auto-number rows in a table), see this FAQ entry.

Names and Aliases

Syntax

A constant value of type INT can be entered as a numeric literal.For example: 42, -1234, or 0xCAFE.

Size

The different integer types place different constraints on the range of allowable values, but all integers are stored in the same way regardless of type. Smaller values take up less space than larger ones (based on the numeric value, not the data type).

Considerations for 64-bit signed integers

By default, INT is an alias for INT8, which creates 64-bit signed integers. This differs from the Postgres default for INT, which is 32 bits, and may cause issues for your application if it is not written to handle 64-bit integers, whether due to the language your application is written in, or the ORM/framework it uses to generate SQL (if any).

For example, JavaScript language runtimes represent numbers as 64-bit floats, which means that the JS runtime can only represent 53 bits of numeric accuracy and thus has a max safe value of 2⁵³, or 9007199254740992. This means that the maximum size of a default INT in CockroachDB is much larger than JavaScript can represent as an integer. Visually, the size difference is as follows:

9223372036854775807 # INT default max value
   9007199254740991 # JS integer max value

Given the above, if a table contains a column with a default-sized INT value, and you are reading from it or writing to it via JavaScript, you will not be able to read and write values to that column correctly. This issue can pop up in a surprising way if you are using a framework that autogenerates both frontend and backend code (such as twirp). In such cases, you may find that your backend code can handle 64-bit signed integers, but the generated client/frontend code cannot.

If your application needs to use an integer size that is different than the CockroachDB default (for these or other reasons), you can change one or both of the settings below. For example, you can set either of the below to 4 to cause INT and SERIAL to become aliases for INT4 and SERIAL4, which use 32-bit integers.

• The default_int_size session variable.
• The sql.defaults.default_int_size cluster setting.

Tip:

If your application requires arbitrary precision numbers, use the DECIMAL data type.

Examples

> CREATE TABLE ints (a INT PRIMARY KEY, b SMALLINT);

> SHOW COLUMNS FROM ints;

+-------------+-----------+-------------+----------------+-----------------------+-------------+
| column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| a           | INT       |    false    | NULL           |                       | {"primary"} |
| b           | SMALLINT  |    true     | NULL           |                       | {}          |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
(3 rows)

> INSERT INTO ints VALUES (1, 32);

INSERT 1

> SELECT * FROM ints;

+---+----+
| a | b  |
+---+----+
| 1 | 32 |
+---+----+
(1 row)

Supported casting and conversion

INT values can be cast to any of the following data types:

See also

• Data Types
• FLOAT
• DECIMAL

Was this page helpful?
YesNo