PostgreSQL Tutorial: FORMAT Function

September 17, 2023

Summary: PostgreSQL FORMAT() function formats arguments based on a format string.

Table of Contents

If you have worked with the C programming language, you will find that the FORMAT() function is similar to the sprintf() function.

Syntax

The syntax of the PostgreSQL FORMAT() function is as follows:

FORMAT(format_string [, format_arg [, ...] ])

The FORMAT() function is variadic, therefore, you can pass the arguments to be formatted as an array marked with the VARIADIC keyword.

The FORMAT() function treats the array’s elements as ordinary arguments and NULL as an array of zero elements.

Arguments

1) format_string

The format_string argument is a string that specifies how the result string should be formatted.

The format_string consists of text and format specifiers. Text is copied directly to the result string while the format specifiers are placeholders for the arguments to be inserted into the result string.

The following shows the syntax of the format specifier:

%[position][flags][width]type

A format specifier starts with % character. It has three optional components position, flags, widthand a required component type.

position

The position specifies which argument to be inserted in the result string. The position is in the form n$ where n is the argument index. The first argument starts from 1.

If you omit the position component, the default is the next argument in the list.

flags

Currently, the flags can accept a minus sign (-) that instructs the format specifier’s output to be left-justified.

The flags component only takes effect when the width field is specified.

width

The optional width field specifies the minimum number of characters to use for displaying the format specifier’s output.

The result string can be padded left or right with the spaces needed to fill the width.

If the width is too small, the output will be displayed as-is without any truncation.

The width can be one of the following values:

A positive integer.
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.

type

type is the type of format conversion to use to produce the format specifier’s output.

The permitted values for type argument are as follows:

s formats the argument value as a string. NULL is treated as an empty string.
I treats the argument value as an SQL identifier.
L quotes the argument value as an SQL literal.

We often use I and L for constructing dynamic SQL statements.

If you want to include % in the result string, use double percentages %%

2) format_arg

As mentioned earlier, you pass a number of format arguments to the FORMAT() function.

Return value

The FORMAT() function returns a formatted string.

Examples

Let’s take a look at some examples of using the FORMAT() function.

A) Simple format

The following statement uses the FORMAT() function to format a string:

SELECT FORMAT('Hello, %s', 'PostgreSQL');

The output is:

'Hello, PostgreSQL'

In this example, %s is replaced by the 'PostgreSQL' string argument.

See the following customer table in the sample database.

customer table

The following statement uses the FORMAT() function to construct customers’ full names from first names and last names:

SELECT
    FORMAT('%s, %s', last_name, first_name) full_name
FROM
    customer;
ORDER BY
    full_name;

PostgreSQL FORMAT example

In this example, we used two format specifiers %s %s which are then replaced by values in the first_name and last_name columns.

B) Using flags component example

The following statement shows how to use the flags and with components in the format specifier:

SELECT FORMAT('|%10s|', 'one');

The output string is left-padded with spaces and right-aligned.

format
--------------
 |       one|
(1 row)

To make it left-aligned, you use – as the flag:

SELECT FORMAT('|%-10s|', 'one');

The output is:

    format
--------------
 |one       |
(1 row)

C) Using position component example

This example shows how to use the position component of the format specifier:

SELECT 
    FORMAT('%1$s apple, %2$s orange, %1$s banana', 'small', 'big');

The following illustrates the output:

                format
---------------------------------------
 small apple, big orange, small banana
(1 row)

In this example, we have two arguments which are 'small' and 'big' strings.

The 1$ and 2$ positions instruct the FORMAT() function to inject the first ('small') and second arguments ('big') into the corresponding placeholders.

The 1$ position appears twice in the format string, therefore, the first argument is also inserted twice.

In this tutorial, you have learned how to use the PostgreSQL FORMAT() function to format arguments according to a format string.