SQL BIGINT

What is SQL BIGINT?

BIGINT stores signed or unsigned 64-bit whole numbers.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BIGINT: PostgreSQL, MySQL, MariaDB, SQL Server, Snowflake, Redshift, SQLite, DuckDB, Trino. Oracle uses NUMBER(19) as an equivalent.

SQL BIGINT Full Explanation

BIGINT is a fixed-precision integer data type that occupies 8 bytes (64 bits) of storage and represents whole numbers without a fractional component. In Standard SQL the type is signed, giving a range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Many dialects such as MySQL and MariaDB also offer an UNSIGNED modifier that shifts the range to 0 through 18,446,744,073,709,551,615.Because BIGINT can safely represent any 32-bit and 53-bit (JavaScript) integer, it is frequently chosen for high-volume primary keys, surrogate keys, epoch timestamps in milliseconds, monetary amounts in smallest currency units, and any counter expected to exceed the 32-bit INT range. Compared with DECIMAL or NUMERIC, BIGINT requires less storage and performs arithmetic faster because it has no scale metadata.Dialect differences exist:- PostgreSQL: BIGINT is an alias for INT8. BIGSERIAL creates an auto-incrementing BIGINT column plus sequence.- MySQL/MariaDB: Optional display width (deprecated) and optional UNSIGNED.- SQL Server: BIGINT is always signed; identity columns can auto-increment.- Oracle: Use NUMBER(19) for equivalent precision because Oracle lacks a dedicated BIGINT keyword.- SQLite: Uses dynamic typing; any integer up to 8 bytes is stored as INTEGER, so BIGINT aliases to INTEGER affinity.Caveats:- Choosing BIGINT when INT would suffice increases disk and index size.- Overflow results in errors or wraparound depending on dialect.- Client libraries in weakly typed languages (JavaScript, JSON) may lose precision for values larger than 9,007,199,254,740,991 (2^53-1).

SQL BIGINT Syntax

-- Column definition
column_name BIGINT [UNSIGNED] [NOT NULL] [DEFAULT value];

-- Auto-increment examples
-- PostgreSQL
id BIGSERIAL PRIMARY KEY;
-- MySQL
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY;

-- Casting
SELECT CAST('9223372036854775807' AS BIGINT);

SQL BIGINT Parameters

Example Queries Using SQL BIGINT

-- 1. Create a table that uses BIGINT
CREATE TABLE orders (
    order_id   BIGINT PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    total_cents BIGINT UNSIGNED
);

-- 2. Insert a large integer value
INSERT INTO orders (order_id, user_id, total_cents)
VALUES (9007199254740992, 42, 1299);

-- 3. Select rows over a monetary threshold
SELECT order_id, total_cents
FROM orders
WHERE total_cents > 1000;

-- 4. Cast a string to BIGINT
SELECT CAST('123456789012345' AS BIGINT) AS big_number;

Expected Output Using SQL BIGINT

Table is created with three BIGINT columns.
One row is inserted successfully because the value is within BIGINT range.
The SELECT returns the inserted row because 1299 > 1000.
Query returns a single column called big_number with value 123456789012345.

Use Cases with SQL BIGINT

Primary keys expected to exceed 2.1 billion (32-bit INT upper limit)
Millisecond or microsecond Unix epoch timestamps
Monetary values stored as integer cents to avoid floating-point error
Cumulative counters or sequences with high transaction volumes
Storing hashes or identifiers that fit in 64 bits

Common Mistakes with SQL BIGINT

Using BIGINT when INT or SMALLINT is adequate, increasing storage and index size unnecessarily
Forgetting UNSIGNED in MySQL and unexpectedly hitting negative ranges
Assuming Oracle has BIGINT; NUMBER(19) must be used instead
Returning BIGINTs to JavaScript clients without serialization, causing precision loss beyond 2^53-1
Mixing BIGINT columns with FLOAT or DOUBLE in expressions, which may coerce to floating point and lose precision

First Introduced In

SQL:1999

Frequently Asked Questions

What is SQL BIGINT used for?

BIGINT is chosen for columns that need to store integers larger than the 32-bit INT range, such as high-volume primary keys, epoch timestamps, and monetary amounts in cents.

What is the storage size of BIGINT?

BIGINT always uses 8 bytes of disk and memory regardless of the actual value stored.

How do I create an auto-incrementing BIGINT column?

- PostgreSQL: use BIGSERIAL or create a BIGINT column with DEFAULT nextval('seq').- MySQL: add AUTO_INCREMENT to a BIGINT UNSIGNED column.- SQL Server: declare BIGINT IDENTITY(1,1).

Can BIGINT cause performance issues?

Sequential scans are slightly slower and indexes are larger compared with INT because of the extra 4 bytes, but for most workloads the difference is negligible relative to the safety of avoiding overflow.