PostgreSQL – FORMAT Function
The FORMAT function is used to format a string using placeholders that are replaced with corresponding values. It’s a powerful function that allows you to create formatted strings with specific patterns and insert values into those patterns.
The syntax of the FORMAT function is as follows:
FORMAT(format_string, value1, value2, ...)where format_string is the string that defines the formatting pattern. It can contain placeholders in the form of %s, %I, %L, %d, %f, etc., where each placeholder corresponds to a value you want to insert;
and value1, value2, etc.: The values that will replace the placeholders in the format_string.
Here’s an example of how you might use the FORMAT function in a SQL query:
SELECT FORMAT('Name: %s, Age: %d', 'John', 30) AS formatted_result;In this example, the result would be the string 'Name: John, Age: 30', as the placeholders %s and %d were replaced by the values 'John' and 30 respectively.
You can use different placeholders for different types of values:
%s is used for string values.
%d is used for integer values.
%f is used for floating-point values.
%I is used for identifiers (table/column names), escaping them if necessary.
%L is used for literal values, escaping single quotes.
The FORMAT function is particularly helpful when you want to generate complex strings based on dynamic values, such as constructing SQL queries dynamically, generating log messages, or creating formatted output for reports.