Get Sign of column in PostgreSQL – SIGN() Function

In PostgreSQL, you can use the SIGN() function to determine the sign of a numeric value. This function returns. In order to get sign of the column value in postgresql we use SIGN() function. SIGN() Function in gets negative (-1) or positive value (1).

Let’s see how to

• Get basic Sign of numeric value
• Get Sign of the column value in PostgreSQL –  SIGN()
• Practical use case on how to use sign() function
• Assign keyword like Positive, Negative and Neutral based on the column value sign in PostgreSQL

With an example for each

Syntax:

numeric_expression: The number for which you want to determine the sign.

Example of SIGN() function:

Example 1: Basic Usage

To get the sign of a positive, negative, and zero value:

 

SELECT 
    SIGN(9) AS sign_positive,
    SIGN(-9) AS sign_negative,
    SIGN(0) AS sign_zero;

Output:

Get Sign of the column in PostgreSQL table:

We use table sign_demo

 

SELECT *,SIGN(value) as sign_value  from SIGN_DEMO

We have created a column and stored the Sign value of the column “value”

Practical Use Cases

Example: Categorizing Values by Sign

In PostgreSQL You might want to categorize values into positive, negative, and zero groups based on their sign: IT will assign column vales as Negative to minus sign and Positive keyword to positive sign and Neutral or Zero keyword where Zero is present.

 

SELECT 
    id, 
    value, 
    CASE 
        WHEN SIGN(value) = 1 THEN 'Positive'
        WHEN SIGN(value) = -1 THEN 'Negative'
        ELSE 'Zero'
    END AS value_category
FROM SIGN_DEMO;

In this example, the CASE statement is used to create categories based on the sign of each value.

Output: