I’m getting this error when trying to do a MOD on a numeric column in Amazon Redshift database. Is this a bug or I’m I missing something?
create table sch.test123(abc numeric(10,5));
insert into sch.test123(abc) values(12345.345);
select mod(cast(abc as decimal(10,3)),cast('10.0' as decimal(10,3))) from sch.test123;
Error is
Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.
Advertisement
Answer
This is an issue in the MOD() function. It currently only works for INT and BIGINT data types. However you can use the modulo operator instead.
SELECT abc % 10.000 from #test123; -- 5.34500
Thank you for bringing this to our attention. We’ve identified the issue and are working on a fix. When fixed it will be noted in our maintenance announcements at the top of the forum. https://forums.aws.amazon.com/forum.jspa?forumID=155&start=0
UPDATE: This issue is now resolved in the latest Redshift version.