What is the limit of the field type BIGINT in SQL?

Question

is 100000235882380 or 100000466411115 acceptable? (That is ID from facebook)

I guess for portability, you may want to use a string instead. In my case I encoded the twos-complement value (BigInteger.toBytes) using Base64 and looked up by that when accessing the database. — Archimedes Trajano, Jul 19 '13 at 01:33

score 37 · Accepted Answer · edited Dec 14 '17 at 01:24

37

Check the manual of the RDBMS you're using. It may not be the same in all systems.

MySQL:
https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

PostgreSQL:
https://www.postgresql.org/docs/10/static/datatype-numeric.html

SQL Server (Transact-SQL):
https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql

DB2:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_biginteger.htm

edited Dec 14 '17 at 01:24

Pang

9,564
146
81
122

answered Dec 10 '10 at 12:29

Dan Grossman

51,866
10
112
101

2

Are there any other RDMSs that have a BIGINT type other than SQL Server? Maybe a similar type but I think it is fair to conclude that OP is using SQL Server. – Andrew Hare Dec 10 '10 at 12:31
8

MySQL, Postgres... there are more users of RDBMS's with BIGINT *other than* SQL Server than users of SQL Server. – Dan Grossman Dec 10 '10 at 12:33
1

Actually, if bigint is defined in the ISO SQL standard as 64 bits (which I suspect it is), then you wouldn't need to make an exhaustive list of databases, only the ones too brain-damaged to conform to the standard. – Jeff Jan 22 '18 at 03:03
@Jeff: Unfortunately, SQL standard says only this about the ranges of integer types: "The precision of SMALLINT shall be less than or equal to the precision of INTEGER, and the precision of BIGINT shall be greater than or equal to the precision of INTEGER." (a precision is the number of bits of the integer type) – iwis Oct 10 '20 at 23:31
This old DB2 link is dead; DB2's numeric data types can now be found at https://www.ibm.com/docs/en/db2/11.5?topic=dtl-numbers – Lee C. Jan 06 '22 at 17:44

score 10 · Answer 2 · answered Dec 10 '10 at 12:29

10

Yes, according to int, bigint, smallint, and tinyint (Transact-SQL):

bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

answered Dec 10 '10 at 12:29

Andrew Hare

344,730
71
640
635

John Parker · Answer 3 · 2010-12-10T12:37:00.547

3

It depends on the RDBMS. For example, the maxiumum values of a BIGINT are 9223372036854775807 (for signed) or 18446744073709551615 (for un-signed) on MySQL.

See the Numeric Types section of the docs for the full details.

edited Dec 10 '10 at 12:37

answered Dec 10 '10 at 12:30

John Parker

54,048
11
129
129

1

Note that this is for MySQL, other database platforms may or may not have the same limits (alas, the OP didn't specify which SQL db was meant). – Piskvor left the building Dec 10 '10 at 12:35
@Piskvor - Good call, a presumption too far on my part. I'll update my answer. :-) – John Parker Dec 10 '10 at 12:36

score 0 · Answer 4 · answered Dec 10 '10 at 12:29

0

Nothing to worry about. BIGINT can store much larger values than the ones you have.

At a minimum up to 9,223,372,036,854,775,807.

answered Dec 10 '10 at 12:29

rustyx

80,671
25
200
267

What is the limit of the field type BIGINT in SQL?

4 Answers4

Linked

Related