In PostgreSQL, the sign() function is used to indicate the sign of its argument.

It doesn’t simply return the actual sign. Rather, it returns -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Syntax

The syntax goes like this:

sign(dp or numeric)

Where dp is a double precision value.

Example 1 – Positive Number

Here’s how it works when you provide a positive number.

SELECT sign(552);

Result:

1

In this case my argument is a positive number and so sign() returns 1.

Example 2 – Negative Number

And here’s what happens if I add a negative sign to that number.

SELECT sign(-552);

Result:

-1

Example 3 – Zero

And here’s what happens if I pass zero to the function.

SELECT sign(0);

Result:

0

Example 4 – Fractional Seconds

It doesn’t matter if your argument is a whole number or if it consists of fractional seconds, the result is the same.

SELECT 
  sign(0.552),
  sign(0.0),
  sign(-0.552);

Result:

 sign | sign | sign
------+------+------
    1 |    0 |    -1

Example 5 – Expressions

You can also provide expressions, such as the following.

SELECT sign(12 * -0.552);

Result:

-1