PostgreSQL SIGN() Function

PostgreSQL SIGN() Function

The PostgreSQL SIGN() function is used to determine the sign of a numeric value. This function returns -1 for negative numbers, 0 for zero, and 1 for positive numbers. It is essential for identifying the sign of numerical data.

---

Syntax

SIGN(number)

The SIGN() function has the following component:

• number: The numeric value for which to determine the sign.

---

Example PostgreSQL SIGN() Queries

Let's look at some examples of PostgreSQL SIGN() function queries:

1. Basic SIGN() Example

SELECT SIGN(-123) AS sign_value;

This query returns the sign of -123, which is -1.

2. SIGN() with Column Values

SELECT value, SIGN(value) AS sign_value
FROM numbers;

This query retrieves the value and its sign from the numbers table.

3. SIGN() with Zero Value

SELECT value, SIGN(value) AS sign_value
FROM numbers
WHERE value = 0;

This query retrieves the value and its sign from the numbers table where the value is zero.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SIGN() function to determine the sign of numeric values.

Step 1: Creating a Table

This step involves creating a new table named numbers to store numerical data.

CREATE TABLE numbers (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

In this example, we create a table named numbers with columns for id and value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the numbers table.

INSERT INTO numbers (value)
VALUES (-123),
       (0),
       (456),
       (-789);

Here, we insert data into the numbers table.

Step 3: Using the SIGN() Function

This step involves using the SIGN() function to determine the sign of numeric values from the numbers table.

-- Basic SIGN()
SELECT value, SIGN(value) AS sign_value
FROM numbers;

-- SIGN() with Zero Value
SELECT value, SIGN(value) AS sign_value
FROM numbers
WHERE value = 0;

These queries demonstrate how to use the SIGN() function to determine the sign of numeric values from the numbers table, including basic usage and handling zero values.

Conclusion

The PostgreSQL SIGN() function is a fundamental tool for determining the sign of a given numeric value. Understanding how to use the SIGN() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.