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.