Skip to content
Advertisement

Amazon Redshift MOD calculation on numeric column causing error

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement