PostgreSQL - FORMAT Function
Last Updated :
14 Oct, 2024
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:
Specifier | Description |
---|
%s | Replaces with a string. |
%I | Replaces with an identifier (table or column name). |
%L | Replaces with a literal (safely quoted). |
%t | Replaces with a Boolean. |
%D | Replaces with a numeric, double-precision number. |
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.
Similar Reads
PostgreSQL- CONCAT Function The PostgreSQL CONCAT function allows us to combine multiple strings or column values into a single output, making it a flexible tool for data manipulation. This function is essential for string concatenation tasks, whether weâre working with static text, columns from a database table, or dynamic SQ
4 min read
PostgreSQL - EXTRACT Function In PostgreSQL, the EXTRACT() function is a powerful tool used to retrieve specific components of a date or time value. Whether you need to query for a particular year, month, day, or even more detailed time attributes, EXTRACT() can help you extract these fields from date and time values efficiently
2 min read
PostgreSQL Date Functions PostgreSQL is widely recognized for its comprehensive support for date and time manipulations, making it an excellent choice for applications requiring precise time management and complex calculations. This article explores the core PostgreSQL date functions, covering how to retrieve the current dat
4 min read
PostgreSQL - CHR Function The CHR() function in PostgreSQL is used to convert an integer ASCII code or a Unicode code point into its corresponding character. Let us better understand the concept of CHR Function in PostgreSQL from this article.SyntaxCHR(value);Parameter:value: The 'value' argument is generally an integer ASCI
2 min read
PostgreSQL - CONCAT_WS Function In PostgreSQL, the CONCAT_WS function is a powerful and versatile tool for concatenating strings with a specified separator. This function not only combines multiple string values but also efficiently handles NULL values, ignoring them in the concatenation process. In this article, we will go deep i
3 min read
PostgreSQL - ASCII Function When working with PostgreSQL, you might need to derive the ASCII (American Standard Code for Information Interchange) code of a character. The PostgreSQL ASCII() function is a handy tool for this purpose. In the case of UTF-8 encoding, the ASCII() function returns the Unicode code point of the chara
2 min read
PostgreSQL DATE_PART Function Handling dates and times efficiently is essential for data-driven applications, and PostgreSQL provides powerful built-in functions for managing and manipulating time-based data. One such function is the DATE_PART() function, which allows us to extract specific subfields from date and timestamp valu
5 min read
PostgreSQL - ARRAY_AGG() Function The PostgreSQL ARRAY_AGG function is a aggregate function that allows users to combine values from multiple rows into an array. This function is particularly useful when managing grouped data and returning multiple values in a single row. In this article, We will learn about the What is PostgreSQL A
4 min read
PostgreSQL - DATE_TRUNC Function The PostgreSQL DATE_TRUNC() function is a powerful tool that helps us truncate a timestamp or interval to a specified level of precision. It is particularly useful when dealing with time-based data for effective data grouping and manipulation, making it ideal for reporting, analytics, and queries wh
4 min read
DATEADD() Function in PostgreSQL PostgreSQL is a powerful, open-source relational database system known for its strength and wide range of functionalities. DATEADD function in PostgreSQL adds or subtract time intervals from a given date. In this article, we will discuss basic usage, advanced interval types, and practical examples f
6 min read