PostgreSQL format() Function

The PostgreSQL format() function returns a formatted string according to the specified format string and arguments.

`format()` Syntax

This is the syntax of the PostgreSQL format() function:

format(format_str, [param1[, param2] ...])

Parameters

format_str

Required. The format string. This format string may contain multiple format specifiers. Format specifiers take the following form:
```
%[position][flags][width]type
```
The format specifier starts with % and ends with type, with 3 optional parameters position, flags, and width:
- position
  
  Optional. It is used to indicate which parameter to use. It takes the form N$ where N is a number. N starts at 1, where 1 means the first argument after format_str. If it is not specified, the default is to use the next parameter in the parameter list.
- flags
  
  Optional. Used in conjunction with the width option. Currently only one symbol is supported: the minus sign (-), which means the output is left-justified. It does not work when width is not specified.
- width
  
  Optional. It is used to set the minimum number of characters output by this format specifier. Depending on the setting of flag, the output is left-padded or right-padded with spaces. If the specified width is smaller than the parameter’s width, it has no effect.
  
  This parameter can use any of the following:
  - a positive integer value
  - An asterisk (*), to use the next function argument as the width
  - A string in the format *n$, to use the nth function argument as the width.
- type
  
  Required. Specifies the type of output for the format specifier. The following types are supported:
  - s format the parameter value as a simple string. Null values are treated as empty strings.
  - I treat parameter values as SQL identifiers, enclosing them in double quotes if necessary. A value of null is wrong.
  - L quote the parameter value as SQL text. Empty values are displayed as strings NULL without quotes.
param1...

Optional. Parameters referenced by format specifiers in the format string. If there are more arguments than format specifiers, the extra arguments are ignored. The number of arguments is variable and may be zero.

Return value

The PostgreSQL format() function returns a formatted string according to the specified format string and arguments.

`format()` Examples

Basic usage

This example shows how to format a string using the PostgreSQL format() function.

SELECT format('Hello %s', 'World') AS "format('Hello %s', 'World')";

 format('Hello %s', 'World')
-----------------------------
 Hello World

This example shows how to use the PostgreSQL format() function to handle a format string with multiple arguments.

SELECT format('This is %s, he is %s years old.', 'Tim', 20);

              format
----------------------------------
 This is Tim, he is 20 years old.

For the same format string, you can easily modify the parameters to output a different content. As follows:

SELECT format('This is %s, he is %s years old.', 'Tom', 18);

              format
----------------------------------
 This is Tom, he is 18 years old.

The location of the parameter

You can specify the index position of the parameter which is used in the format specifier. As follows:

SELECT format('Reserve arguments: %3$s, %2$s, %1$s', 'A', 'B', 'C');

           format
----------------------------
 Reserve arguments: C, B, A

width

This example shows how to use the format() function to left-pad a string with spaces to make its width to 10.

SELECT format('|%10s|', 'foo');

    format
--------------
 |       foo|

The following statement can achieve the same purpose:

SELECT format('|%*s|', 10, 'foo');

This example shows how to use the format() function to right-pad a string with spaces to the right to make its width to 10.

SELECT format('|%-10s|', 'foo');

    format
--------------
 |foo       |