PostgreSQL SCALE() Function
Summary: in this tutorial, you will learn how to use the PostgreSQLSCALE() function to retrieve the scale of a number.
Introduction to the PostgreSQL SCALE() function
The scale of a number is the count of decimal digits in its fractional part. For example, the scale of1.234 is 3 because it has three digits after the decimal point.
TheSCALE() function allows you to determine the scale of a number.
Here’s the basic syntax of theSCALE() function:
SCALE(numeric_value)TheSCALE() function returns an integer representing the scale of thenumeric_value. It returnsNULL if thenumeric_value isNULL.
PostgreSQL SCALE() function examples
Let’s explore some examples of using the PostgreSQLSCALE() function.
1) Basic SCALE() function example
The following example uses theSCALE() function to determine the scale of the number3.141592653589793:
SELECT SCALE(3.141592653589793);Output:
scale------- 15(1 row)It returns 15 indicating that there are 15 digits after the decimal point.
2) Using the SCALE() table to examine table data
First,create a table calledproduct_prices to store product prices with various scales:
CREATE TABLE product_prices ( product_idSERIAL PRIMARY KEY, product_nameVARCHAR(100)NOT NULL, priceNUMERIC NOT NULL);Second,insert some data into the table:
INSERT INTO product_prices (product_name, price)VALUES ('T-Shirt',10.123), ('Jeans',20.5678), ('Sneakers',30.45), ('Backpack',40.12345), ('Watch',50.6789), ('Sunglasses',60.1), ('Headphones',70.23456), ('Smartphone',80.123), ('Laptop',90.5), ('Camera',100.1234)RETURNING*;Output:
product_id | product_name | price------------+--------------+---------- 1 | T-Shirt | 10.123 2 | Jeans | 20.5678 3 | Sneakers | 30.45 4 | Backpack | 40.12345 5 | Watch | 50.6789 6 | Sunglasses | 60.1 7 | Headphones | 70.23456 8 | Smartphone | 80.123 9 | Laptop | 90.5 10 | Camera | 100.1234(10 rows)Third, group the product prices by scales using theSCALE() function:
SELECT scale(price)AS price_scale, COUNT(*)AS count_of_productsFROM product_pricesGROUP BY price_scaleORDER BY price_scale;Output:
price_scale | count_of_products-------------+------------------- 1 | 2 2 | 1 3 | 2 4 | 3 5 | 2(5 rows)By understanding the scales of prices, you can identify the diverse decimal precisions and take appropriate action to standardize them.
Summary
- Use the
SCALE()function to retrieve the scale of a number.
Last updated on