A Quick Look at the GCD() Function in PostgreSQL

In PostgreSQL, gcd() is a mathematical function that returns the greatest common divisor. We pass two numbers and the function returns the largest positive number that divides both inputs with no remainder.

Example

Here’s an example to demonstrate:

SELECT gcd(20, 15);

Result:

5

Here, 5 is the largest positive number that divides into both 20 and 15 with no remainder and so 5 is returned.

Here are some more examples:

SELECT 
    gcd(200, 150),
    gcd(1, 2),
    gcd(1024, 512),
    gcd(0, 150),
    gcd(1024, 1024);

Result:

 gcd | gcd | gcd | gcd | gcd  
-----+-----+-----+-----+------
50 | 1 | 512 | 150 | 1024

Passing a Negative Value

The function returns a positive number regardless of whether we pass positive or negative values:

SELECT 
    gcd(200, -150),
    gcd(-200, 150),
    gcd(-200, -150);

Result:

 gcd | gcd | gcd 
-----+-----+-----
  50 |  50 |  50

Passing Zero

If any one of the arguments is zero, then the non-zero number is returned:

SELECT 
    gcd(0, 150),
    gcd(200, 0);

Result:

 gcd | gcd 
-----+-----
150 | 200

But if both arguments are zero, then zero is returned:

SELECT gcd(0, 0);

Result:

 gcd 
-----
0

Passing a Null Value

If any one of the arguments is null, then null is returned:

SELECT 
    gcd(null, 150),
    gcd(200, null);

Result:

 gcd  | gcd  
------+------
null | null

But if both arguments are null, an error is returned:

SELECT gcd(null, null);

Result:

ERROR:  function gcd(unknown, unknown) is not unique
LINE 1: SELECT gcd(null, null);
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.

Passing the Wrong Argument Type

If any of the arguments are not numeric, an error is returned:

SELECT gcd('cat', 150);

Result:

ERROR:  invalid input syntax for type integer: "cat"
LINE 1: SELECT gcd('cat', 150);
^