PostgreSQL – FORMAT Function
In PostgreSQL, the FORMAT() function is used to format arguments based on a format string.
Syntax: FORMAT(format_string [, format_argument [, ....] ])
Let’s analyze the above syntax:
- The FORMAT() function is variadic, meaning, users can supply the arguments as an array marked with the VARIADIC keyword.
- The FORMAT() function considers the array’s elements as normal arguments and it treats NULL as an array of zero elements.
Arguments
1. format_string
The format_string sets the behaviour of the resulting formatted string. It also has text and format specifiers. In this function, the text arguments are copied directly to the result string and the format specifiers are placeholders for the arguments.
Syntax of the format specifier:
Syntax: %[position][flags][width]type
Note that a format specifier begins with a “%” and it has three optional components position, flags, width, and a required component type.
2. position
It is used to set the argument that is to be inserted in the result string. The position is in the form of n$ where n is the argument index. The first argument starts from 1. The default is the next argument in the list if the position component is not set.
3. flags
This component is used in conjunction with the width field, for instance, the flags can accept a minus sign (-) that instructs the format specifier’s output to be left-justified.
4. width
It is an optional argument and is used to set the minimum number of characters to use for displaying the format specifier’s output. At this stage, padding can be done to fill up the empty spaces whereas in case the string is smaller than the specified width the result string can be padded left or right with the spaces needed to fill the width. In the exact opposite case, the result string is displayed without any alteration.
The width argument can hold the following values:
- A positive integer value.
- An asterisk (*) to use the next function argument as the width.
- A string of the form *n$ to use the nth function argument as the width.
5. type
It is used to define the type of the output string from the format specifier.
It can hold the following values:
- s: It formats the argument value as a string. NULL values are treated as an empty strings.
- I: It treats the argument value as an SQL identifier.
- L: It makes the argument value as an SQL literal.
I and L are generally used for constructing dynamic SQL statements. The double percentages (%%) can also be used for including the % sign
6. format_arg
As discussed earlier, the users pass a number of format arguments to the FORMAT() function.
Return value
The FORMAT() function returns a formatted string.
Example 1:
The following statement uses the FORMAT() function to format a string:
SELECT FORMAT('Hello, %s', 'Beginner!!');
Output:
Example 2:
The following statement uses the FORMAT() function to construct customer’s full names from first names and last names from the customers table of the sample database, ie, dvdrental:
SELECT FORMAT('%s, %s', last_name, first_name) full_name FROM customer; ORDER BY full_name;
Output: