CODEWITHSUNDEEP
typesnumbersamazon-redshiftsqldatatypes
Stanleyrr
Stanleyrr

Reputation: 875

Best data structure to store long strings of digits

I have a column of numbers (maximum length of each number is 14 digits) and each character in each number is exclusively digit (so no letters or special characters inside the numbers).

I've tried assigning the data type int4 and int8 to the numbers column, and load the table into Redshift (PostgreSQL). However, I kept getting the error below:

numeric value "9555739320" out of range for integer

This confused me because I thought int8 is bigint type which can store very huge numbers, as stated in session 8.1. Numeric Types in this site: https://www.postgresql.org/docs/current/datatype-numeric.html. If my understanding is correct, why would the number "9555739320" be out of range?

Is there any recommendation on what data type to assign this number column to? Let's say that some numbers might have leading 0s, for example "000223568960", and I want to preserve every digit including the 0s.

Upvotes: 1

Views: 419

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657052

Your error message unmistakably states:

numeric value "9555739320" out of range for integer

"integer". If you had actually tried to assign to a int8 column, the error message would say "bigint", not "integer". Except that it wouldn't, as 9555739320 is easily in the bigint range of -9223372036854775808 to 9223372036854775807 - like you stated yourself.

int8 is an alias for bigint.
int4 or just int are aliases for integer.

Let's say that some numbers might have leading 0s, for example "000223568960", and I want to preserve every digit including the 0s.

If you want to preserve a variable number of leading zeros, you cannot use any of the numeric data types, which trim such insignificant noise. Use text or varchar instead.

Upvotes: 3

Related Questions