PostgreSQL MIN_SCALE() Function

PostgreSQL MIN_SCALE() Function

The PostgreSQL MIN_SCALE() function is used to return the number of digits in the fractional part of a numeric value, discarding trailing zeroes. This function is essential for understanding the precision of numerical data.

Syntax

MIN_SCALE(number)

The MIN_SCALE() function has the following component:

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

Example PostgreSQL MIN_SCALE() Queries

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

1. Basic MIN_SCALE() Example

SELECT MIN_SCALE(123.456000) AS min_scale_value;

This query returns the minimum scale of 123.456000, which is 3.

2. MIN_SCALE() with Column Values

SELECT value, MIN_SCALE(value) AS min_scale_value
FROM numbers;

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

3. MIN_SCALE() with Zero Fractional Part

SELECT value, MIN_SCALE(value) AS min_scale_value
FROM numbers
WHERE value = 100.000;

This query retrieves the value and its minimum scale from the numbers table where the value has a zero fractional part.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the MIN_SCALE() function to calculate the minimum 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.456000),
       (789.000),
       (0.123450),
       (100.000);

Here, we insert data into the numbers table.

Step 3: Using the MIN_SCALE() Function

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

-- Basic MIN_SCALE()
SELECT value, MIN_SCALE(value) AS min_scale_value
FROM numbers;

-- MIN_SCALE() with Zero Fractional Part
SELECT value, MIN_SCALE(value) AS min_scale_value
FROM numbers
WHERE value = 100.000;

These queries demonstrate how to use the MIN_SCALE() function to calculate the minimum scale of numeric values from the numbers table, including basic usage and handling zero fractional parts.

Conclusion

The PostgreSQL MIN_SCALE() function is a fundamental tool for understanding the precision of numerical data by calculating the number of digits in the fractional part of a numeric value. Understanding how to use the MIN_SCALE() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.