PostgreSQL - FORMAT Function

The PostgreSQL format() function is a powerful tool for string formatting by allowing developers to insert variables into strings using format specifiers like %s, %I, and %L. This function is especially useful for building dynamic SQL queries and ensuring proper formatting of identifiers.

It simplifies complex string manipulations and enhances security by preventing SQL injection. In this article, We will learn about the FORMAT Function in PostgreSQL in detail by understanding various aspects.

PostgreSQL Format() Function

• The format() function in PostgreSQL is used to format strings in a way similar to the printf() function in C.
  
• It allows placeholders (like %s, %I, or %L) to be replaced with variable values.
  
• This can be particularly useful in dynamically generating SQL statements, user-friendly messages or customized data outputs in queries.

Syntax

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

Explanation:

• format_string: The string containing the format specifiers.
• format_arg: The arguments to replace the format specifiers.

Supported Format Specifiers

The format() function supports various format specifiers to handle different data types:

Examples of Using the format() Function

Let us take a look at some of the examples of FORMAT Function in PostgreSQL to better understand the concept.

Example 1: Simple String Formatting

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

SELECT FORMAT('Hello, %s', 'Geeks!!');

Output:

Example 2: Constructing Customer Full Names

The following statement uses the FORMAT() function to construct customer' full names from first names and last names from the customers table of the sample database which is dvdrental:

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

Output:

Important Points About FORMAT Function in PostgreSQL

• The FORMAT() function is variadic, meaning it can accept a varying number of arguments.
  
• The 'I' and 'L' type specifiers allow the FORMAT() function to format SQL identifiers and literals.
  
• The '-' flag left-justifies the output, while the '0' flag pads the output with zeros instead of spaces.
  
• The FORMAT() function allows the use of positional parameters with the syntax '%n$', where n is the position of the argument in the list.

Conclusion

The PostgreSQL format() function is a versatile tool for formatting strings, making it an excellent choice for dynamic SQL generation and improving code readability. Whether you’re dealing with simple text or complex identifiers and literals, the format() function provides a robust way to customize your outputs and queries.