Open In App

PostgreSQL – FORMAT Function

Improve
Improve
Like Article
Like
Save
Share
Report

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', 'Geeks!!');

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:


Last Updated : 25 Aug, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads