Format Number in PostgreSQL

In this PostgreSQL tutorial, I will show you how to format numbers in PostgreSQL, where you will understand different functions provided by PostgreSQL such as to_char, round() and etc.

Additionally, you will understand how to use lpad() function with the numeric value, and use the to_char() function to format the numeric value with the currency symbol to represent the value in a more meaningful way.

Format Number in PostgreSQL

Formatting numbers representing the information in a more organized and readable form. In other words, showing the numeric values or raw numeric data in a more meaningful or readable way. For example, you might want to show the prices with a thousand separator or want to show the prices with a currency symbol.

PostgreSQL provides different types of methods to format numbers, and to present the data in more clarity, it is necessary to format the data.

Let’s see how to use these different methods to format the number that exists in PostgreSQL with real-world examples.

Format Number in PostgreSQL using to_char() function

The to_char() function format the numbers according to the specified template and the is most common or flexible method in PostgreSQL.

Let’s see with an example how to use to_char() to format the numbers, suppose you work on the E-commerce platform and you have been given the task to create a report that shows the total sales of each product.

And you have a table called ‘sales’ which is shown below.

Now you decided that you will use a thousand separators to represent total sales in a more readable way. So execute the below query and generate the report.

SELECT product_name, 
    to_char(sum(sale_amount), '999,999.99') AS total_sales
FROM sales
GROUP BY product_name;

As you can see that the total sales for each product contain a thousand separators which are better for readability. Here you have formatted the total_sales with a thousand separators.

In the above query, select the product name and then compute the sum of value in column ‘sales_amount’ using the sum() function and pass the summed sale amount to to_char() function to format it. Then group the rows based on the column product_name.

Round() function to Format Number in PostgreSQL

To round the number to a number of certain decimal places, PostgreSQL provides a function called round().

For example, suppose you work in a manufacturing company and you have to task to compute the average weight of a specific product, the weight value should be rounded to two decimal places with units in grams.

And you have table ‘product’ as shown below.

Let’s compute the average of the product ‘iPhone’, so for that execute the below query.

SELECT 'All iPhones' AS product_name, 
       round(avg(product_weight), 2) || ' g' AS avg_weight
FROM product
WHERE product_name LIKE 'iPhone%';

As you can see in the output the average weight of all the iPhones is 169.98 g, and you can see the value average weight is more readable and meaningful.

So here you can see how the round() function round the computed average weight of the product using the avg(product_weight) to two decimal places, and the pipe || operator adds string value ‘g’ which represents the weight in grams to the average weight value to format the number value.

Format Number in PostgreSQL For Currency Symbol

You can also use the to_char() function to format the number with the currency symbol. For example, you work in a retail store, and where you want to use the POS (Point of Sale) system to print the receipts.

The receipt should contain the prices formatted with the currency symbol, and you have a table called ‘transaction_info’.

Now, to generate receipts containing prices with formatted currency symbols use the below query.

SELECT item_name, to_char(item_price, 'FM$999,999.00') AS formatted_price
FROM transaction_info
ORDER BY transaction_id;

As you can see in the above output, how the prices of each item in receipts are formatted with the currency symbol dollar using the to_char() function with template ‘FM$999,999.00’.

If you want to know more about the function to_char(), then refer to the tutorial PostgreSQL TO_CHAR function – How to use + Examples

Using Lpad() function to Format Number in PostgreSQL

The lpad() function in PostgreSQL is called left pad, which pads the string with another character to the left till it reaches the specific length.

The syntax of the lpad() is given below.

LPAD(string_value, length, padding_value)

• string_value: It is the source string that you want to pad.
• length: To specify the length of the string that you want after padding.
• padding_value: The padding value that you want to add to the left of the string, if not specified, by default it pad the string with spaces.

For example, you work as a database developer in a company that manufactures the electronics components such as capacitors, resistors and etc.

The company unique batch number for each component and the batch number is made up of a 3-letter prefix that tells the type of component and is followed by a numeric identifier ( batch id). You have been assigned the task to represent all the component batch numbers in a standard format.

This means you have to assign a 3-letter prefix followed by a numeric identifier to each product and be left padded with zero if the numeric identifier is small. So you have a table called production_data which is shown below.

Now execute the below query to represent the batch numbers in the standard format.

SELECT component_name, 
       production_date,
       lpad(batch_prefix || batch_id::text, 8, '0') AS formatted_batch_number
FROM production_data;

Look at the column ‘formatted_batch_number’ that contains the batch number of components in a standard format such as for the component resistor, the batch number is 000RES47, and so on for the other components.

Let’s understand the query part lpad(batch_prefix || batch_id::text, 8, ‘0’) AS formatted_batch_number.

Here original string is (batch_prefix || batch_id::text) which combines the value of column batch_prfix with column batch_id using the pipe || operator. But batch_id is the numeric value so the batch_id column is cast to text using the type cast operator (::text).

The value 8 in the function is the length of the resultant string after the padding. 0 is the padding value that is padded left to the original string.

Conclusion

In this PostgreSQL tutorial, you learned, especially you learned the functions to_char() to format the numeric value with a thousand separator and currency symbol. Also, you learned how to use the round() and lpad() functions to round the numeric value and format the number with padding value respectively.

You may like to read:

• Postgres Length Function
• Group By in PostgreSQL