1

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.

Stanleyrr
  • 858
  • 3
  • 12
  • 31
  • "*I thought `int4` is `bigint` type*" - that's not what the page you linked says. – Bergi Sep 26 '20 at 01:06
  • 1
    Aside: Redshift is *not* Postgres. It has been forked a very long time and differences have been growing ever since. The Postgres manual for version 8.1 is as close as it gets, but still far from it. Stick to the (inferior, granted) documentation of Amazon for their product: https://docs.aws.amazon.com/redshift/latest/dg/welcome.html – Erwin Brandstetter Sep 26 '20 at 01:09
  • 1
    @Bergi: In this particular case it makes kind of sense that the OP pointed to the 8.1 manual instead of the current one. (All the same for the actual problem, though.) – Erwin Brandstetter Sep 26 '20 at 01:14
  • @Bergi thanks for catching that. I made a typo meant to say "int8" instead. – Stanleyrr Sep 26 '20 at 15:42

1 Answers1

3

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks, @Erwin Brandsetter. It's interesting I've tried assign the column "text" type, but still won't work. This time the error says "numeric value "37167582983" out of range for integer". I am confused because I assigned it "text" type why it would still treat it as an "integer". – Stanleyrr Sep 26 '20 at 17:03
  • 1
    @Stan: It wouldn't. There must be another, unnecessary cast to integer in the food chain. Find it and eliminate it. Be sure that your (now) string constants are properly single-quoted. See: https://stackoverflow.com/a/12320729/939860 – Erwin Brandstetter Sep 26 '20 at 17:18