Create a SQL Function to Calculate the nth Root of a Number

Posted February 27, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Vertica has a built-in function to calculate the arithmetic square root a number called SQRT and another built-in function to calculate the arithmetic cube root of a number called CBRT.

Example: dbadmin=> SELECT sqrt(9); sqrt ------ 3 (1 row) dbadmin=> SELECT cbrt(27); cbrt ------ 3 (1 row) But what if I need a function to calculate the arithmetic nth root of a number? No problem. I can create my own! dbadmin=> CREATE OR REPLACE FUNCTION nrt (x FLOAT, y FLOAT) RETURN FLOAT dbadmin-> AS dbadmin-> BEGIN dbadmin-> RETURN POWER(x,(1/y)); dbadmin-> dbadmin-> END; CREATE FUNCTION dbadmin=> SELECT nrt(390625, 4); nrt ----- 25 (1 row) dbadmin=> SELECT nrt(256, 4); nrt ----- 4 (1 row) dbadmin=> SELECT nrt(27, 3); nrt ----- 3 (1 row) dbadmin=> SELECT nrt(9, 2); nrt ----- 3 (1 row) Helpful Links: