Unsigned field in Amazon Redshift?

Unfortunately Amazon Redshift doesn't support unsigned integer. As a workaround, we are using numeric(20,0) for bigint unsigned data. Here is an example.

create table funny_table(
    my_field numeric(20, 0)
);
insert into funny_table values ( 18446744073709551614 );
select * from funny_table;
       my_field
----------------------
 18446744073709551614
(1 row)

See here for the details of Numeric type.


As already mentioned, Redshift does not support unsigned. Given that, please take a closer at what you need to achieve.

bigint occupies 8 bytes giving you a range of -9223372036854775808 to 9223372036854775807

numeric occupies 128-bit (Variable, up to 128 bits) but offers a bigger range at the expense of memory.

I believe the idea behind using unsigned is doubling the range WITHOUT EXTRA EXPENSE TO STORAGE. So if you are comfortable with the highest positive value of 2^63 - 1 go with bigint and forget about the unsigned because it costs 8 bytes anyway.

If you have bigger positive integers go with numeric(20, 0) (or higher precision) but you need to be aware that it is still signed and occupies more than 8 bytes.