PostgreSQL – BIGINT Data Type
The bigint data type is used to store large whole numbers (integers) within a much larger range than the regular integer data type. It’s a 64-bit signed integer type, which means it can represent much larger and smaller values compared to the integer type. Here are some key points about the bigint data type in PostgreSQL:
- Size and Range: A
biginttakes up 8 bytes of storage and can represent values from approximately -9.2 quintillion (-9,223,372,036,854,775,808) to 9.2 quintillion (9,223,372,036,854,775,807). - Alias: The
biginttype doesn’t have a short alias like theintegertype. You must usebigintwhen defining columns of this type. - Arithmetic Operations: You can perform arithmetic operations (addition, subtraction, multiplication, division, etc.) on
bigintvalues just like any other numeric type. - Numeric Functions: Similar to the
integerdata type, you can use various mathematical functions and operators withbigintvalues. - Overflow Behavior: If an arithmetic operation results in a value outside the range of the
bigintdata type, an overflow occurs. PostgreSQL will wrap around the values according to their representation as signed integers.
Here’s an example of using the bigint data type:
CREATE TABLE account_balances ( account_id serial PRIMARY KEY, account_name varchar(100), balance bigint ); INSERT INTO account_balances (account_name, balance) VALUES ('Savings', 1000000000000), ('Investment', 5000000000000);SELECT * FROM account_balances WHERE balance > 2000000000000;Here, balance column is of type bigint. It is used to store account balances. The bigint data type allows you to work with very large numerical values, making it suitable for applications that deal with significant quantities or financial data.