An Overview of the FORMAT() Function in PostgreSQL

In PostgreSQL, the format() function produces output formatted according to a format string. The first argument is the format string (consisting of one or more format specifiers that start with the % character), and the subsequent arguments are the values to format.

Syntax

The syntax goes like this:

format(formatstr text [, formatarg "any" [, ...] ])

The first argument is the format string. It determines how the formatarg arguments are formatted.

The formatstr argument has the following syntax:

%[position][flags][width]type

So we must provide a type, but the others are optional.

The type format specifier can be any of the following:

`s`	Formats the argument value as a simple string. A null value is treated as an empty string.
`I`	Treats the argument value as an SQL identifier, double-quoting it if necessary. The value cannot be null.
`L`	Quotes the argument value as an SQL literal. A null value is displayed as the string `NULL`, without quotes.

As mentioned, the position, flags, and width format specifiers are optional. Here’s a quick explanation of these:

`position`	Allows us to specify which position to put each `formatarg` argument in the output.
`flags`	Additional options controlling how the format specifier’s output is formatted. Currently the only supported flag is a minus sign (`-`) which will cause the format specifier’s output to be left-justified. This has no effect unless the `width` field is also specified.
`width`	Specifies the minimum number of characters to use to display the format specifier’s output. The output is padded on the left or right (depending on the `-` flag) with spaces as needed to fill the width.

String Example

Here’s a basic example to demonstrate how to format the argument as a simple string:

SELECT format('Do %s More', 'Run');

Result:

Do Run More

Here, the %s part is a placeholder for the second argument to be inserted into the output as a simple string. The argument was inserted as though it was part of the string. We were able to include text in our format string and we were able to use the s format specifier to output the text from the second argument.

We can provide multiple values by providing a third argument, a fourth, fifth, and so on:

SELECT format('Do %s %s More', 'Run', 'Fast');

Result:

Do Run Fast More

It’s also possible change the order in which these arguments appear in the output string (more on this later).

SQL Identifier Example

We can use the I format specifier to format the argument value as a SQL identifier, double-quoting it if necessary.

Here are examples of various values being formatted as SQL identifiers:

\x
SELECT 
    format('SELECT * FROM %I', 'city') AS "city",
    format('SELECT * FROM %I', 'city_city') AS "city_city",
    format('SELECT * FROM %I', 'city city') AS "city city",
    format('SELECT * FROM %I', 'user') AS "user",
    format('SELECT * FROM %I', 'User') AS "User";

Result:

city      | SELECT * FROM city
city_city | SELECT * FROM city_city
city city | SELECT * FROM "city city"
user      | SELECT * FROM "user"
User      | SELECT * FROM "User"

In this case I dynamically constructed some SQL SELECT statements, using %I to insert the table name.

We can see that some were quoted and others weren’t. PostgreSQL decides whether or not it should add quotes.

SQL Literal Example

We can use the L format specifier to format the argument value as an SQL literal.

Here’s an example:

SELECT format('SELECT * FROM t1 WHERE c2 = %L', 'Cat');

Result:

SELECT * FROM t1 WHERE c2 = 'Cat'

Here, the second argument became the value for the WHERE clause as a SQL literal.

Changing the Position of the Arguments

Earlier, we saw that we can provide multiple values to insert into the output string.

When we do this, we also have the option of specifying the position of each argument in the output string. We can do this with the form n$, where n is the index of the argument to print.

As a refresher, the syntax of the format string goes like this:

%[position][flags][width]type

So if we’re going to specify the position of an argument, we need to do it straight after the percentage sign (%).

Here’s an example:

SELECT format('%2$s %3$s %1$s', 'Cat', 'Dog', 'Bird');

Result:

Dog Bird Cat

We can see that the three values are output in the specified order. For example, %2$s means that the second argument should be output in that position. In this case the second argument is Dog, and so that’s what is output.

Here are some more examples to demonstrate how different values affect the output:

SELECT 
    format('%s %s %s', 'Cat', 'Dog', 'Bird') AS "%s %s %s",
    format('%s %s', 'Cat', 'Dog', 'Bird') AS "%s %s",
    format('%2$s %3$s', 'Cat', 'Dog', 'Bird') AS "%2$s %3$s",
    format('%2$s %2$s %2$s', 'Cat', 'Dog', 'Bird') AS "%2$s %2$s %2$s";

Result:

   %s %s %s   |  %s %s  | %2$s %3$s | %2$s %2$s %2$s 
--------------+---------+-----------+----------------
 Cat Dog Bird | Cat Dog | Dog Bird  | Dog Dog Dog

So if we want the arguments to be output in the same order that we provided them in, then we don’t need to specify a position. And if we don’t want all arguments to be included in the output, then we don’t need to include them in the format string.

However, when we use the index to specify the position, we must be sure that a corresponding argument exists. Here’s what happens when we specify an index that doesn’t exist:

SELECT format('%4$s', 'Cat', 'Dog', 'Bird');

Result:

ERROR:  too few arguments for format()

We get an error. That’s because we specified an index of 4 but only provided three arguments to format.

Adjusting the Width

We can specify the number of characters to use to display the format specifier’s output:

SELECT format('%2$5s %3$10s %1$25s', 'Cat', 'Dog', 'Bird');

Result:

                   format                   
--------------------------------------------
   Dog       Bird                       Cat

Here I specified a width that was greater than the specified values. Therefore, spaces were added to fill the width.

If we provide a width that’s less than the actual values, then the width is ignored (the value isn’t truncated):

SELECT format('%2$1s %3$1s %1$1s', 'Cat', 'Dog', 'Bird');

Result:

    format    
--------------
 Dog Bird Cat

We can specify the width with any of the following: a positive integer; an asterisk (*) to use the next function argument as the width; or a string of the form *n$ to use the nth function argument as the width.

Adding a Flag

PostgreSQL allows us to add a flag to control how the output is formatted. That said, it currently only supports one flag; the minus sign (-) which causes the format specifier’s output to be left-justified. This is only applicable when the width field is also specified.

Here’s an example of using the - flag:

SELECT format('%2$-5s %3$-10s %1$-25s', 'Cat', 'Dog', 'Bird');

Result:

                   format                   
--------------------------------------------
 Dog   Bird       Cat

So this the same example from above, except that I added the - flag in order to left-justify the arguments in the output.

Passing an Array

It’s possible to pass an array to the format() function so that each array element becomes treated as a separate argument. To do this, prefix the array with the VARIADIC keyword:

SELECT format(
    'I have a %s, a %s, and a %s', 
    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]
    );

Result:

I have a Cat, a Dog, and a Horse

We can see that each element in the array has become a separate field in the output string. We can move them around in the string just like we can do if we’d passed them as separate arguments, and we can limit which ones appear in the output string (i.e. we don’t have to include all array elements if we don’t want):

SELECT format(
    'I have a %2$s and a %1$s', 
    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]
    );

Result:

I have a Dog and a Cat