PostgreSQL SCALE() Function

PostgreSQL SCALE() Function

The PostgreSQL SCALE() function is used to return the number of digits to the right of the decimal point in a numeric value. This function is essential for understanding the precision of numerical data.

Syntax

SCALE(number)

The SCALE() function has the following component:

  • number: The numeric value for which to calculate the scale.

Example PostgreSQL SCALE() Queries

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

1. Basic SCALE() Example

SELECT SCALE(123.456) AS scale_value;

This query returns the scale of 123.456, which is 3.

2. SCALE() with Column Values

SELECT value, SCALE(value) AS scale_value
FROM numbers;

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

3. SCALE() with Integer Values

SELECT value, SCALE(value) AS scale_value
FROM numbers
WHERE value = 100;

This query retrieves the value and its scale from the numbers table where the value is an integer.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SCALE() function to calculate the scale 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.456),
       (789.0),
       (0.12345),
       (100);

Here, we insert data into the numbers table.

Step 3: Using the SCALE() Function

This step involves using the SCALE() function to calculate the scale of numeric values from the numbers table.

-- Basic SCALE()
SELECT value, SCALE(value) AS scale_value
FROM numbers;

-- SCALE() with Integer Values
SELECT value, SCALE(value) AS scale_value
FROM numbers
WHERE value = 100;

These queries demonstrate how to use the SCALE() function to calculate the scale of numeric values from the numbers table, including basic usage and handling integer values.

Conclusion

The PostgreSQL SCALE() function is a fundamental tool for understanding the precision of numerical data by calculating the number of digits to the right of the decimal point. Understanding how to use the SCALE() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.